以下の記述によると ORDER BY するフィールドについて btree で INDEX を作っておくと高速化出来るとのこと。
- PostgreSQL マニュアル / インデックスとORDER BY
- PostgreSQLチューニング実践テクニック / 第４回：インデックスの活用によるチューニング
ところが、
EXPLAIN でいくら調べても Seq Scan 後に Sort されるばかりで Index Scan してくれない。
以下の人の報告によるとデータ件数により閾値が働くらしく、あまり大きなデータセットだと強制的に Seq Scan でプラニングされてしまうらしい。
どうにかして、Index Scan を強制出来んものかと調べていたら、以下のフォーラムに答えを発見。
以下のように設定してみろと。
set enable_seqscan = false;
ただし Index Scan は Seq Scan に比べてクソ遅いので止めとけとのこと。
実際に試してみた結果は以下の通り。
psql
=> \d sampletable
Table "public.sampletable"
Column | Type | Modifiers
-----------------+--------------------------+---------------
sampletable_id | text | not null
＜中略＞
Indexes:
"sampletable_pkey" PRIMARY KEY, btree (sampletable_id)
=> EXPLAIN ANALYZE SELECT * FROM sampletable;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on sampletable (cost=0.00..17606.49 rows=714449 width=79) (actual time=3.960..1361.018 rows=714449 loops=1)
Total runtime: 2559.071 ms
(2 rows)
=> EXPLAIN ANALYZE SELECT * FROM sampletable ORDER BY sampletable_id LIMIT 61470;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..224025.58 rows=61470 width=79) (actual time=0.097..978.127 rows=61470 loops=1)
-> Index Scan using sampletable_pkey on sampletable (cost=0.00..2603788.10 rows=714449 width=79) (actual time=0.092..771.948 rows=61470 loops=1)
Total runtime: 1081.756 ms
(3 rows)
=> EXPLAIN ANALYZE SELECT * FROM sampletable ORDER BY sampletable_id LIMIT 61471;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=223874.05..224027.73 rows=61471 width=79) (actual time=23054.728..23433.328 rows=61471 loops=1)
-> Sort (cost=223874.05..225660.17 rows=714449 width=79) (actual time=23054.724..23227.204 rows=61471 loops=1)
Sort Key: sampletable_id
-> Seq Scan on sampletable (cost=0.00..17606.49 rows=714449 width=79) (actual time=0.022..1499.600 rows=714449 loops=1)
Total runtime: 23536.696 ms
(5 rows)
=> EXPLAIN ANALYZE SELECT * FROM sampletable ORDER BY sampletable_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Sort (cost=223874.05..225660.17 rows=714449 width=79) (actual time=20816.544..22215.092 rows=714449 loops=1)
Sort Key: sampletable_id
-> Seq Scan on sampletable (cost=0.00..17606.49 rows=714449 width=79) (actual time=0.020..1482.260 rows=714449 loops=1)
Total runtime: 23418.107 ms
(4 rows)
=> set enable_seqscan=false;
SET
=> EXPLAIN ANALYZE SELECT * FROM sampletable ORDER BY sampletable_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using sampletable_pkey on sampletable (cost=0.00..2603788.10 rows=714449 width=79) (actual time=2.205..8384.373 rows=714449 loops=1)
Total runtime: 9585.890 ms
(2 rows)
結果だけ簡潔にまとめると以下のようになった。
| 条件 | データ件数 | 所要時間 |
|---|
| sortなし全件 | 714449 | 2559.071 ms |
| LIMIT 61470 | 61470 | 1081.756 ms |
| LIMIT 61471 | 61471 | 23536.696 ms |
| enable_seqscan=true | 714449 | 23418.107 ms |
| enable_seqscan=false | 714449 | 9585.890 ms |
確かに、データ数 10 倍に増えても Seq Scan + Sort で誤差の範囲でしか速度が変わらないが、Index Scan だとデータ数にほぼ比例して遅くなってるという目を疑いそうな結果になってしまった。Index Scan は Seq Scan の概ね 10 倍遅いという結果のようだ。とは言え、上記の条件で全件ソートする場合、依然として Index Scan のが 2 倍ちょい速いわけで、PostgreSQL の plannner の閾値の見積りは、最適化が少々甘いと言える。いっその事 FFTW みたく実測(measure)モードとかあっても良いんじゃないのか？と思う。
何か手はあるんじゃないのかと思い、少し調べてみたところ、マニュアルの以下の部分にチューニング出来そうな雰囲気のことは書いてはあった。
しかし具体的にどこどういじれば良いのかが見つからないので、ちょっとハードルが高そうである。
コメントをかく