Re: Implementing Incremental View Maintenance

On Sun, Aug 1, 2021 at 11:30 PM Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hi hackers,
>
> On Mon, 19 Jul 2021 09:24:30 +0900
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> > On Wed, 14 Jul 2021 21:22:37 +0530
> > vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> > > The patch does not apply on Head anymore, could you rebase and post a
> > > patch. I'm changing the status to "Waiting for Author".
> >
> > Ok. I'll update the patch in a few days.
>
> Attached is the latest patch set to add support for Incremental
> Materialized View Maintenance (IVM)
>
> The patches are rebased to the master and also revised with some
> code cleaning.
>
> IVM is a way to make materialized views up-to-date in which only
> incremental changes are computed and applied on views rather than
> recomputing the contents from scratch as REFRESH MATERIALIZED VIEW
> does. IVM can update materialized views more efficiently
> than recomputation when only small part of the view need updates.
>
> The patch set implements a feature so that materialized views could be
> updated automatically and immediately when a base table is modified.
>
> Currently, our IVM implementation supports views which could contain
> tuple duplicates whose definition includes:
>
> - inner and outer joins including self-join
> - DISTINCT
> - some built-in aggregate functions (count, sum, agv, min, and max)
> - a part of subqueries
> -- simple subqueries in FROM clause
> -- EXISTS subqueries in WHERE clause
> - CTEs
>
> We hope the IVM feature would be adopted into pg15. However, the size of
> patch set has grown too large through supporting above features.
> Therefore,
> I think it is better to consider only a part of these features for the
> first
> release. Especially, I would like propose the following features for pg15.
>
> - inner joins including self-join
> - DISTINCT and views with tuple duplicates
> - some built-in aggregate functions (count, sum, agv, min, and max)
>
> By omitting outer-join, sub-queries, and CTE features, the patch size
> becomes
> less than half. I hope this will make a bit easer to review the IVM patch
> set.
>
> Here is a list of separated patches.
>
> - 0001: Add a new syntax:
> CREATE INCREMENTAL MATERIALIZED VIEW
> - 0002: Add a new column relisivm to pg_class
> - 0003: Add new deptype option 'm' in pg_depend
> - 0004: Change trigger.c to allow to prolong life span of tupestores
> containing Transition Tables generated via AFTER trigger
> - 0005: Add IVM supprot for pg_dump
> - 0006: Add IVM support for psql
> - 0007: Add the basic IVM future:
> This supports inner joins, DISTINCT, and tuple duplicates.
> - 0008: Add aggregates (count, sum, avg, min, max) support for IVM
> - 0009: Add regression tests for IVM
> - 0010: Add documentation for IVM
>
> We could split the patch furthermore if this would make reviews much
> easer.
> For example, I think 0007 could be split into the more basic part and the
> part
> for handling tuple duplicates. Moreover, 0008 could be split into "min/max"
> and other aggregates because handling min/max is a bit more complicated
> than
> others.
>
> I also attached IVM_extra.tar.gz that contains patches for sub-quereis,
> outer-join, CTE support, just for your information.
>
> Regards,
> Yugo Nagata
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>

Hi,
For v23-0007-Add-Incremental-View-Maintenance-support.patch :

bq. In this implementation, AFTER triggers are used to collecting
tuplestores

'to collecting' -> to collect

bq. are contained in a old transition table.

'a old' -> an old

bq. updates of more than one base tables

one base tables -> one base table

bq. DISTINCT and tuple duplicates in views are supported

Since distinct and duplicate have opposite meanings, it would be better to
rephrase the above sentence.

bq. The value in__ivm_count__ is updated

I searched the patch for in__ivm_count__ - there was no (second) match. I
think there should be a space between in and underscore.

+static void CreateIvmTriggersOnBaseTables_recurse(Query *qry, Node *node,
Oid matviewOid, Relids *relids, bool ex_lock);

nit: long line. please wrap.

+ if (rewritten->distinctClause)
+ rewritten->groupClause = transformDistinctClause(NULL,
&rewritten->targetList, rewritten->sortClause, false);
+
+ /* Add count(*) for counting distinct tuples in views */
+ if (rewritten->distinctClause)

It seems the body of the two if statements can be combined into one.

More to follow for this patch.

Cheers