Re: Implementing Incremental View Maintenance

Attached is the latest patch to add support for Incremental
Materialized View Maintenance (IVM). IVM allows to reflect
modifications made on base tables immediately to the target
materialized views.

Up to now, IVM supports materialized views using:

- Inner joins
- Some aggregate functions (count, sum, min, max, avg)
- GROUP BY
- Self joins

With the latest patch now IVM supports subqueries in addition to
above.

Known limitations are listed here:

https://github.com/sraoss/pgsql-ivm/issues

See more details at:
https://wiki.postgresql.org/wiki/Incremental_View_Maintenance

About subquery support:

The patch supports simple subqueries using EXISTS:

CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_exists_subquery AS SELECT
a.i, a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE
a.i = b.i);

and subqueries in the FROM clause:

CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_subquery AS SELECT a.i,a.j
FROM mv_base_a a,( SELECT * FROM mv_base_b) b WHERE a.i = b.i;

Other form of subqueries such as below are not supported:

-- WHERE IN .. (subquery) is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM
mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );

-- subqueries in target list is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k
FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;

-- nested EXISTS subqueries is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm11 AS SELECT a.i,a.j FROM
mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE EXISTS(SELECT
1 FROM mv_base_b c WHERE b.i = c.i));

-- EXISTS subquery with aggragate function is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_exists AS SELECT COUNT(*)
FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i =
b.i) OR a.i > 5;

-- EXISTS subquery with condition except AND is not supported.
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM
mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR
a.i > 5;

This work has been done by Yugo Nagata (nagata(at)sraoss(dot)co(dot)jp), Takuma
Hoshiai (hoshiai(at)sraoss(dot)co(dot)jp). Adding support for EXISTS clause has
been done by Takuma.

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