Re: Implementing Incremental View Maintenance

From: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
To: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Subject: Re: Implementing Incremental View Maintenance
Date: 2021-01-12 10:03:08
Message-ID: 20210112190308.ee9e98c24080310a487349f3@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sraoss(dot)co(dot)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(at)sraoss(dot)co(dot)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(dot)knizhnik(at)postgrespro(dot)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(at)sraoss(dot)co(dot)jp>
>
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>
>

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

Attachment Content-Type Size
IVM_patches_v21.tar.gz application/gzip 83.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2021-01-12 10:30:13 Re: O(n^2) system calls in RemoveOldXlogFiles()
Previous Message Kyotaro Horiguchi 2021-01-12 09:58:08 Re: In-placre persistance change of a relation