PostgreSQL が空テーブルの統計情報を更新しないことによる実行計画への影響
概要
PostgreSQL の ANALYZE コマンドの説明に以下の記載があります。
解析しようとするテーブルが完全に空である場合、ANALYZEはそのテーブルに対する新しい解析情報を記録しません。 これまでの統計情報はすべて保持されます。
ANALYZE
「これまでの統計情報はすべて保持されます」というのがポイントで、以前の統計情報を基に実行計画を立案してしまうことがあります。このケースについて、検証と考察を行います。
検証方法
PostgreSQL 12.1 で検証を行いました。
統計情報の更新タイミングを制御するため、autovacuum = off に設定しています。
(key, value) 列で構成されたテーブルを作成します。
key 列にはインデックスを作成します。
初期データとして (1...10000, 'aaaaa') を投入します。
| key | value |
|---|---|
| 1 | aaaaa |
| 2 | aaaaa |
| （中略） | （中略） |
| 10000 | aaaaa |
VACUUM ANALYZE コマンドを実行します。
ここで key = 1 を条件に SELECT を実行すれば、key 列はインデックスがありユニークという統計情報があるため、実行計画で Index Scan を選ぶであろうことが推測できます。
TRUNCATE コマンドを実行し、続けて VACUUM ANALYZE コマンドを実行します。
テーブルは完全に空ですが、ANALYZE コマンドは統計情報を更新しません。
新たなデータとして (1, 'aaaaa')×10000行を投入します。
初期データと異なり、key 列はユニークではありません。
| key | value |
|---|---|
| 1 | aaaaa |
| 1 | aaaaa |
| （中略） | （中略） |
| 1 | aaaaa |
ここで key = 1 を条件に SELECT を実行します。key 列はユニークでありませんが、「key 列はユニークである」という統計情報を参照し、実行計画で Index Scan が選ばれるはずです。
VACUUM ANALYZE コマンドを実行します。
再度 key = 1 を条件に SELECT を実行します。今度は key 列の値が "1" しかないという統計情報を参照し、実行計画で Index Scan 以外が選ばれるはずです。
統計情報で key 列がユニークか否かどちらに判定したかは pg_stats.n_distinct を参照します。PostgreSQL のドキュメントより n_distinct の説明を引用します。
ゼロより大きい値は列内の個別値の推定数です。 ゼロより小さければ行数で個別値を割算した数字の負数です。 （テーブルが肥大するにつれ個別値の増大があり得るとANALYZEが判断した場合に負変換形式が使われます。 正変換形式は列の取り得る値が固定数を持つと思われる場合に使用されます）。 例えば-1は個別値の数が行数と等しいような、一意な列を表します。
51.88. pg_stats
n_distinct の値が "1" なら列内の個別値が1個、つまり列はユニークでないといえます。また、n_distinct の値が "-1" なら、列はユニークといえます。
検証結果
=# CREATE TABLE tab1(key INTEGER, value CHAR(5));
=# CREATE INDEX ON tab1 (key);
=# INSERT INTO tab1 SELECT generate_series(1, 10000), 'aaaaa';
=# VACUUM ANALYZE tab1;
=# SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'tab1' AND attname = 'key';
tablename | attname | n_distinct
-----------+---------+------------
tab1 | key | -1
(1 row)
=# EXPLAIN SELECT * FROM tab1 WHERE key = 1;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using tab1_key_idx on tab1 (cost=0.29..8.30 rows=1 width=10)
Index Cond: (key = 1)
(2 rows)
=# TRUNCATE tab1;
=# VACUUM ANALYZE tab1;
=# SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'tab1' AND attname = 'key';
tablename | attname | n_distinct
-----------+---------+------------
tab1 | key | -1
(1 row)
=# INSERT INTO tab1 SELECT 1, 'aaaaa' FROM generate_series(1, 10000);
=# EXPLAIN SELECT * FROM tab1 WHERE key = 1;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using tab1_key_idx on tab1 (cost=0.29..8.30 rows=1 width=10)
Index Cond: (key = 1)
(2 rows)
=# VACUUM ANALYZE tab1;
=# SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'tab1' AND attname = 'key';
tablename | attname | n_distinct
-----------+---------+------------
tab1 | key | 1
(1 row)
=# EXPLAIN SELECT * FROM tab1 WHERE key = 1;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on tab1 (cost=0.00..180.00 rows=10000 width=10)
Filter: (key = 1)
(2 rows)
まとめ
検証方法で事前に予測した通りの結果が得られました。
空テーブルの統計情報は ANALYZE コマンドを実行してもクリアされないということを理解し、データ投入後の ANALYZE コマンドを忘れず実行しましょう。
