Re: Implementing Incremental View Maintenance

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>