PostgreSQL 8.1 の count(*) を高速化

PostgreSQL 8.1 で count(*) の取得が遅いので高速化してみた。

↓高速化対応前

ec_shop=> EXPLAIN ANALYZE SELECT count(*) FROM order WHERE is_settled = 't' AND delete_flag = 'f';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=146378.16..146378.17 rows=1 width=0) (actual time=3108.970..3108.971 rows=1 loops=1)
   ->  Seq Scan on order  (cost=0.00..144762.80 rows=646145 width=0) (actual time=0.241..3091.596 rows=21089 loops=1)
         Filter: (is_settled AND (NOT delete_flag))
 Total runtime: 3109.115 ms
(4 rows)

↓高速化対応後

ec_shop=> EXPLAIN ANALYZE SELECT count(*) FROM order WHERE id > 0 AND is_settled = 't' AND delete_flag = 'f';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=133584.15..133584.17 rows=1 width=0) (actual time=540.989..540.990 rows=1 loops=1)
   ->  Bitmap Heap Scan on order  (cost=8744.16..133045.70 rows=215382 width=0) (actual time=311.333..524.769 rows=21089 loops=1)
         Recheck Cond: (id > 0)
         Filter: (is_settled AND (NOT delete_flag))
         ->  Bitmap Index Scan on order_idx_is_settled  (cost=0.00..8744.16 rows=430763 width=0) (actual time=306.146..306.146 rows=21092 loops=1)
               Index Cond: ((id > 0) AND (is_settled = true))
 Total runtime: 541.263 ms
(7 rows)

INDEX を利用しているカラムを指定(上の例では id ）する事で検索するレコード行数を制限した。
結果、検索する行数が減って結果の表示が速くなった。


↓参考
https://wiki.postgresql.org/wiki/Slow_Counting/ja
https://www.postgresql.jp/document/8.1/html/performance-tips.html


こういうのパズルみたいで楽しい。