Re: Implementing Incremental View Maintenance

> Could you give some concrete use cases, so that I can have a clearer image of the target data? In the discussion, someone referred to master data with low update frequency, because the proposed IVM implementation adds triggers on source tables, which limits the applicability to update-heavy tables.

But if you want to get always up-to-data you need to pay the cost for
REFRESH MATERIALIZED VIEW. IVM gives a choice here.

pgbench -s 100
create materialized view mv1 as select count(*) from pgbench_accounts;
create incremental materialized view mv2 as select count(*) from pgbench_accounts;

Now I delete one row from pgbench_accounts.

test=# delete from pgbench_accounts where aid = 10000000;
DELETE 1
Time: 12.387 ms

Of course this makes mv1's data obsolete:
test=# select * from mv1;
count
----------
10000000
(1 row)

To reflect the fact on mv1 that a row was deleted from
pgbench_accounts, you need to refresh mv1:

test=# refresh materialized view mv1;
REFRESH MATERIALIZED VIEW
Time: 788.757 ms

which takes 788ms. With mv2 you don't need to pay this cost to get the
latest data.

This is kind of ideal use case for IVM and I do not claim that IVM
always wins over ordinary materialized view (or non materialized
view). IVM will give benefit in that a materialized view instantly
updated whenever base tables get updated with a cost of longer update
time on base tables.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp