|From:||Tatsuo Ishii <ishii(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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
> 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.
> 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
SRA OSS, Inc. Japan
|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_*)|