Re: Implementing Incremental View Maintenance

From: Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
To: nagata(at)sraoss(dot)co(dot)jp
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, ishii(at)sraoss(dot)co(dot)jp, k(dot)knizhnik(at)postgrespro(dot)ru
Subject: Re: Implementing Incremental View Maintenance
Date: 2020-12-23 21:54:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi Yugo,

> 1. Creating an index on the matview automatically


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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2020-12-23 22:22:05 Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
Previous Message Stephen Frost 2020-12-23 19:27:32 Re: pg_ls_tmpdir to show directories and shared filesets (and pg_ls_*)