From: | Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> |
---|---|
To: | k(dot)knizhnik(at)postgrespro(dot)ru |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Implementing Incremental View Maintenance |
Date: | 2020-11-12 11:53:55 |
Message-ID: | 20201112.205355.393498768557598337.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 1. Create pgbench database with scale 100.
> pgbench speed at my desktop is about 10k TPS:
>
> pgbench -M prepared -N -c 10 -j 4 -T 30 -P 1 postgres
> tps = 10194.951827 (including connections establishing)
>
> 2. Then I created incremental materialized view:
>
> create incremental materialized view teller_sums as select
> t.tid,sum(abalance) from pgbench_accounts a join pgbench_tellers t on
> a.bid=t.bid group by t.tid;
> SELECT 1000
> Time: 20805.230 ms (00:20.805)
>
> 20 second is reasonable time, comparable with time of database
> initialization.
>
> Then obviously we see advantages of precalculated aggregates:
>
> postgres=# select * from teller_sums where tid=1;
> tid | sum
> -----+--------
> 1 | -96427
> (1 row)
>
> Time: 0.871 ms
> postgres=# select t.tid,sum(abalance) from pgbench_accounts a join
> pgbench_tellers t on a.bid=t.bid group by t.tid having t.tid=1
> ;
> tid | sum
> -----+--------
> 1 | -96427
> (1 row)
>
> Time: 915.508 ms
>
> Amazing. Almost 1000 times difference!
>
> 3. Run pgbench once again:
>
> Ooops! Now TPS are much lower:
>
> tps = 141.767347 (including connections establishing)
>
> Speed of updates is reduced more than 70 times!
> Looks like we loose parallelism because almost the same result I get
> with just one connection.
How much TPS do you get if you execute pgbench -c 1 without
incremental materialized view defined? If it's around 141 then we
could surely confirm that the major bottle neck is locking contention.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2020-11-12 11:58:55 | Remove unused variable from SharedSort |
Previous Message | Bharath Rupireddy | 2020-11-12 10:47:43 | Re: Skip ExecCheckRTPerms in CTAS with no data |