MySQL 8.0.22で Derived Condition Pushdown Optimization を試した MySQL Release note でわいわい言う勉強会 8.0.22　2020/10/29 まつひさ（hmatsu47） 

MySQL 8.0.22 のリリースノートを見て、 ● 困った、取り上げるネタがない ● しょうがない、何か選ぶか… …というわけで、 2 

MySQL 8.0.22 のリリースノートを見て、 ● 困った、取り上げるネタがない ● しょうがない、何か選ぶか… …というわけで、 3 （リリースノート「Optimizer Notes」より） 

Derived Condition Pushdown Optimization ● サブクエリの外側に記された条件（ WHERE 句など）を、 ● サブクエリに直接適用（プッシュダウン）することで、 ● クエリの実行を高速化する …という機能です。が。 　試し方をいきなり間違えたので、迷い道へ… 4 

間違い① いきなり難しい適用例にチャレンジ ● Window 関数を含む例にチャレンジ ○ 公式マニュアルの最後に記されている（いた？） ○ https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html 5 

間違い② テスト環境の設定がデフォルトと違った ● ONLY_FULL_GROUP_BY が無効になっていた ● 結果として、間違い③に気付くのが遅れた 6 ↑この時点で十分怪しい 

間違い③ 公式マニュアルの適用例が間違ってた ● ONLY_FULL_GROUP_BY が有効だとエラーに ● ONLY_FULL_GROUP_BY が無効だと結果が不定に ○ 行数は変わるし、Empty set になることも 7 mysql> SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3; ↓この結果が、 あるときは、 45 rows in set (0.76 sec) またあるときは、 891 rows in set (0.45 sec) さらに… Empty set (0.20 sec) 

エラー or 結果が不定になる原因は、 ● GROUP_BY 句と Window 関数を単純に併用すると、 ● SUM(○) OVER (PARTITION BY △) の ○ が不定になるから そして、 ● SUM(SUM(○)) OVER (PARTITION BY △) で SUM() を二重化 すると、 ONLY_FULL_GROUP_BY 問題を避けられる 8 

しかし、 ● そのままでは Derived Condition Pushdown Optimization が効かない ● 公式マニュアルの少し上を読むと…？ 9 i じゃなくて j 、ですよね…？ 

しかし、 ● そのままでは Derived Condition Pushdown Optimization が効かない←すみません、実験ミスによる勘違いでした ● 公式マニュアルの少し上を読むと…？ 10 i じゃなくて j 、ですよね…？ ↑どっちでも行けました 

というわけで、 ● こうなりました 11 SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3; +-----+-----+------+--------+ | i | j | min | sum | +-----+-----+------+--------+ | 380 | 28 | 1 | 244608 | | 94 | 56 | 0 | 225664 | | 430 | 58 | 1 | 190592 | | 96 | 58 | 1 | 190592 | （中略） | 290 | 974 | 1 | 380480 | | 309 | 986 | 2 | 280480 | | 323 | 994 | 2 | 356160 | +-----+-----+------+--------+ 44 rows in set (0.78 sec) 

というわけで、 ● こうなりました 12 mysql> EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G *************************** 1. row *************************** EXPLAIN: -> Filter: (dt.min < 3) -> Table scan on dt (cost=23961.62 rows=212970) -> Materialize -> Window aggregate with buffering: sum(sum(t1.k)) OVER (PARTITION BY t1.j ) -> Sort: t1.j -> Table scan on -> Aggregate using temporary table -> Filter: (t1.j > 10) (cost=65468.60 rows=212971) -> Table scan on t1 (cost=65468.60 rows=638976) 1 row in set (0.00 sec) ↑ここにプッシュダウン ←これは外側に残る 

参考までに、PostgreSQL 11 の場合は、こう 13 subquery_test=> EXPLAIN SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM testschema.t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Subquery Scan on dt (cost=61813.16..63733.16 rows=21333 width=44) Filter: (dt.min < 3) -> WindowAgg (cost=61813.16..62933.16 rows=64000 width=44) -> Sort (cost=61813.16..61973.16 rows=64000 width=20) Sort Key: t1.j -> Finalize GroupAggregate (cost=36070.71..56704.11 rows=64000 width=20) Group Key: t1.i, t1.j -> Gather Merge (cost=36070.71..54784.11 rows=128000 width=20) Workers Planned: 2 -> Partial GroupAggregate (cost=35070.68..39009.71 rows=64000 width=20) Group Key: t1.i, t1.j -> Sort (cost=35070.68..35730.49 rows=263922 width=12) Sort Key: t1.i, t1.j -> Parallel Seq Scan on t1 (cost=0.00..6793.33 rows=263922 width=12) Filter: (j > 10) (15 rows) 少し表現は違うものの MySQL 8.0 と同様に 

でも実は、 ● この適用例は全く速くなりません！ ● 「Derived Condition Pushdown Optimization の意味ない じゃん！」となるとマズいので、↓の記事の最後では速くなる 例を示しています。 ○ MySQL 8.0.22 で Derived Condition Pushdown Optimization を試してみた ○ https://qiita.com/hmatsu47/items/c15b1c778d050913201d 14