|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
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.
> 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>
|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|