概要
PostgreSQL の ANALYZE コマンドはトランザクション内でも実行できます。また、トランザクション内でのそれまでの更新結果が統計情報に反映されます。
ANALYZE コマンドをトランザクション内で実行した際の挙動について、検証と考察を行いました。
検証
PostgreSQL 12.1 で検証を行いました。
基本的にトランザクション TX1, TX2, TX3 の順で実行します。コメントに記載した実行タイミングを満たさない、もしくは応答待ちになったときは次のトランザクションに実行順序を移します。
TX1 はトランザクション内で更新処理の後に ANALYZE コマンドを実行することで、それまでの更新結果も統計情報に反映されることを確認します。
TX1 が先行した状態で TX2 を実行し、TX1 が進行中もしくは COMMIT 後に TX2 からは統計情報がどのように見えるかを確認します。
TX1, TX2 は PostgreSQL のデフォルト分離レベルである READ COMMITTED でしたが、TX3 は REPEATABLE READ とし、トランザクション分離レベルが統計情報の見え方にどのような影響を与えるかを確認します。
-- TX1
=# CREATE TABLE tab1(c1 INTEGER);
=# INSERT INTO tab1 SELECT generate_series(1, 10);
=# VACUUM ANALYZE tab1;
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------------
tab1 | c1 | -1 | | | {1,2,3,4,5,6,7,8,9,10}
(1 row)
=# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
=# UPDATE tab1 SET c1 = '1';
=# ANALYZE tab1;
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------
tab1 | c1 | 1 | {1} | {1} |
(1 row)
-- TX3 の ANALYZE コマンドを実行後に TX1 の COMMIT を実行した。
=# COMMIT;-- TX2
=# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------------
tab1 | c1 | -1 | | | {1,2,3,4,5,6,7,8,9,10}
(1 row)
=# ANALYZE tab1;
-- TX1 が COMMIT するまで待機状態となる。
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------
tab1 | c1 | 1 | {1} | {1} |
(1 row)
=# COMMIT;-- TX3
=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------------
tab1 | c1 | -1 | | | {1,2,3,4,5,6,7,8,9,10}
(1 row)
=# ANALYZE tab1;
-- TX1, TX2 が COMMIT するまで待機状態となる。
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------------
tab1 | c1 | -1 | | | {1,2,3,4,5,6,7,8,9,10}
(1 row)
=# COMMIT;
=# SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename = 'tab1';
tablename | attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
-----------+---------+------------+------------------+-------------------+------------------
tab1 | c1 | 1 | {1} | {1} |
(1 row)
考察
TX1 の実行結果より、トランザクション内で更新処理の後に ANALYZE コマンドを実行すると、それまでの更新結果も統計情報に反映されたことが分かります。トランザクション内で大量の更新を行った後、ANALYZE コマンドを実行することで、後続のクエリで効率の良い実行計画を得られることが期待できます。
TX2 の ANALYZE コマンドの実行が先行する TX1 の終了を待ちました。これは ANALYZE コマンドが SHARE UPDATE EXCLUSIVE ロックを取得するためです。
SHARE UPDATE EXCLUSIVE
SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、およびACCESS EXCLUSIVEロックモードと競合します。 このモードにより、同時実行されるスキーマの変更およびVACUUMコマンドの実行から、テーブルを保護します。
（FULLなしの）VACUUM、ANALYZE、CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、CREATE STATISTICS、ALTER TABLE VALIDATE、および、ALTER INDEXやALTER TABLEの特定の亜種(詳細はALTER INDEXやALTER TABLEを参照してください)によって獲得されます。
13.3. 明示的ロック
TX2, TX3 はトランザクション分離レベルが異なります。TX1 を COMMIT した後、TX2 は TX1 を反映した統計情報が見えていますが (READ COMMITTED)、TX3 はトランザクションを終了するまで以前の統計情報が見えています (REPEATABLE READ)。TX2, TX3 の ANALYZE コマンド実行が結果に影響を与えた可能性を考慮し、ANALYZE コマンドを実行せずに単に待機した場合でも同様の結果が得られました。このことから、統計情報の可視性もトランザクション分離レベルに従うと推測されます。スナップショットと統計情報の状態に整合性があることは正しい実行計画を得るためにも重要といえます。
