関数つきインデックス

「テーブルの値を関数で評価して、その結果で絞り込む」といった場合に、関数つきインデックスを作成しておくと高速にSQLを実行できます。例えば、以下のような文字列型のカラムを持つテーブルがあったとして、

test=# select * from test01 limit 10;
                       str                       
-------------------------------------------------
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaa
 aaaaaaaaaaaaaaaaaaaaaa
(10 rows)

このテーブルから、strの長さが10文字の行数を取得したい場合、

test=# select count(str) from test01 where char_length(str) = 10;

インデックスなしや、素のstrのインデックスだけある場合はSeq Scanになります。

test=# EXPLAIN ANALYZE select count(str) from test01 where char_length(str) = 10;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.08..25.08 rows=1 width=146) (actual time=4.591..4.597 rows=1 loops=1)
   ->  Seq Scan on test01  (cost=0.00..25.07 rows=6 width=146) (actual time=0.495..4.220 rows=26 loops=1)
         Filter: (char_length((str)::text) = 10)
 Total runtime: 5.895 ms
(4 rows)

行数が少ないので数msですんでいますが、データが増えてくるとそれに比例して処理時間が増加します。

ここで、関数つきでインデックスを追加してやると、

test=# CREATE INDEX test01_str_length_idx ON test01 ( char_length(str)  );

探索がIndex Scanになり、行数が増えても高速に探索できます。

test=# EXPLAIN ANALYZE select count(str) from test01 where char_length(str) = 10;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18.61..18.61 rows=1 width=146) (actual time=1.661..1.668 rows=1 loops=1)
   ->  Index Scan using test01_str_length_idx on test01  (cost=0.00..18.59 rows=6 width=146) (actual time=0.043..0.428 rows=26 loops=1)
         Index Cond: (char_length((str)::text) = 10)
 Total runtime: 1.749 ms
(4 rows)