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 (nag...@sraoss.co.jp), Takuma Hoshiai (hosh...@sraoss.co.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
IVM_v7.patch.gz
Description: Binary data
