TECH BLOG
技術ブログ

ARTICLE

  • 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 が可能となりますので、「PostgreSQL へデータをリストアした後に VACUUM ANALYZE を実施した方がよい」と考えられます。

実際に確認してみます

以下のコード例では

  • $ で始まるものは 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 のみでもよいと思います。
柔軟に対応できるのが最善だと思いますので、環境や運用方針、制約に合わせて対応方法を検討していただけますと幸いです。

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

CATEGORY

ARCHIVE

データベースの
パフォーマンス・チューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT