Re: Implementing Incremental View Maintenance

Hello Zhihong Yu,

On Mon, 2 Aug 2021 14:33:46 -0700
Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:

> 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-0008-Add-aggregates-support-in-IVM.patch :

Thank you for looking into this!

> As a restriction, expressions specified in GROUP BY must appear in
> the target list because tuples to be updated in IMMV are identified
> by using this group keys.
>
> IMMV -> IMVM (Incremental Materialized View Maintenance, as said above)
> Or maybe it means 'incrementally maintainable materialized view'. It would
> be better to use the same abbreviation.

IMMV is correct in the commit message of this patch. Rather, IMVM used
in v23-0003-Add-new-deptype-option-m-in-pg_depend-system-cat.patch
should be corrected to IMMV.

> this group keys -> this group key
>
> + errmsg("GROUP BY expression not appeared in select
> list is not supported on incrementally maintainable materialized view")));
>
> expression not appeared in select list -> expression not appearing in
> select list
>
> + * For aggregate functions except to count
>
> except to count -> except count

Thank you for pointing out them. I'll fix.

Regards,
Yugo Nagata

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