ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL は統計情報の度数分布と実際の最小値・最大値との乖離を補正して行数推定することがある

データベース

PostgreSQL には統計情報の度数分布と実際の最小値・最大値が乖離した場合の影響を抑える機能があります。

コミットログを引用します。

When estimating the selectivity of an inequality "column > constant" or "column < constant", and the comparison value is in the first or last histogram bin or outside the histogram entirely, try to fetch the actual column min or max value using an index scan (if there is an index on the column). If successful, replace the lower or upper histogram bound with that value before carrying on with the estimate. This limits the estimation error caused by moving min/max values when the comparison value is close to the min or max. Per a complaint from Josh Berkus.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40608e7f949fb7e4025c0ddd5be01939adc79eec

この機能が作動するのは以下の条件がそろったときです。

  • インデックスが存在する場合
  • 不等式の選択性を評価する場合
  • 比較値が度数分布の端もしくは外側にある場合

この機能によって実行計画の推定行数がどのように変化するかを PostgreSQL 14.2 で検証しました。PostgreSQL 10.20 でも同様の結果が得られることを確認しています。

検証手順を以下に示します。

-- tab1 はインデックス(PK)あり、tab2 はインデックスなし。
-- 以降のクエリは対象のテーブル名以外同様とする。
CREATE TABLE tab1 (id integer PRIMARY KEY);

INSERT INTO tab1 SELECT generate_series(1, 1000);

VACUUM ANALYZE;

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (1)

INSERT INTO tab1 SELECT generate_series(1001, 1099);

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (2)

VACUUM ANALYZE;

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (3)

この機能が作動したとすれば、インデックスの有無で (2) の実行計画の推定行数が異なるはずです。

実行結果を以下に示します。

【インデックスあり】

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (1)
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.29 rows=1 width=4)
   Index Cond: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (2)
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.43 rows=9 width=4)
   Index Cond: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (3)
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..6.01 rows=99 width=4)
   Index Cond: (id > 1000)
(2 rows)

【インデックスなし】

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (1)
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab2  (cost=0.00..17.50 rows=1 width=4)
   Filter: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (2)
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab2  (cost=0.00..17.50 rows=1 width=4)
   Filter: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (3)
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on tab2  (cost=0.00..18.74 rows=99 width=4)
   Filter: (id > 1000)
(2 rows)

最上位ノードの推定行数を表にまとめます。

実行計画 インデックスあり インデックスなし
(1) 1 1
(2) 9 1
(3) 99 99

(2) において「インデックスなし」より「インデックスあり」の推定行数は大きく見積もられています。これにより、実際の行数との乖離が縮まっています。

先の検証ではテーブル全体の行数が 1000 ⇒ 1099 に増加していました。この行数増加が先の検証での推定行数に影響していないことを確かめるため、行数を変えずに列値の最大値を更新して検証します。

-- tab1 はインデックス(PK)あり、tab2 はインデックスなし。
-- 以降のクエリは対象のテーブル名以外同様とする。
CREATE TABLE tab1 (id integer PRIMARY KEY);

INSERT INTO tab1 SELECT generate_series(1, 1000);

VACUUM ANALYZE;

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (1)

-- 先の検証では INSERT だったが、今回は UPDATE する。
UPDATE tab1 SET id = 1099 WHERE id = 1000;

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (2)

VACUUM ANALYZE;

EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (3)

【インデックスあり】

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (1)
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.29 rows=1 width=4)
   Index Cond: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (2)
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.43 rows=9 width=4)
   Index Cond: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab1 WHERE id > 1000; -- (3)
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.43 rows=9 width=4)
   Index Cond: (id > 1000)
(2 rows)

【インデックスなし】

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (1)
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab2  (cost=0.00..17.50 rows=1 width=4)
   Filter: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (2)
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab2  (cost=0.00..17.50 rows=1 width=4)
   Filter: (id > 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab2 WHERE id > 1000; -- (3)
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab2  (cost=0.00..17.50 rows=9 width=4)
   Filter: (id > 1000)
(2 rows)
実行計画 インデックスあり インデックスなし
(1) 1 1
(2) 9 1
(3) 9 9

(2) の結果が先の検証と同様になりました。よって、インデックスの最大値が更新されたことにより、推定行数が影響を受けたとわかります。

以上の検証により、今回取り上げた機能が「統計情報の度数分布と実際の最小値・最大値が乖離した場合の影響」の緩和策となっていることを確認できました。