PG16:Tracking last scan time #PostgreSQL

はじめに

にゃーん。趣味でポスグレをやっている者だ。

この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 7日目の記事です。

今回はPostgreSQL 16で統計情報に追加された改造の内容を書いてみます。

概要

項目	内容
タイトル	Tracking last scan time
Topic	Monitoring & Control
ステータス	commited
Last modified	2022-10-14
概要	統計情報ビュー`pg_stat__tables`に`last_seq_scan`列と`last_idx_scan`列を追加。統計情報ビュー`pg_stat__indexes`に`last_idx_scan`列を追加

変更内容

pg_stat_*_tables

PostgreSQL 16では、統計情報ビューpg_stat_*_tablesに以下の2つの列が追加されます。

列名	型	内容
last_seq_scan	timestamp with time zone	該当のリレーションに対して最後にSeqScanによるアクセスが行われた時刻
last_idx_scan	timestamp with time zone	該当のリレーションに対して最後にIndexScanによるアクセスが行われた時刻

pg_stat_*_indexes

PostgreSQL 16では、統計情報ビューpg_stat_*_indexesに以下の1つの列が追加されます。

列名	型	内容
last_idx_scan	timestamp with time zone	該当のインデックスファイルに対して最後にアクセスが行われた時刻

どういうときに役に立つ？

この改善事項は、作成したインデックスがきちんと使われているかをチェックするのに役に立つと思われます。

例えばあるテーブルにインデックスを設定したけど、そのインデックスが実は使われないと、インデックス領域の無駄になるだけでなく、テーブルに更新があったときに同時に発生するインデックスの更新コストが無駄になります。

pg_stat_user_tablesのlast_seq_scanやlast_index_scanの時刻を確認することで、最近そのテーブルに対してSeqScanがされた時刻や、そのテーブルに対して何らかのインデックスによるアクセスがされた時刻を確認できます。
例えば、あるテーブルに対してインデックスは作成したけど、そのインデックスを使う検索が全く行われなければ、last_index_scanのタイムスタンプは更新されず、last_seq_scanのタイムスタンプだけ更新される、という結果になります。

テーブルに対しては複数のインデックスが定義できるので、イネックスのうちどれかが使用されれば、pg_stat_user_tablesのタイムスタンプは更新されます。複数のインデックスのうち、どれかが使われない、という場合には、pg_stat_*_indexesのlast_idx_scanを確認します。

実行例

ビルドバージョン

~~masterブランチのcommit 34fa0ddae5cf27dd93a5855b30083185d4bb5a5bを--enable-debug --enable-cassertオプションつきでconfigureしてビルドした版を使っています。~~

masterブランチのcommit 8018ffbf5895ee16a1fd7117c4526b47ac42332eを``ビルドした版を使っています。(2022-12-07修正)

pgbench_accountsの例

pgbenchの-i -s 10オプションでpgbench_accountsテーブルを作成します。

$ ~/pgsql/master/bin/pgbench bench -i -s 10 -q
（省略）
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 2.27 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 3.55 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 2.61 s, vacuum 0.15 s, primary keys 0.78 s).
$
$ psql bench -c "\d pgbench_accounts"
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null |
 bid      | integer       |           |          |
 abalance | integer       |           |          |
 filler   | character(84) |           |          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

わかりやすくするために、pg_stat_reset()を実行して、活動統計情報の内容をクリアし、その状態でpgbench_accountsテーブルのpg_stat_user_tablesの内容を確認します。

$ psql bench -c "SELECT pg_stat_reset()"
 pg_stat_reset
---------------

(1 row)

$ psql bench -P "null=(null)" -x -c "SELECT relname, seq_scan,last_seq_scan,idx_scan,last_idx_scan FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts'"
-[ RECORD 1 ]-+-----------------
relname       | pgbench_accounts
seq_scan      | 0
last_seq_scan | (null)
idx_scan      | 0
last_idx_scan | (null)

$

pgbench_accountsのaid列に対する=条件つきのクエリを実行し、実行後にpg_stat_user_tablesを再確認します。

$ psql bench -c "SELECT aid, bid, abalance FROM pgbench_accounts WHERE aid = 10000"
  aid  | bid | abalance
-------+-----+----------
 10000 |   1 |        0
(1 row)

$ psql bench -P "null=(null)" -x -c "SELECT relname, seq_scan,last_seq_scan,idx_scan,last_idx_scan FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts'"
-[ RECORD 1 ]-+------------------------------
relname       | pgbench_accounts
seq_scan      | 0
last_seq_scan | (null)
idx_scan      | 1
last_idx_scan | 2022-12-07 10:01:39.024468+09

$

idx_scanがカウントアップされ、last_idx_scanが更新されているのがわかります。

今度は、WHERE句条件なしでabalance列のAVG()をとってみます。この検索ではSeqScanが実行されます。

$ psql bench -c "SELECT AVG(abalance) FROM pgbench_accounts"
            avg
----------------------------
 0.000000000000000000000000
(1 row)

$ psql bench -P "null=(null)" -x -c "SELECT relname, seq_scan,last_seq_scan,idx_scan,last_idx_scan FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts'"
-[ RECORD 1 ]-+------------------------------
relname       | pgbench_accounts
seq_scan      | 3
last_seq_scan | 2022-12-07 10:02:50.276891+09
idx_scan      | 1
last_idx_scan | 2022-12-07 10:01:39.024468+09

$

seq_scanの数が0から3に増え、またlast_seq_scanのタイムスタンプが更新されているのがわかります。

なお、seq_scanが1ではなく3になっているのは、2つのWorkerを起動したパラレルクエリで実行されたからです。

$ psql bench -c "EXPLAIN SELECT AVG(abalance) FROM pgbench_accounts"
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=22602.55..22602.56 rows=1 width=32)
   ->  Gather  (cost=22602.33..22602.54 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=21602.33..21602.34 rows=1 width=32)
               ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..20560.67 rows=416667 width=4)
(5 rows)

$

パラレルクエリが実行された場合には、パラレルワーカーで実施されたスキャン方式の回数がカウントアップされるようですね。

おわりに

テーブルに対する最終シーケンシャルスキャンアクセス時刻・インデックスアクセス時刻や、インデックス毎の最終アクセス時刻をきちんと監視することで、より効率の良いインデックスのメンテナンスができるようになるかもしれません。
このあたりの監視・運用のノウハウも考えないといけませんね。