PostgreSQL 9.6だとNLJを選んでいたクエリでもMerge Sort JoinやHash Joinを選びやすい。 多くのケースだと問題ないのだけど、下記のようにORDER BY 狙いのINDEXが効くようなケースだとクエリが遅くなる場合がある。
実行結果
試しにローカルにDocker経由で最新のPostgreSQL 13を用意して、10億レコードほど入ったtableを用意する。 普通にクエリを実行するとマージソートが選ばれる。
hoge=# SELECT version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
hoge=# EXPLAIN ANALYZE SELECT a.id FROM "売上" AS a JOIN "売上" AS b ON a.id = b.id ORDER BY a.id LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.15..1.22 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)
-> Merge Join (cost=1.15..67606628.03 rows=1010033664 width=4) (actual time=0.021..0.021 rows=1 loops=1)
Merge Cond: (a.id = b.id)
-> Index Only Scan using "売上_pkey" on "売上" a (cost=0.57..26228061.54 rows=1010033664 width=4) (actual time=0.009..0.009 rows=1 loops=1)
Heap Fetches: 0
-> Index Only Scan using "売上_pkey" on "売上" b (cost=0.57..26228061.54 rows=1010033664 width=4) (actual time=0.010..0.010 rows=1 loops=1)
Heap Fetches: 0
Planning Time: 0.143 ms
Execution Time: 0.035 ms
(9 rows)
十分はえーじゃん。 Index Only Scan だし、ちょっとDBに優しいクエリだったね。 でもNLJ*1ならもっと早い。
hoge=# set enable_mergejoin = off ; SET hoge=# EXPLAIN ANALYZE SELECT a.id FROM "売上" AS a JOIN "売上" AS b ON a.id = b.id ORDER BY a.id LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1.15..5.69 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1) -> Nested Loop (cost=1.15..4582467879.52 rows=1010033664 width=4) (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using "売上_pkey" on "売上" a (cost=0.57..26228061.54 rows=1010033664 width=4) (actual time=0.018..0.018 rows=1 loops=1) Heap Fetches: 0 -> Index Only Scan using "売上_pkey" on "売上" b (cost=0.57..4.51 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1) Index Cond: (id = a.id) Heap Fetches: 0 Planning Time: 0.089 ms Execution Time: 0.039 ms (9 rows)
こんな感じ。 現場のスパゲッティクエリ*2では顕著に差が出る。
最悪なケースはHash JOINが選ばれた時
JOINのアルゴリズムがわかっていれば、それは当然そうだよねって感じなんだけど一応実行結果を並べておく。
hoge=# set enable_nestloop = off ; SET -- 実行結果を確認してHash JOINが選ばれていることを確認する hoge=# EXPLAIN SELECT a.id FROM "売上" AS a JOIN "売上" AS b ON a.id = b.id ORDER BY a.id LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=46418550.34..46418550.46 rows=1 width=4) -> Gather Merge (cost=46418550.34..144623123.99 rows=841694720 width=4) Workers Planned: 2 -> Sort (cost=46417550.32..47469668.72 rows=420847360 width=4) Sort Key: a.id -> Parallel Hash Join (cost=22590655.60..44313313.52 rows=420847360 width=4) Hash Cond: (a.id = b.id) -> Parallel Seq Scan on "売上" a (cost=0.00..15686128.60 rows=420847360 width=4) -> Parallel Hash (cost=15686128.60..15686128.60 rows=420847360 width=4) -> Parallel Seq Scan on "売上" b (cost=0.00..15686128.60 rows=420847360 width=4) JIT: Functions: 10 Options: Inlining true, Optimization true, Expressions true, Deforming true (13 rows) -- 実際にクエリを実行してみる hoge=# EXPLAIN ANALYZE SELECT a.id FROM "売上" AS a JOIN "売上" AS b ON a.id = b.id ORDER BY a.id LIMIT 1; -- QUERYが帰ってこない
Hash JOINは仕組み上、パラレルクエリと言えど、テーブルスキャンが必要なので圧倒的に実行速度に差が出る。
まとめ
PostgreSQLのバージョンアップで前よりパフォーマンスに差が出たときには一度実行計画を見ること。
上記の問題にぶつかった時はpg_hint_planをインストールして、ヒント句で調整するかset enable_mergejoin = off ; や set enable_hashjoin = off ; を実行して実行計画を調整するのが良い。
