関数つきインデックス
「テーブルの値を関数で評価して、その結果で絞り込む」といった場合に、関数つきインデックスを作成しておくと高速に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)