PostgreSQL の式インデックス（関数インデックス）もテーブルと同様に統計情報の収集対象です。よって、式インデックス作成後に ANALYZE を実行しないと、正しく活用されません。また、pg_stats ビューは式インデックスの統計情報も参照できます。
PostgreSQL のインデックスは列値そのものだけでなく、列値を用いた演算結果も対象にできます。
インデックス列は、基礎をなすテーブルにある列である必要はなく、そのテーブルの１つ以上の列から計算される関数やスカラ式とすることもできます。この機能は、ある演算結果に基づいた高速テーブルアクセスを行う時に有用です。
11.7. 式に対するインデックス
式インデックスも統計情報の収集対象です。なお、通常の（列値に対する）インデックスは統計情報の収集対象外です。
pg_statisticはインデックス式の値についての統計データも格納します。これらはあたかも値が実際のデータ列であるかのように表現されます。特にstarelidはインデックスを参照します。これは元のテーブル列の項目に対して冗長となるので、普通の式を持たないインデックス列では項目は作成されません。現在インデックス式用の項目は常にstainherit = falseを持ちます。
51.49. pg_statistic
式インデックスの統計情報もプランナが参照します。よって、式インデックス作成後に ANALYZE を実行しないと、式インデックスを適切に利用したプランが得られません。
また、pg_stats ビューのマニュアルからはテーブルの統計情報のみ参照できるように読めますが、実際には式インデックスの統計情報も参照できます。
51.88. pg_stats
以降で実際の挙動を確認します。
検証環境は以下の通りです。
- PostgreSQL 14.4
- autovacuum = off
テーブルを作成し、データを投入した後、VACUUM ANALYZE を実行します。その後、通常インデックスと式インデックスを作成します。
インデックス作成後の ANALYZE 実行有無によって、各インデックスを利用するクエリのプランがどうなるかを確認します。
=# CREATE TABLE tab1 (id integer);
=# INSERT INTO tab1 SELECT generate_series(1, 100000);
=# VACUUM ANALYZE tab1;
=# CREATE INDEX tab1_id_index ON tab1 (id);
=# CREATE INDEX tab1_id_bigint_index ON tab1 (CAST(id AS bigint));
-- (1) 通常インデックスかつANALYZE前
=# EXPLAIN SELECT * FROM tab1 WHERE id < 100;
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using tab1_id_index on tab1 (cost=0.29..6.06 rows=101 width=4)
Index Cond: (id < 100)
(2 rows)
-- (2) 式インデックスかつANALYZE前
=# EXPLAIN SELECT * FROM tab1 WHERE CAST(id AS bigint) < 100;
QUERY PLAN
-----------------------------------------------------------------------------------------
Bitmap Heap Scan on tab1 (cost=626.62..1569.62 rows=33333 width=4)
Recheck Cond: ((id)::bigint < 100)
-> Bitmap Index Scan on tab1_id_bigint_index (cost=0.00..618.29 rows=33333 width=0)
Index Cond: ((id)::bigint < 100)
(4 rows)
=# ANALYZE tab1;
-- (3) 通常インデックスかつANALYZE後
=# EXPLAIN SELECT * FROM tab1 WHERE id < 100;
QUERY PLAN
--------------------------------------------------------------------------------
Index Only Scan using tab1_id_index on tab1 (cost=0.29..5.97 rows=96 width=4)
Index Cond: (id < 100)
(2 rows)
-- (4) 式インデックスかつANALYZE後
testdb=# EXPLAIN SELECT * FROM tab1 WHERE CAST(id AS bigint) < 100;
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using tab1_id_bigint_index on tab1 (cost=0.29..9.97 rows=96 width=4)
Index Cond: ((id)::bigint < 100)
(2 rows)インデックス作成後に ANALYZE を実行しない場合でも、通常インデックスであれば行数推定は正確でした。ですが、式インデックスの場合は行数推定を大きく外しています。
インデックス作成後に ANALYZE を実行すると、式インデックスの場合でも行数推定が正確になりました。
この検証からもわかるように、式インデックスを作成した後に ANALYZE も実行することが重要といえます。
pg_stats ビューで式インデックスの統計情報も参照できることを確認します。
=# SELECT tablename, attname FROM pg_stats WHERE tablename LIKE '%tab1%';
tablename | attname
----------------------+---------
tab1 | id
tab1_id_bigint_index | id
(2 rows)テーブルと式インデックスの統計情報を参照できました。また、通常インデックスの統計情報が無いことも確認できました。
