Re: Implementing Incremental View Maintenance - Mailing list pgsql-hackers

Hi,

Attached is the revised patch (v21) to add support for Incremental
Materialized View Maintenance (IVM).

In addition to some typos in the previous enhancement, I fixed a check to
prevent a view from containing an expression including aggregates like
sum(x)/sum(y) in this revision.

Regards,
Yugo Nagata

On Tue, 22 Dec 2020 22:24:22 +0900
Yugo NAGATA <nagata@sraoss.co.jp> wrote:

> Hi hackers,
> 
> I heard the opinion that this patch is too big and hard to review.
> So, I wander that we should downsize the patch  by eliminating some
> features and leaving other basic features.
> 
> If there are more opinions this makes it easer for reviewers to look
> at this patch, I would like do it. If so, we plan to support only
> selection, projection, inner-join, and some aggregates in the first
> release and leave sub-queries, outer-join, and CTE supports to the
> next release.
> 
> Regards,
> Yugo Nagata
> 
> On Tue, 22 Dec 2020 21:51:36 +0900
> Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> > Hi,
> > 
> > Attached is the revised patch (v20) to add support for Incremental
> > Materialized View Maintenance (IVM).
> > 
> > In according with Konstantin's suggestion, I made a few optimizations.
> > 
> > 1. Creating an index on the matview automatically
> > 
> > When creating incremental maintainable materialized view (IMMV)s,
> > a unique index on IMMV is created automatically if possible.
> > 
> > If the view definition query has a GROUP BY clause, the index is created
> > on the columns of GROUP BY expressions. Otherwise, if the view contains
> > all primary key attributes of its base tables in the target list, the index
> > is created on these attributes.  Also, if the view has DISTINCT,
> > a unique index is created on all columns in the target list.
> > In other cases, no index is created.
> > 
> > In all cases, a NOTICE message is output to inform users that an index is
> > created or that an appropriate index is necessary for efficient IVM.
> > 
> > 2. Use a weaker lock on the matview if possible
> > 
> > If the view has only one base table in this query, RowExclusiveLock is
> > held on the view instead of AccessExclusiveLock, because we don't
> > need to wait other concurrent transaction's result in order to
> > maintain the view in this case. When the same row in the view is
> > affected due to concurrent maintenances, a row level lock will
> > protect it.
> > 
> > On Tue, 24 Nov 2020 12:46:57 +0300
> > Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:
> > 
> > > The most obvious optimization is not to use exclusive table lock if view 
> > > depends just on one table (contains no joins).
> > > Looks like there are no any anomalies in this case, are there?
> > 
> > I confirmed the effect of this optimizations.
> > 
> > First, when I performed pgbench (SF=100) without any materialized views,
> > the results is :
> >  
> >  pgbench test4 -T 300 -c 8 -j 4
> >  latency average = 6.493 ms
> >  tps = 1232.146229 (including connections establishing)
> > 
> > Next, created a view as below, I performed the same pgbench.
> >  CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm2 AS
> >         SELECT bid, count(abalance), sum(abalance), avg(abalance)
> >         FROM pgbench_accounts GROUP BY bid;
> > 
> > The result is here:
> > 
> > [the previous version (v19 with exclusive table lock)]
> >  - latency average = 77.677 ms
> >  - tps = 102.990159 (including connections establishing)
> > 
> > [In the latest version (v20 with weaker lock)]
> >  - latency average = 17.576 ms
> >  - tps = 455.159644 (including connections establishing)
> > 
> > There is still substantial overhead, but we can see that the effect
> > of the optimization.
> > 
> > Regards,
> > Yugo Nagata
> > 
> > -- 
> > Yugo NAGATA <nagata@sraoss.co.jp>
> 
> 
> -- 
> Yugo NAGATA <nagata@sraoss.co.jp>
> 
> 


-- 
Yugo NAGATA <nagata@sraoss.co.jp>