PostgreSQL ソート インデックス

• 最新順(desc)でソートするクエリの場合、応答時間高速化の効果が期待できる

Requirements

psql (9.3.2)

インデックス作成

CREATE INDEX idx_hoge_code_desc ON hoge(code DESC);

• hoge に対して、テーブルロック が発生するので、サービス停止中等に実行するのがベター
• レコードの多いテーブル の場合、実行時間が長時間かかる可能性があるので、事前に同じデータのDBを同等スペックの別サーバ等に準備して、時間を予測しておく。キャッシュは恐らく実行時間には影響しなさそう。
• 途中で止めたい場合は Ctrl + c でキャンセル可能、恐らく問題ない

（何かあって）インデックス作成 削除する場合

DROP index idx_hoge_code_desc;

確認

# \d hoge
                                        Table "public.hoge"
     Column     |            Type             |                        Modifiers
----------------+-----------------------------+---------------------------------------------------------
 code           | integer                     | not null default nextval('hoge_code_seq'::regclass)
 …
Indexes:
    "idx_hoge_code_desc" btree (code DESC)

# \d idx_hoge_code_desc
Index "public.idx_hoge_code_desc"
     Column     |  Type   |   Definition
----------------+---------+----------------
 code           | integer | code
 …
btree, for table "public.hoge"

• DESC の情報が表示されない… 確認する方法知ってる方いらっしゃったら教えてください

インデックス前後 統計情報 比較

• インデックス作成 前

# explain analyze select * from hoge where order by code desc limit 50;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..2146.45 rows=50 width=44) (actual time=6797.790..6822.734 rows=50 loops=1)
   ->  Index Scan Backward using hoge_pkey on hoge  (cost=0.44..1418472.53 rows=33049 width=44) (actual time=6797.787..6822.728 rows=50 loops=1)
   …
 Total runtime: 6822.785 ms

• インデックス作成 後

# explain analyze select * from hoge where order by code desc limit 50;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..44.30 rows=50 width=44) (actual time=0.089..0.221 rows=50 loops=1)
   ->  Index Scan using idx_hoge_code_desc on hoge  (cost=0.56..28818.94 rows=32950 width=44) (actual time=0.086..0.206 rows=50 loops=1)
 Total runtime: 0.279 ms

早なった