Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@nuko_yokohama(nuko yokohama)

pg_ivm(IMMV)を使ってみた(2) 1.1での改善点など

Posted at

1. はじめに

にゃーん。
今日は、先週、バージョン1.1がリリース（リリースニュース）されたpg_ivmについて、実際に動作させて改善点を検証しました。

2. What's pg_ivm?

pg_ivmはPostgreSQLでIncremental View Maintenanceを実現する拡張機能です。
Incremental View Maintenanceとは、再計算を行わず、増分変更のみを計算してビューに適用することで、実体化したビューを最新の状態にする方法です。

この増分変更の計算と適用の方式に関する考え方は、論文などでも紹介されています（PostgreSQL Wiki - Incremental View Maintenance等参照）が、自分は猫なのでそういう難しいことはわかりません。本記事ではあくまでも使い方について書いてみます。

詳細は前回書いた記事（pg_ivm(IMMV)を使ってみた(1)）を参照していただければと。

3. pg_ivm 1.1の改善点

pg_ivm 1.1のリリースノートに記載されている改善点は以下のとおり。

  • PostgreSQL 13のサポート
  • 集約関数のサポート
  • ベーステーブルに対するTRUNCATEのサポート
  • refresh_immv()関数の追加
  • create_immv()への入力が不正な場合のエラーコードを修正

以下、それぞれの改善点について見ていきます。

3.1 PostgreSQL 13のサポート

前回調査時は、PostgreSQL 14上での動作を確認しましたが、pg_ivm 1.1ではPostgreSQL 13にも対応したとのこと。

手元の環境（PostgreSQL 12, 13, 14, 15 beta1）でpg_ivmがビルドできるかを一応確認してみました。

バージョン ビルド可否 備考
PostgreSQL 12 ✗ QueryCompletion型がない
INT4OIDがない
IndexStmtのメンバoldCreateSubid,
opclassoptsが存在しない
PostgreSQL 13 ○
PostgreSQL 14 ○
PostgreSQL 15 beta1 ✗ make_new_heap()への引数が少ない

今のところ、PostgreSQL 13, 14のみ利用可能なようです。
pg_ivm 1.0のときには、PostgreSQL 15beta1でもビルドはできていたような気がしたけど・・・。もうすぐPostgreSQL 15 beta2 がリリースされるらしいので、beta2がリリースされたら、beta2でビルド可能かも確認しておかねば。

なお、以降の検証は全て、PostgreSQL 14上で実施しています。

3.2 集約関数のサポート

pg_ivm 1.1での改善項目の目玉その1。

pg_ivm 1.1では、組み込み集約関数のうち、count, sum, avgの3関数に対応しているとのこと。
ということで、以下の検証をしてみました。

検証概要

  • 各ビューの集約演算元のテーブル(base)を定義して、10万件のランダムなデータを挿入する。
  • baseに対してCOUNT(*), SUM(), AVG()の集約を行うクエリをもとに3種類のビューを作成する。
    • VIEW(agg_v)
    • MATERIALIZED VIEW(agg_mv)
    • INCREMENTAL MAINTENANCE MATERIALIZED VIEW(agg_immv)
  • 作成直後に3種類のビューを検索する。
  • その状態で、更に10万件のデータを挿入する。
  • agg_mvをリフレッシュする。
  • この状態で3種類のビューを検索する。

検証用のテーブル

以下のベーステーブルを定義する。
このベーステーブルはPKとint型、double precision型、numeric型を持つ。

CREATE TABLE ivm.base
  (id int primary key, i_data int, d_data double precision, n_data numeric);

データ挿入(1)

baseテーブルに10万件のデータを挿入する。i_data, d_data, n_dateにはrandom() * 100000 の数値を格納する。

INSERT INTO ivm.base VALUES
  (generate_series(1, 100000), (random() * 10000)::int, random(), random());

各ビューの作成

3種類のビューを作成する。それぞれのビューの生成元となるSELECT文は以下とする。

  SELECT
    COUNT(*),
    SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
    AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
  FROM ivm.base

実際のビュー定義は以下のようになる。

CREATE VIEW ivm.agg_v AS
  SELECT
    COUNT(*),
    SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
    AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
  FROM ivm.base
;
CREATE MATERIALIZED VIEW ivm.agg_mv AS
  SELECT
    COUNT(*),
    SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
    AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
  FROM ivm.base
;
SELECT create_immv('ivm.agg_immv', $$
  SELECT
    COUNT(*),
    SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
    AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
  FROM ivm.base
$$
);

検索(1)

各ビューに対して検索を行う。（1行が長いので拡張表示形式で出力する）1

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_v;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834

Time: 22.003 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_mv;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834

Time: 0.310 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_immv;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834

Time: 0.212 ms

int型やnumeric型に対するSUM, AVGはどのビューでも同じ値になる。
double precision型に対するSUM, AVGは各ビューでビミョーに違う値になってしまうが、これは浮動小数点型だから仕方ないのかも。

データ挿入(2)

この状態で、さらに10万件のデータをbaseテーブルに挿入する。
また、REFRESH MATERIALIZED VIEWも実施する。

INSERT INTO ivm.base VALUES
  (generate_series(100001, 200000), (random() * 10000)::int, random(), random());
INSERT 0 100000
Time: 354.707 ms
REFRESH MATERIALIZED VIEW ivm.agg_mv;
REFRESH MATERIALIZED VIEW
Time: 32.198 ms

検索(2)

再度データ挿入した後で再検索する。

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_v;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.67078115631
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.5003783539057816
n_avg | 0.50058409007212873207

Time: 25.712 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_mv;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.6707811564
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.500378353905782
n_avg | 0.50058409007212873207

Time: 0.374 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_immv;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.6707811559
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.5003783539057796
n_avg | 0.50058409007212873207

Time: 0.137 ms

1回目の検索と同様、int型やnumeric型に対するSUM, AVGはどのビューでも同じ値になり、double precision型に対するSUM, AVGは各ビューでビミョーに違う値になってしまう。これは想定どおり。

ということで、Incremental View Maintenanceによる、集約関数(COUNT, SUM, AVG)が対応していることを確認できました。
あとは、MAX, MIN関数がサポートされればなあ・・・。これがサポートされると、IMMVを使うことで、PostgreSQLでも巨大なテーブルのMAX, MINが素早く求められるようになるのだけど。

3.3 ベーステーブルに対するTRUNCATEのサポート

pg_ivm 1.1の改善項目の目玉その2。
pg_ivm 1.0ではベーステーブルに対してTRUNCATEしても、IMMVは追随してくれなかったのですが、pg_ivm 1.1ではベーステーブルに対するTRUNCATEの結果にIMMVが追随するようになりました。

検証

ベーステーブル(base)に200万件入った状態で、各ビューの結果を表示します。

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_v;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.49547540041
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770021
n_avg | 0.49930606097435924830

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_mv;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.4954754003
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770015
n_avg | 0.49930606097435924830

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_immv;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.49547540018
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770009
n_avg | 0.49930606097435924830

この状態で、ベーステーブル(base)をTRUNCATEします。
またマテリアライズド・ビューはリフレッシュしておきます。

TRUNCATE ivm.base;
TRUNCATE TABLE
REFRESH MATERIALIZED VIEW ivm.agg_mv;
REFRESH MATERIALIZED VIEW

TRUNCATEした後は、ビュー、マテリアライズド・ビュー、IMMVともにCOUNTが0件、SUM, AVGの結果はNULLになりました。めでたしめでたし。

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_v;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_mv;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |

SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg  FROM ivm.agg_immv;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |

3.4 refresh_immv()関数の追加

pg_ivm拡張を登録後に、\dfコマンドでimmvがつく関数を探してみます。

postgres@ivm# \df pg_catalog.*immv;
                             List of functions
   Schema   |     Name     | Result data type | Argument data types | Type
------------+--------------+------------------+---------------------+------
 pg_catalog | create_immv  | bigint           | text, text          | func
 pg_catalog | refresh_immv | bigint           | text, boolean       | func
(2 rows)

たしかにrefresh_immvという関数が追加されているようです。
refresh_immv関数は2つの引数を持っています。
最初の引数はリフレッシュしたいIMMV名（create_immv実行時に指定したもの）を指定します。
2つ目の引数は

WITH [NO] DATA オプションに対応する boolean パラメータ

というもの。true, falseを指定すると以下のような挙動になります。

引数の値 挙動
true create_immv()で指定したクエリを再実行して、クエリの実行結果をIMMVに設定する。
同時にベーステーブルにトリガを再設定する。
後述のfalseを指定してrefresh_immv()を実行したあとで、再びIMMVを有効にするときに使う。
false 指定したIMMVの結果は空になる。
またベーステーブルに設定されたトリガは削除される。
IMMV自体は削除されていないので、このパラメータをtrueにすることで、再びクエリの結果を反映することができる。
一時的にIMMVを無効化する、ということかな。

検証概要

  • ベーステーブルを定義して10万件データを挿入する。
  • IMMVを作成する。
  • 作成直後にトリガ定義一覧を確認する。
  • IMMVの内容を検索する。
  • refresh_immv()をfalseモードで実行する。
  • 作成直後にトリガ定義一覧を確認する。
  • IMMVの内容を検索する。
  • refresh_immv()をtrueモードで実行する。
  • 作成直後にトリガ定義一覧を確認する。
  • IMMVの内容を検索する。

ベーステーブルの作成とデータ挿入

以下のようなベーステーブルを定義する。

CREATE TABLE ivm.base (id int, i_data int, n_data numeric);

ベーステーブルにテキトーに10万件を挿入する。

INSERT INTO ivm.base VALUES
  (generate_series(1, 100000), random() * 10000, random());

IMMVの作成と確認

IMMVを作成する。

SELECT create_immv('ivm.agg_immv', $$
  SELECT
    id, i_data, n_data
  FROM ivm.base
  WHERE i_data <= 1
$$
);

作成直後にトリガの状態を確認する。

SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
 tgrelid | relname  |              tgname
---------+----------+-----------------------------------
   18616 | agg_immv | IVM_prevent_immv_change_18629
   18616 | agg_immv | IVM_prevent_immv_change_18630
   18616 | agg_immv | IVM_prevent_immv_change_18631
   18616 | agg_immv | IVM_prevent_immv_change_18632
   18611 | base     | IVM_trigger_del_after_18626
   18611 | base     | IVM_trigger_del_before_18622
   18611 | base     | IVM_trigger_ins_after_18625
   18611 | base     | IVM_trigger_ins_before_18621
   18611 | base     | IVM_trigger_truncate_after_18628
   18611 | base     | IVM_trigger_truncate_before_18624
   18611 | base     | IVM_trigger_upd_after_18627
   18611 | base     | IVM_trigger_upd_before_18623
(12 rows)

ベーステーブルと作成されたIMMVに対するトリガの一覧が表示される。
この状態でIMMVを検索する。

SELECT * FROM ivm.agg_immv;
  id   | i_data |      n_data
-------+--------+-------------------
   333 |      1 |  0.46216434577229
   770 |      1 | 0.643584418527755
  1380 |      1 | 0.160069154000713
（略）
 93344 |      0 | 0.890123925243319
 99346 |      1 | 0.211752012419627
(15 rows)

refresh_immv()をfalseで実行

作成したIMMVに対して、refresh_immvを第2引数falseで実行する。

SELECT refresh_immv('ivm.agg_immv', false);
 refresh_immv
--------------
            0
(1 row)

この状態で、トリガの状態を確認する。

SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
 tgrelid | relname  |            tgname
---------+----------+-------------------------------
   18616 | agg_immv | IVM_prevent_immv_change_18629
   18616 | agg_immv | IVM_prevent_immv_change_18630
   18616 | agg_immv | IVM_prevent_immv_change_18631
   18616 | agg_immv | IVM_prevent_immv_change_18632
(4 rows)

ベーステーブルに対するトリガ定義が削除されていることがわかる。
この状態でもIMMVへの検索自体はできる。

SELECT * FROM ivm.agg_immv;
 id | i_data | n_data
----+--------+--------
(0 rows)

検索はできるが、空の結果が返却されるだけになる。

refresh_immv()をtrueで実行

この状態でrefresh_immvを第2引数trueで実行する。

SELECT refresh_immv('ivm.agg_immv', true);
 refresh_immv
--------------
           15
(1 row)

refresh_immvの実行後に、再びトリガ状態を確認する。

SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
 tgrelid | relname  |              tgname
---------+----------+-----------------------------------
   18616 | agg_immv | IVM_prevent_immv_change_18629
   18616 | agg_immv | IVM_prevent_immv_change_18630
   18616 | agg_immv | IVM_prevent_immv_change_18631
   18616 | agg_immv | IVM_prevent_immv_change_18632
   18611 | base     | IVM_trigger_del_after_18648
   18611 | base     | IVM_trigger_del_before_18644
   18611 | base     | IVM_trigger_ins_after_18647
   18611 | base     | IVM_trigger_ins_before_18643
   18611 | base     | IVM_trigger_truncate_after_18650
   18611 | base     | IVM_trigger_truncate_before_18646
   18611 | base     | IVM_trigger_upd_after_18649
   18611 | base     | IVM_trigger_upd_before_18645
(12 rows)

ベーステーブルに対するトリガ設定が復活している。
この状態でIMMVを検索すると

SELECT * FROM ivm.agg_immv;
  id   | i_data |      n_data
-------+--------+-------------------
   333 |      1 |  0.46216434577229
   770 |      1 | 0.643584418527755
  1380 |      1 | 0.160069154000713
（略）
 93344 |      0 | 0.890123925243319
 99346 |      1 | 0.211752012419627
(15 rows)

元の結果に戻る。めでたしめでたし。

3.5 create_immv()への入力が不正な場合のエラーコードを修正

（この調査は省略）

5. おわりに

pg_ivm 1.1は前回調査したときの不満点の多くが改善されたバージョンになっていることが分かりました。MAX/MINや、現状未サポートのSELECT機能にも早く対応してもらえると嬉しいですね。

あとは、AWS RDS Aurora PostgreSQL互換、Google AlloyDB、Azure PostgreSQL Flexible Server等のパブリッククラウドでこの拡張がサポートされたり、contribモジュールとしてPostgreSQL本体のコードにマージされたりすると、より気軽に使えるようになるかなあ。

  1. SELECT * FROM ...で検索すると、IMMVの場合、管理用のカラムも表示されてしまうので、明示的にカラム名をSELECT句に指定した。 ↩

5
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?