PostgreSQL にデータをリストアした後に行う ANALYZE について

2021-09-13 PostgreSQL

PostgreSQL にデータをリストアした後に行う ANALYZE について

概要

pg_dump で取得したダンプファイルを PostgreSQL へリストアした後には ANALYZE したほうがよい、と公式ドキュメント（下記 URL ）にも記載があります。
https://www.postgresql.jp/document/12/html/backup-dump.html

ここで「ANALYE」だけでよいのか、それとも「VACUUM ANALYZE」をしたほうがよいのか、という疑問が湧きましたので、PostgreSQL 12.7 を利用して調べてみました。

結論

結論としては、データをリストアした後には「VACUUM ANALYZE」を実行するほうがお勧め、ということになりました。

理由

手動で行う VACUUM では以下のことが行われます。

(1) 不要タプルが使用する領域を回収
(2) 可視性マップの更新
(3) トランザクションID（xid）の周回エラーの防止

各事項の解説については、公式ドキュメントに詳しく記載されていますので、そちら（下記 URL ）をご参照ください。
https://www.postgresql.jp/document/12/html/maintenance.html

PostgreSQL へデータをリストアした直後では不要タプルは発生していないため、ANALYZE だけでも十分に思えます。
しかし、上記のとおり VACUUM の役割としては、不要タプルが使用する領域の回収以外にもありますので、ANALYZE だけでは不十分な場合があります。
特に「(2) 可視性マップの更新」が行われることで、（効率的な）Index Only Scan が可能となったり、次回の VACUUM 処理が効率的になったりします。
そのため、「PostgreSQL へデータをリストアした後に VACUUM ANALYZE を実施した方がよい」と考えられます。

可視性マップの詳細については、例によって公式ドキュメントに詳しく記載されていますので、そちら（下記 URL ）をご参照ください。
https://www.postgresql.jp/document/12/html/storage-vm.html

実際に確認してみます

以下のコード例では

$ で始まるものは OS コマンド
=# で始まるものは psql コマンド

としています。

1.データベースの構築

PostgreSQL Tutorial に用意されているサンプルデータを利用して、検証環境を構築します。

サンプルデータの取得

$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
$ unzip ./dvdrental.zip

dvdrental.zip を解凍すると、dvdrental.tar が作成されます。

検証用データベースの作成

=# CREATE DATABASE test_db;
CREATE DATABASE

検証用データベースにサンプルデータをリストア

$ pg_restore -U postgres -d test_db ./dvdrental.tar

リストアされたか確認

$ psql -U postgres -d test_db

=# \dt+
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description 
--------+---------------+-------+----------+------------+-------------
 public | actor         | table | postgres | 40 kB      | 
 public | address       | table | postgres | 88 kB      | 
 public | category      | table | postgres | 8192 bytes | 
 public | city          | table | postgres | 64 kB      | 
 public | country       | table | postgres | 8192 bytes | 
 public | customer      | table | postgres | 96 kB      | 
 public | film          | table | postgres | 464 kB     | 
 public | film_actor    | table | postgres | 264 kB     | 
 public | film_category | table | postgres | 72 kB      | 
 public | inventory     | table | postgres | 224 kB     | 
 public | language      | table | postgres | 8192 bytes | 
 public | payment       | table | postgres | 888 kB     | 
 public | rental        | table | postgres | 1224 kB    | 
 public | staff         | table | postgres | 16 kB      | 
 public | store         | table | postgres | 8192 bytes | 
(15 rows)

無事にデータがリストアされたことを確認できました。

2.インデックスオンリースキャンが実行されるかを確認

city テーブルに対して SELECT 実行

=# EXPLAIN (ANALYZE, BUFFERS) SELECT city_id FROM city WHERE city_id < 10;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using city_pkey on city  (cost=0.28..8.43 rows=9 width=4) (actual time=0.046..0.048 rows=9 loops=1)
   Index Cond: (city_id < 10)
   Heap Fetches: 9
   Buffers: shared read=3
 Planning Time: 0.772 ms
 Execution Time: 0.102 ms
(6 rows)

実行計画上はIndex Only Scan using city_pkey on city ...`となっていますが、`Heap Fetches: 9となっていますので、テーブルへのアクセスが発生しています。
Heap Fetches: 0となっていなければ、Index Only Scan が実行されていてもテーブルへのアクセスが発生しているため、高速でない可能性があります。

3.pg_visibility モジュールを利用して、可視性マップを確認

可視性マップは pg_visibility モジュールを利用することで確認できます。
pg_visibility モジュールは PostgreSQL 標準モジュールです。
詳細は例によって下記の公式ドキュメントをご参照ください。
https://www.postgresql.jp/document/12/html/pgvisibility.html

pg_visibility モジュールの導入

=# CREATE EXTENSION pg_visibility;
CREATE EXTENSION

可視性マップの確認

pg_visibility_map 関数の引数にテーブル名を指定して実行することで、指定したテーブルの可視性マップ（と凍結状態）を確認できます。

=# SELECT * FROM pg_visibility_map('city');
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
(5 rows)

上記 SQL 結果より city テーブルには 5 ブロックあり、そのすべての all_visible が f （全可視状態ではない）となっていることが分かりました。

4.ANALYZE を実行して確認

この状態で ANALYZE を実行して、どのように変化されるかを確認します。

city テーブルに ANALYZE を実行

=# ANALYZE city;
ANALYZE

可視性マップを確認

=# SELECT * FROM pg_visibility_map('city');
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
(5 rows)

当然ですが、ANALYZE だけでは可視性マップに変動ありませんでした。
- そのため、Index Only Scan の Heap Fetches も 0 にはなりません。

5.VACUUM ANALYZE を実行して確認

今度は VACUUM ANALYZE してみます。

=# VACUUM ANALYZE city;
VACUUM

可視性マップを確認

=# SELECT * FROM pg_visibility_map('city');
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
     1 | t           | f
     2 | t           | f
     3 | t           | f
     4 | t           | f
(5 rows)

all_visible がすべて t となりました。

city テーブルに対して SELECT 実行

VACUUM ANALYZE 後の Index Only Scan での Heap Fetches を確認します。

=# EXPLAIN (ANALYZE, BUFFERS) SELECT city_id FROM city WHERE city_id < 10;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using city_pkey on city  (cost=0.28..4.43 rows=9 width=4) (actual time=0.008..0.009 rows=9 loops=1)
   Index Cond: (city_id < 10)
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning Time: 0.103 ms
 Execution Time: 0.024 ms
(6 rows)

Heap Fetches: 0ということで、無事にテーブルアクセスがない高速な Index Only Scan が実行されました。

まとめ

繰り返しとなりますが、PostgreSQL へデータをリストアした後には「VACUUM ANALYZE」を実行するのがお勧めとなります。
もちろん「Index Only Scan なんて使わない」とか「VACUUM ANALYZE では時間がかかりすぎる」という場合には、ANALYZE のみでもよいと思います。
柔軟に対応できるのが最善だと思いますので、環境や運用方針、制約に合わせて対応方法を検討していただけますと幸いです。

本記事は以上となります。

TECH BLOG
技術ブログ