以下のクエリはトップページにおいて新着POVコメントを3つ表示する際のクエリです。
その時のEXPLAINの結果は以下のとおりでした。
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=80949.23..80949.24 rows=3 width=171) (actual time=1194.803..1194.805 rows=3 loops=1)
-> Sort (cost=80949.23..82594.36 rows=658051 width=171) (actual time=1194.801..1194.801 rows=3 loops=1)
Sort Key: pg.tourokubi
Sort Method: top-N heapsort Memory: 26kB
-> Hash Join (cost=2162.89..72444.05 rows=658051 width=171) (actual time=26.481..801.675 rows=666273 loops=1)
Hash Cond: (pg.pov = p.id)
-> Hash Join (cost=2125.05..63358.01 rows=658051 width=136) (actual time=26.243..579.539 rows=666273 loops=1)
Hash Cond: (pg.game = g.id)
-> Seq Scan on povgroups pg (cost=0.00..44781.68 rows=658051 width=67) (actual time=0.003..246.225 rows=666273 loops=1)
Filter: (memo IS NOT NULL)
Rows Removed by Filter: 1320643
-> Hash (cost=1879.04..1879.04 rows=19681 width=73) (actual time=26.230..26.230 rows=19678 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 2098kB
-> Hash Join (cost=169.81..1879.04 rows=19681 width=73) (actual time=2.403..19.669 rows=19678 loops=1)
Hash Cond: (g.brandname = b.id)
-> Seq Scan on gamelist g (cost=0.00..1364.81 rows=19681 width=50) (actual time=0.005..5.145 rows=19681 loops=1)
-> Hash (cost=122.14..122.14 rows=3814 width=27) (actual time=2.391..2.391 rows=3813 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 224kB
-> Seq Scan on brandlist b (cost=0.00..122.14 rows=3814 width=27) (actual time=0.004..1.326 rows=3813 loops=1)
-> Hash (cost=35.15..35.15 rows=215 width=39) (actual time=0.230..0.230 rows=215 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on povlist p (cost=0.00..35.15 rows=215 width=39) (actual time=0.004..0.147 rows=215 loops=1)
Total runtime: 1194.894 ms
(23 行)
クエリの実行に1秒以上かかっています。
povgroupsのtourokubiにインデックスを設定した後は以下のようになりました。
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.14..42.39 rows=3 width=171) (actual time=0.120..0.188 rows=3 loops=1)
-> Nested Loop (cost=1.14..9048124.95 rows=658070 width=171) (actual time=0.120..0.187 rows=3 loops=1)
-> Nested Loop (cost=0.99..7975722.70 rows=658070 width=136) (actual time=0.115..0.174 rows=3 loops=1)
-> Nested Loop (cost=0.71..5838805.47 rows=658070 width=113) (actual time=0.111..0.157 rows=3 loops=1)
-> Index Scan Backward using povgroups_tourokubi on povgroups pg (cost=0.43..3665680.24 rows=658070 width=67) (actual time=0.101..0.133 rows=3 loops=1)
Filter: (memo IS NOT NULL)
Rows Removed by Filter: 158
-> Index Scan using gamelist_pkey on gamelist g (cost=0.29..3.29 rows=1 width=50) (actual time=0.006..0.006 rows=1 loops=3)
Index Cond: (id = pg.game)
-> Index Scan using brandlist_pkey on brandlist b (cost=0.28..3.24 rows=1 width=27) (actual time=0.004..0.005 rows=1 loops=3)
Index Cond: (id = g.brandname)
-> Index Scan using povlist_pkey on povlist p (cost=0.14..1.62 rows=1 width=39) (actual time=0.002..0.003 rows=1 loops=3)
Index Cond: (id = pg.pov)
Total runtime: 0.266 ms
(14 行)
実行時間が1ミリ秒以下になりました…
Sort Keyがなくなって、かわりに設定しインデックスであるpovgroups_tourokubiが使用されています。
PostgreSQLのマニュアルはインデックスとORDER BYによると、
ORDER BY句に設定される列にはインデックスを設定した方がよいです。
ap2=# EXPLAIN ANALYZE SELECT * FROM povgroups pg , gamelist g , brandlist b , povlist p WHERE pg.memo IS NOT NULL AND g.id = pg.game AND g.brandname = b.id AND p.id = pg.pov ORDER BY pg.tourokubi DESC OFFSET 0 LIMIT 3 ;当時、povgroupsのtourokubiにインデックスを設定していませんでした。
その時のEXPLAINの結果は以下のとおりでした。
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=80949.23..80949.24 rows=3 width=171) (actual time=1194.803..1194.805 rows=3 loops=1)
-> Sort (cost=80949.23..82594.36 rows=658051 width=171) (actual time=1194.801..1194.801 rows=3 loops=1)
Sort Key: pg.tourokubi
Sort Method: top-N heapsort Memory: 26kB
-> Hash Join (cost=2162.89..72444.05 rows=658051 width=171) (actual time=26.481..801.675 rows=666273 loops=1)
Hash Cond: (pg.pov = p.id)
-> Hash Join (cost=2125.05..63358.01 rows=658051 width=136) (actual time=26.243..579.539 rows=666273 loops=1)
Hash Cond: (pg.game = g.id)
-> Seq Scan on povgroups pg (cost=0.00..44781.68 rows=658051 width=67) (actual time=0.003..246.225 rows=666273 loops=1)
Filter: (memo IS NOT NULL)
Rows Removed by Filter: 1320643
-> Hash (cost=1879.04..1879.04 rows=19681 width=73) (actual time=26.230..26.230 rows=19678 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 2098kB
-> Hash Join (cost=169.81..1879.04 rows=19681 width=73) (actual time=2.403..19.669 rows=19678 loops=1)
Hash Cond: (g.brandname = b.id)
-> Seq Scan on gamelist g (cost=0.00..1364.81 rows=19681 width=50) (actual time=0.005..5.145 rows=19681 loops=1)
-> Hash (cost=122.14..122.14 rows=3814 width=27) (actual time=2.391..2.391 rows=3813 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 224kB
-> Seq Scan on brandlist b (cost=0.00..122.14 rows=3814 width=27) (actual time=0.004..1.326 rows=3813 loops=1)
-> Hash (cost=35.15..35.15 rows=215 width=39) (actual time=0.230..0.230 rows=215 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on povlist p (cost=0.00..35.15 rows=215 width=39) (actual time=0.004..0.147 rows=215 loops=1)
Total runtime: 1194.894 ms
(23 行)
クエリの実行に1秒以上かかっています。
povgroupsのtourokubiにインデックスを設定した後は以下のようになりました。
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.14..42.39 rows=3 width=171) (actual time=0.120..0.188 rows=3 loops=1)
-> Nested Loop (cost=1.14..9048124.95 rows=658070 width=171) (actual time=0.120..0.187 rows=3 loops=1)
-> Nested Loop (cost=0.99..7975722.70 rows=658070 width=136) (actual time=0.115..0.174 rows=3 loops=1)
-> Nested Loop (cost=0.71..5838805.47 rows=658070 width=113) (actual time=0.111..0.157 rows=3 loops=1)
-> Index Scan Backward using povgroups_tourokubi on povgroups pg (cost=0.43..3665680.24 rows=658070 width=67) (actual time=0.101..0.133 rows=3 loops=1)
Filter: (memo IS NOT NULL)
Rows Removed by Filter: 158
-> Index Scan using gamelist_pkey on gamelist g (cost=0.29..3.29 rows=1 width=50) (actual time=0.006..0.006 rows=1 loops=3)
Index Cond: (id = pg.game)
-> Index Scan using brandlist_pkey on brandlist b (cost=0.28..3.24 rows=1 width=27) (actual time=0.004..0.005 rows=1 loops=3)
Index Cond: (id = g.brandname)
-> Index Scan using povlist_pkey on povlist p (cost=0.14..1.62 rows=1 width=39) (actual time=0.002..0.003 rows=1 loops=3)
Index Cond: (id = pg.pov)
Total runtime: 0.266 ms
(14 行)
実行時間が1ミリ秒以下になりました…
Sort Keyがなくなって、かわりに設定しインデックスであるpovgroups_tourokubiが使用されています。
PostgreSQLのマニュアルはインデックスとORDER BYによると、
単に問い合わせによって返される行を見つけ出すだけではなく、インデックスは、その行を指定した順番で取り出すことができます。 これにより、別途ソート処理を行うことなく、問い合わせのORDER BY指定に従うことが可能です。 PostgreSQLが現在サポートするインデックスの種類の中で、B-Treeのみがソート出力を行うことができます。ということです。
ORDER BY句に設定される列にはインデックスを設定した方がよいです。