> 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
