Re: Implementing Incremental View Maintenance

Hi Yugo,

> 1. Creating an index on the matview automatically

Nice.

> 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.

Great improvement. Now with this patch more than 4x faster than
previous one!

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp