Re: Implementing Incremental View Maintenance

From: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, michael(at)paquier(dot)xyz, amitlangote09(at)gmail(dot)com, alvherre(at)2ndquadrant(dot)com, thomas(dot)munro(at)gmail(dot)com, kgrittn(at)gmail(dot)com
Subject: Re: Implementing Incremental View Maintenance
Date: 2020-04-10 14:26:58
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Attached is the latest patch (v15) to add support for Incremental Materialized
View Maintenance (IVM). It is possible to apply to current latest master branch.

Differences from the previous patch (v14) include:

* Fix to not use generate_series when views are queried

In the previous implementation, multiplicity of each tuple was stored
in ivm_count column in views. When SELECT was issued for views with
duplicate, the view was replaced with a subquery in which each tuple
was joined with generate_series function in order to output tuples
of the number of ivm_count.

This was problematic for following reasons:

- The overhead was huge. When almost of tuples in a view were selected,
it took much longer time than the original query. This lost the meaning
of materialized views.

- Optimizer could not estimate row numbers correctly because this had to
know ivm_count values stored in tuples.

- System columns of materialized views like cmin, xmin, xmax could not
be used because a view was replaced with a subquery.

To resolve this, the new implementation doen't store multiplicities
for views with tuple duplicates, and doesn't use generate_series
when SELECT query is issued for such views.

Note that we still have to use ivm_count for supporting DISTINCT and

* Add query checks for IVM restrictions

Query checks for following restrictions are added:

- TABLESAMPLE parameter
- inheritance parent table
- window function
- some aggregate options(such as FILTER, DISTINCT, ORDER and GROUPING SETS)
- targetlist containing IVM column
- simple subquery is only supported
- empty target list

* Improve error messages

Add error code ERRCODE_FEATURE_NOT_SUPPORTED to each IVM error message.
Also, the message format was unified.

* Support subqueries containig joins in FROM clause

Previously, when multi tables are updated simultaneously, incremental
view maintenance with subqueries including JOIN didn't work correctly
due to a bug.

Best Regards,
Takuma Hoshiai

Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>

Attachment Content-Type Size
IVM_patches_v15.tar.gz application/gzip 74.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2020-04-10 14:54:10 Re: where should I stick that backup?
Previous Message Neil 2020-04-10 14:24:11 Re: Support for DATETIMEOFFSET