はじめに
にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の22日目のエントリです。
今日は短め。
今日のお題
今日のお題は、REFRESH MATERIALIZED VIEWのちょっとした改善の話。
PostgreSQL 10での改善点
PostgreSQLの稼働統計情報ビューのpg_stat_user_tablesは、テーブルだけではなく、MATERIALIZED VIEWも対象になっている。
しかし、PostgreSQL 9.6までは、REFRESH MATERIALIZED VIEWが実施されたときに、pg_stas_user_tablesの内容が更新されないままになっていた。
たとえば、以下のような簡単なMATERIALIZED VIEWを定義しておく。
matview=# CREATE TABLE test_t (id int primary key, data int);
CREATE TABLE
matview=# CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM test_t;
SELECT 0
matview=#
matview=# \d+ test_t
Table "public.test_t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
data | integer | | plain | |
Indexes:
"test_t_pkey" PRIMARY KEY, btree (id)
matview=# \d+ test_mv
Materialized view "public.test_mv"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
data | integer | | plain | |
View definition:
SELECT test_t.id,
test_t.data
FROM test_t;
この状態で、pg_stat_user_tablesを確認する。
matview=# SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables ;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
---------+-----------+-----------+-----------+------------+------------
test_t | 0 | 0 | 0 | 0 | 0
test_mv | 0 | 0 | 0 | 0 | 0
(2 rows)
この時点では、何も更新していないのでこれらの値は全て0だ。
さて、test_mv の元になった test_t テーブルに対して、以下のようにINSERT/UPDATE/DELETE文を実行する。
matview=# INSERT INTO test_t VALUES (generate_series(1, 100000), (random() * 100000)::int);
INSERT 0 100000
matview=# UPDATE test_t SET data = -1 WHERE id <= 10;
UPDATE 10
matview=# DELETE FROM test_t WHERE id >= 99990;
DELETE 11
matview=#
この状態で、pg_stat_user_tablesを参照する。
matview=# SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables ;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
---------+-----------+-----------+-----------+------------+------------
test_t | 100000 | 10 | 11 | 99989 | 21
test_mv | 0 | 0 | 0 | 0 | 0
(2 rows)
test_tテーブルの値は更新されている。
ここまでは、PostgreSQL 9.6までも、PostgreSQL 10でも変わりない。
PostgreSQL 9.6まで
ここでREFRESH MATERIALIZED VIEWコマンドを実行して、pg_stat_user_tables を参照する。
matview=# REFRESH MATERIALIZED VIEW test_mv ;
REFRESH MATERIALIZED VIEW
matview=# SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables ;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
---------+-----------+-----------+-----------+------------+------------
test_t | 100000 | 10 | 11 | 99989 | 21
test_mv | 0 | 0 | 0 | 0 | 0
(2 rows)
PostgreSQL 9.6までは、REFRESH MATERIALIZED VIEWを実行しても対象のMATERIALIZED VIEWの稼働統計情報ビューの内容は更新されなかった。
PostgreSQL 10から
PostgreSQL 10で同じ操作を実施してみる。
matview=# REFRESH MATERIALIZED VIEW test_mv ;
REFRESH MATERIALIZED VIEW
matview=# SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables ;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
---------+-----------+-----------+-----------+------------+------------
test_t | 100000 | 10 | 11 | 99989 | 21
test_mv | 99989 | 0 | 0 | 99989 | 0
(2 rows)
REFRESH MATERIALIZED VIEW後に、MATERIALIZED VIEWのn_tup_insの内容、n_live_tupの値が更新されているのがわかる。
おわりに
実は盲点というか、こんな問題がPostgreSQL 9.6まではあったんだなー、という印象だ。
でも、こういう細かい改善も大事だよなぁ。
参考：該当するリリースノート
本エントリに関連するPostgreSQL 10リリースノートの記載です。
E.2.3.1.6. Monitoring
- Properly update the statistics collector during REFRESH MATERIALIZED VIEW (Jim Mlodgenski)