LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@nuko_yokohama(nuko yokohama)

PostgreSQL 18がやってくる(9) Statistics Import and Export

Posted at

はじめに

にゃーん。
今日はPostgreSQL 18で追加された「Statistics Import and Export」について調べてみました。

Statistics（統計情報）

統計情報はテーブルに格納されたデータをサンプリングして集計した情報である。
与えられたSQL問い合わせを解析後に、プランナを用いて実行計画を生成・選択し、エグゼキュータでその実行計画に基づいた処理を行う。
統計情報はANALYZE,VACUUM等のメンテナンス用コマンドの実行、および自動バキューム機能の背景で更新される。

これまで、PostgreSQLの本体機能では統計情報のインポート・エクスポートの機能はなかった。1
PostgreSQL 18でそれが追加されたようである。

統計情報のインポート・エクスポートがあると何が嬉しい？

一番ありそうなケースとしては、本番環境の統計情報を用いて、試験環境上で本番環境と同様の実行計画を確認する、というものがある。

性能問題が発生した場合、本番環境の情報そのものを試験環境上に再現し、その環境で問題となったSQLの実行計画や実行時間等を確認できるのが理想ではあるが、実際には以下のような問題のために、本番環境のデータそのものを使うことができないことも多い。

  • 機密性の高い情報（特に個人情報）を含むデータを持ち出すことができない
  • 本番環境のデータ量が大きく、試験環境上に膨大なデータをロードできない

このようなケースで、統計情報だけを再現して解析できないか、と思っているPostgreSQLサポートの仕事をしている人は多いのではないだろうか。

概要

Commitfest 2025-03でコミットされた項目である。

タイトル Statistics Import and Export
Topic Performance
Created 2023-08-31 19:21:04
Last modified 2025-03-18 09:47:04
Emails Statistics Import and Export

Createdが2023年8月末なので、そこから2年近くかけて長々と議論されている。
（ML自体はちら見しかしてないけど、当初はEXPORT STATISTICS / IMPORT STATISTICSのようなSQLコマンド案も検討されていたらしい）

PostgreSQL 18での改善点（たぶん）

たぶん、この機能に対応したものとしては、以下の2項目の修正がある。

  • pg_dumpのオプションの追加
  • リレーション毎および列毎のオプティマイザの統計情報を変更する関数の追加

pg_dumpのオプションの追加

PostgreSQL 18文書ののpg_dump2のページを見ると

--no-statistics

    Do not dump statistics.

--statistics-only

    Dump only the statistics, not the schema (data definitions) or data. Statistics for tables, materialized views, and indexes are dumped.

といったオプションが追加されている。実際にpg_dumpのヘルプを見ると

$ pg_dump --version
pg_dump (PostgreSQL) 18devel
$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.
（略）
  --no-statistics              do not dump statistics
（略）
  --statistics-only            dump only the statistics, not schema or data
（略）
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
$

となっている。

リレーション毎および列毎のオプティマイザの統計情報を変更する関数の追加

以下の4つの関数が追加された。

関数名 説明
pg_restore_relation_stats() テーブルレベルの統計情報を更新する。
pg_dumpに--statistics-onlyオプションを付与すると、テーブル毎にこの関数を使ったSQLが生成される。
pg_restore_attribute_stats() 列レベルの統計情報を更新する。
pg_dumpに--statistics-onlyオプションを付与すると、テーブル毎にこの関数を使ったSQLが生成される。
pg_clear_relation_stats() 指定されたリレーションのテーブルレベルの統計情報を消去する。
pg_clear_attribute_stats() 指定された列レベルの統計情報を消去する。

詳細はPostgreSQL 文書Database Object Statistics Manipulation Functions参照。

統計情報インポート・エクスポートの実験

  • データベースから、スキーマ情報（--schema-only）, 統計情報（--statistics-only）の2つのダンプを作成（エクスポート）する。
  • 別のデータベースを新規作成し、そこにスキーマ情報をリストアし、更に統計情報をリストア（インポート）する。

という手順を試してみる。

検証手順

  • 2つのデータベース（testdb1,testdb2）を作成する。
  • データベース1（testdb1）に対してpgbenchを使ってデータを生成する。
    • 今回は--unlogged-tables -s -i 100のオプションで生成する。
    • この処理の最後にVACUUMは実行されるので、手動でのANALYZEはかけない
  • testdb1から、2つのダンプファイルを作成する。
    • ダンプ①：スキーマのみ
    • ダンプ②：統計情報のみ（統計情報のエクスポート）
  • testdb2（テーブル等何もない状態）に対してダンプ①の内容をリストアしてスキーマをリストアする。
  • testdb2に対してダンプ②の内容をリストア（統計情報のインポート）を行う。

検証

ロード元情報の生成

データベースを作成し、testdb1にデータをロードする。

$ createdb testdb1
$ createdb testdb2
$ pgbench -q --unlogged-tables -i -s 100 testdb1
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 19.88 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 12.77 s, vacuum 1.44 s, primary keys 5.67 s).
$
$ psql testdb1 -c "\d"
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

この状態で、pgbench_accountsテーブルの統計情報（pg_classの一部情報）と、phbench_accountsテーブルのaid列の統計情報（pg_statの情報）を確認する。

SELECT * FROM pg_class WHERE relname = 'pgbench_accounts';
-[ RECORD 1 ]-------+-----------------
oid                 | 17243
relname             | pgbench_accounts
（略）
relpages            | 163935
reltuples           | 1e+07
（略）
reloptions          | {fillfactor=100}
relpartbound        |
  • relpages が 163935
  • reltuples が 1e+07

になっている。

同様に、aidに関するpg_statsの内容を確認する。

SELECT * FROM pg_stats WHERE tablename = 'pgbench_accounts' and attname = 'aid';
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname             | public
tablename              | pgbench_accounts
attname                | aid
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {202,102981,186932,295469,396364,491295,580200,691961,789102,887906,992670,1082830,1185283,1282845,1392116,1496038,1601925,1708536,1800800,1904134,2015175,2107177,2207968,2306222,2407679,2498369,2600655,2704436,2794225,2900161,3000809,3098785,3195967,3297153,3400959,3506560,3611174,3724172,3831007,3910212,4004889,4110323,4207622,4318832,4407394,4508261,4615179,4714502,4838380,4936410,5021386,5118124,5224184,5324584,5409281,5504100,5606067,5701194,5795212,5901847,6014239,6120474,6218626,6324358,6424110,6525519,6615489,6705850,6808518,6916014,7014634,7109569,7190262,7289520,7395776,7502947,7602610,7696250,7797450,7890249,7985488,8097459,8195387,8278495,8369879,8462652,8562648,8670678,8777171,8867856,8957037,9055955,9166838,9287990,9395257,9490087,9585257,9696292,9802422,9901165,9999874}
correlation            | 1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
range_length_histogram |
range_empty_frac       |
range_bounds_histogram |

スキーマと統計情報のダンプ

次にtestdb1からダンプ①とダンプ②を生成する。

$ pg_dump testdb1 --schema-only > dump1.txt
$ pg_dump testdb1 --statistics-only > dump2.txt
$

さて、dump2.txtの内容をざっと見てみると

  • pg_catalog.pg_restore_relation_stats()
  • pg_catalog.pg_restore_attribute_stats()

の関数をSELECT文の中で実行しているのがわかる。
名前から推測すると

関数名 内容
pg_restore_relation_stats() リレーション（テーブル）の統計情報を設定する
pg_restore_attribute_stats() 列の統計情報を設定する

だと思われる。
pgbench_accountsテーブルに対するpg_restore_relation_stats()を含むダンプ結果は以下のようになっている。

SELECT * FROM pg_catalog.pg_restore_relation_stats(
        'version', '180000'::integer,
        'schemaname', 'public',
        'relname', 'pgbench_accounts',
        'relpages', '163935'::integer,
        'reltuples', '1e+07'::real,
        'relallvisible', '163935'::integer,
        'relallfrozen', '163935'::integer

同様に、pgbench_accountsテーブルのaid列に対するpg_restore_relation_stats()を含むダンプ結果は以下のようになっている。

SELECT * FROM pg_catalog.pg_restore_attribute_stats(
        'version', '180000'::integer,
        'schemaname', 'public',
        'relname', 'pgbench_accounts',
        'attname', 'aid',
        'inherited', 'f'::boolean,
        'null_frac', '0'::real,
        'avg_width', '4'::integer,
        'n_distinct', '-1'::real,
        'histogram_bounds', '{1291,104116,199210,296747,405472,510215,608202,697506,787612,886962,986650,1090821,1198489,1299964,1394042,1493898,1596413,1691514,1799730,1900473,1997932,2104966,2197069,2286913,2394862,2495289,2596356,2696193,2797842,2894879,2992962,3094084,3197517,3298637,3392298,3501566,3600174,3691425,3795028,3898344,4008606,4122378,4216826,4307612,4407529,4501581,4599542,4708353,4807296,4905132,5005802,5104508,5214602,5321277,5422584,5516353,5612240,5697724,5792144,5907602,6018318,6113929,6228788,6329540,6438817,6537314,6634250,6734005,6831244,6917103,7015041,7117619,7226234,7318893,7411333,7528255,7622165,7721743,7832131,7937141,8030219,8135315,8224764,8325874,8429616,8513793,8615726,8717252,8826804,8931979,9030454,9133285,9225233,9328865,9426936,9518427,9619092,9716573,9808143,9909959,9999638}'::text,
        'correlation', '1'::real
);

--statistics-onlyオプションによるダンプファイルは、以下を生成するようだ。

  • ダンプ対象のテーブル数分、pg_restore_relation_stats()を実行するSELECT文
  • ダンプ対象のテーブルに含まれる列数分、pg_restore_attribute_stats()を実行するSELECT文

新しいデータベースへのスキーマと統計情報のインポート

次に、空のデータベース（testdb2）にダンプをリストアする。
まず、スキーマ情報（dump1.txt）をリストアしてスキーマのみを作成する。

$ psql testdb2 -c "\d"
Did not find any relations.
$ psql testdb2 -f dump1.txt
（略）
$ psql testdb2 -c "\d"
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

$

この状態で、統計情報（``dump2.txt`）をインポートする。

$ psql -p 18001 -U postgres testdb2 -f dump2.txt
（略）
 pg_restore_relation_stats
---------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)
（略）
$

インポートされた統計情報の確認

インポートされたデータベース上のpgbench_accountsに関する統計情報と、aid列に関する統計情報を確認する。

$ psql -p 18001 -U postgres testdb2 -a -f test.sql
\x
Expanded display is on.
SELECT * FROM pg_class WHERE relname = 'pgbench_accounts';
-[ RECORD 1 ]-------+-----------------
oid                 | 17287
relname             | pgbench_accounts
（略）
relpages            | 163935
reltuples           | 1e+07
（略）
reloptions          | {fillfactor=100}
relpartbound        |

SELECT * FROM pg_stats WHERE tablename = 'pgbench_accounts' and attname = 'aid';
-[ RECORD 1 ]----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname             | public
tablename              | pgbench_accounts
attname                | aid
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {1291,104116,199210,296747,405472,510215,608202,697506,787612,886962,986650,1090821,1198489,1299964,1394042,1493898,1596413,1691514,1799730,1900473,1997932,2104966,2197069,2286913,2394862,2495289,2596356,2696193,2797842,2894879,2992962,3094084,3197517,3298637,3392298,3501566,3600174,3691425,3795028,3898344,4008606,4122378,4216826,4307612,4407529,4501581,4599542,4708353,4807296,4905132,5005802,5104508,5214602,5321277,5422584,5516353,5612240,5697724,5792144,5907602,6018318,6113929,6228788,6329540,6438817,6537314,6634250,6734005,6831244,6917103,7015041,7117619,7226234,7318893,7411333,7528255,7622165,7721743,7832131,7937141,8030219,8135315,8224764,8325874,8429616,8513793,8615726,8717252,8826804,8931979,9030454,9133285,9225233,9328865,9426936,9518427,9619092,9716573,9808143,9909959,9999638}
correlation            | 1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
range_length_histogram |
range_empty_frac       |
range_bounds_histogram |

エクスポート元データベース(testdb1）と同様の統計情報がインポートされていることがわかる。

実行計画はどうなる？

ここまでの手順でtestdb2ににも同じ統計情報が設定されたが、では、レコードが1件もない状態でも、testdb1と同じ実行計画が作成されるのだろうか？
全件検索とaid列へのインデックス検索のケースを試してみると・・・

EXPLAIN SELECT count(aid) FROM pgbench_accounts;
                              QUERY PLAN
----------------------------------------------------------------------
 Aggregate  (cost=0.00..0.01 rows=1 width=8)
   ->  Seq Scan on pgbench_accounts  (cost=0.00..0.00 rows=1 width=4)
(2 rows)

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid = 1;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..0.00 rows=1 width=97)
   Filter: (aid = 1)
(2 rows)

そんな都合良くはいかない。reltuples, relpagesの値は書き換わっているはずなのに何故・・・

PostgreSQL 16（日本語文書）の行数推定の例を読むと、

これらの値は最後にそのテーブルをVACUUMまたはANALYZEを行った時点のものです。 プランナはその後、テーブル内の実際のページ数を取り出します（これはテーブルスキャンを行わない安価な操作です）。 それがrelpagesと異なる場合、reltuplesを得られたページ数の割合に応じて変更して現在の推定行数を求めます。

とある。今回、testdb2はデータ0件のままになっている。つまり、実際のページ数は0のまま。
ページ数が0なので、reltuplesも実質0と見なされてcostを計算してしまうようだ。
（なので、WHERE aid = 1のケースもインデックス検索にはならないのだろう）

おわりに

今回は統計情報のインポートやエクスポートに関する、PostgreSQL 18の改善内容について調べてみました。統計情報のインポートやエクスポート自体の挙動は確認できましたが、この機能だけだと、本番環境から統計情報だけエクスポートして、試験環境に統計情報だけインポートするだけなので、残念ながらレコードをロードせずに、本番環境と同じ実行計画を生成を再現させる、というのはできなさそうです。
もしかすると、何か手順を忘れているだけかもしれないですが・・・

  1. 拡張機能として統計情報のインポート・エクスポートを行うために、pg_dbms_statsがある。これは統計情報のインポート・エクスポートだけでなく、統計情報の固定化する機能ももつ。ただ、AWS Aurora等のPostgreSQL PaaSでは対応していない。 ↩

  2. 2025-05-04時点。 ↩

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?