Re: Implementing Incremental View Maintenance

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>