PG16:Tracking last scan time

## はじめに

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

この記事は[PostgreSQL 16 全部ぬこ Advent Calendar 2022](https://qiita.com/advent-calendar/2022/pg16-all-nuko) 7日目の記事です。

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

## 概要

|項目|内容|
|:--|:--|
|タイトル|[Tracking last scan time](https://commitfest.postgresql.org/40/3836/)|
|Topic|Monitoring & Control|
|ステータス|commited|
|Last modified|2022-10-14|
|概要|統計情報ビュー``pg_stat_*_tables``に``last_seq_scan``列と``last_idx_scan``列を追加。<br>統計情報ビュー``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](https://github.com/postgres/postgres/commit/34fa0ddae5cf27dd93a5855b30083185d4bb5a5b)を``--enable-debug --enable-cassert``オプションつきでconfigureしてビルドした版を使っています。~~

masterブランチの[commit 8018ffbf5895ee16a1fd7117c4526b47ac42332e](https://github.com/postgres/postgres/commit/8018ffbf5895ee16a1fd7117c4526b47ac42332e)を``ビルドした版を使っています。(2022-12-07修正)

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

```shell
$ ~/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``の内容を確認します。

```shell
$ 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``を再確認します。

```shell
$ 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が実行されます。

```shell
$ 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を起動したパラレルクエリで実行されたからです。

```shell
$ 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)

$
```

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

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