[PostgreSQL]Explain Analyzeで、多重JOINしたSQLをチューニングする
Sec ScanのないSQLをどうやって高速化するのか
本当に難しいのはここから。例えば、Explain Analyzeの結果が下記のようなものだったら、どうしますか？
どこにボトルネックがあるか、わかりますか？
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1568.27..1568.51 rows=96 width=260) (actual time=822.175..822.259 rows=198 loops=1)
Sort Key: e.pgd, e.evtno, p.ptgno
Sort Method: quicksort Memory: 100kB
-> Nested Loop (cost=87.12..1565.11 rows=96 width=260) (actual time=15.763..821.403 rows=198 loops=1)
-> Nested Loop (cost=87.12..1309.65 rows=96 width=256) (actual time=15.729..792.478 rows=198 loops=1)
-> Hash Join (cost=87.12..1058.64 rows=96 width=256) (actual time=15.703..791.253 rows=198 loops=1)
Hash Cond: (e.grpsno = g.grpsno)
-> Nested Loop (cost=0.00..970.13 rows=112 width=226) (actual time=13.304..788.536 rows=198 loops=1)
-> Nested Loop (cost=0.00..680.36 rows=111 width=129) (actual time=13.282..786.911 rows=198 loops=1)
-> Nested Loop (cost=0.00..69.49 rows=111 width=10) (actual time=13.221..275.439 rows=198 loops=1)
-> Nested Loop (cost=0.00..46.76 rows=4 width=4) (actual time=13.171..218.500 rows=15 loops=1)
-> Index Scan using oifo_uano_index on oifo oi (cost=0.00..21.43 rows=4 width=4) (actual time=13.123..194.165 rows=15 loops=1)
Index Cond: (uano = 103679)
Filter: ((createdday > '2010-01-01 00:00:00'::timestamp without time zone) AND (pmtsttsmstrno = 2))
-> Index Scan using odvifo_oifono on odvifo odi (cost=0.00..6.32 rows=1 width=8) (actual time=1.617..1.618 rows=1 loops=15)
Index Cond: (odi.oifono = oi.oifono)
Filter: (odi.createdday > '2010-01-01 00:00:00'::timestamp without time zone)
-> Index Scan using odpt_odvifono on odpt op (cost=0.00..4.87 rows=65 width=14) (actual time=3.767..3.778 rows=13 loops=15)
Index Cond: (op.odvifono = odi.odvifono)
-> Index Scan using ptgrp_pk on ptgrp p (cost=0.00..5.49 rows=1 width=123) (actual time=2.580..2.581 rows=1 loops=198)
Index Cond: (p.ptgno = op.ptgno)
Filter: ((p.createdday > '2010-01-01 00:00:00'::timestamp without time zone) AND (p.delflag = '0'::bpchar) AND (p.viewflag = '0'::bpchar))
-> Index Scan using evt_cvtifo on evt e (cost=0.00..2.60 rows=1 width=97) (actual time=0.005..0.005 rows=1 loops=198)
Index Cond: (e.cvtifono = p.cvtifono)
Filter: (e.delflag = '0'::bpchar)
-> Hash (cost=69.38..69.38 rows=1420 width=34) (actual time=2.376..2.376 rows=1420 loops=1)
-> Seq Scan on groups g (cost=0.00..69.38 rows=1420 width=34) (actual time=0.011..1.377 rows=1420 loops=1)
Filter: (delflag = '0'::bpchar)
-> Index Scan using cvtifo_pk on cvtifo ci (cost=0.00..2.60 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=198)
Index Cond: (ci.cvtifono = p.cvtifono)
Filter: (ci.delflag = '0'::bpchar)
-> Index Scan using ctgry_pk on ctgry c (cost=0.00..2.65 rows=1 width=8) (actual time=0.143..0.144 rows=1 loops=198)
Index Cond: (c.ctgryno = p.ctgryno)
Filter: ((c.delflag = '0'::bpchar) AND (c.viewflag = '0'::bpchar))
Total runtime: 822.580 ms
(35 行)
"Seq Scan on groups"というのが一か所ありますが、ここに目を奪われていはいけませんよ。
これは1.3msしかかかってないのでボトルネックではありません。
どうですか？
難しいでしょ？
これのSQLのイメージは、例えば下記のような多重JOINしたものです。 (上のやつとちょっと違いますが・・・あくまでイメージです。)
SELECT hoge,fuga
FROM a
JOIN b ON a.a_id = b.a_id
AND a.x_id = 12345
AND a.createdday > '2010-01-01'
JOIN c ON b.b_id = c.b_id
AND b.createdday > '2010-01-01'
JOIN d ON c.c_id = d.c_id
AND d.createdday > '2010-01-01'
JOIN e ON d.d_id = e.d_id
.... 以下、延々と続く
着目点
このSQLを見ると、JOIN時になるべくレコード数を絞るために、AND句で日付を絞って古いレコードを排除しよう努力としています。それ自体は一定の効果を上げているようです。
Explain Analyzeを見ると、Index Condの直下に
Filter: (odi.createdday > '2010-01-01')
のようにFilterとして表れています。
さあ、ここで着目すべきは、 「Index Cond時に、ちゃんとFilterで絞りこめているか？」
その目線でもう一度Explain Analyzeを見てみてください。
ちょうど真ん中あたりにこういうのがあります。
-> Index Scan using odpt_odvifono on odpt op (cost=0.00..4.87 rows=65 width=14) (actual time=3.767..3.778 rows=13 loops=15)
Index Cond: (op.odvifono = odi.odvifono)
Index Cond時にFilterがありません。
どうやらここに原因がありそうです。
さっきのSQLイメージでいうと、c.createddayでの絞り込みがなかったのが原因です。
そこで、AND条件を追加してみましょう。
JOIN c ON b.b_id = c.b_id
AND b.createdday > '2010-01-01'
AND c.createdday > '2010-01-01'
めでたしめでたし！
まとめ
- Sec Scanがなくてもあきらめない！
- Nexted Loopにひるむな！
- 多重JOINしたSQLでは、JOIN時にレコード数を絞りこめ！
- Filter:はちゃんと効いてるか？
参考
下記記事が大変参考になりました。第3回 PostgreSQLを遅くしている犯人はどこだ？
カテゴリ:
PostgreSQL