On Tue, 26 Mar 2019 at 21:55, David Rowley <david.rowley@2ndquadrant.com> wrote:
>
> On Tue, 26 Mar 2019 at 21:23, Tsunakawa, Takayuki
> <tsunakawa.takay@jp.fujitsu.com> wrote:
> > Thank you David for explaining. Although I may not understand the effect of "speeding up planning with partitions"
patch,this patch takes effect even without it. That is, perform the following in the same session:
> >
> > 1. SELECT count(*) FROM table; on a table with many partitions. That bloats the LocalLockHash.
> > 2. PREPARE a point query, e.g., SELECT * FROM table WHERE pkey = $1;
> > 3. EXECUTE the PREPAREd query repeatedly, with each EXECUTE in a separate transaction. Without the patch, each
transaction'sLockReleaseAll() has to scan the bloated large hash table.
>
> Oh. I think I see what you're saying. Really the table in #2 would
> have to be some completely different table that's not partitioned. I
> think in that case it should make a difference.
Here a benchmark doing that using pgbench's script weight feature.
I've set this up so the query that hits the partitioned table runs
once for every 10k times the other script runs. I picked that number
so the lock table was expanded fairly early on in the benchmark.
setup:
create table t1 (a int primary key);
create table hp (a int) partition by hash (a);
select 'create table hp'||x|| ' partition of hp for values with
(modulus 4096, remainder ' || x || ');' from generate_series(0,4095)
x;
\gexec
hp.sql
select count(*) from hp;
t1.sql
\set p 1
select a from t1 where a = :p;
Master = c8c885b7a5
Master:
$ pgbench -T 60 -M prepared -n -f hp.sql@1 -f t1.sql@10000 postgres
SQL script 2: t1.sql
- 1057306 transactions (100.0% of total, tps = 17621.756473)
- 1081905 transactions (100.0% of total, tps = 18021.449914)
- 1122420 transactions (100.0% of total, tps = 18690.804699)
Master + 0002-speed-up-LOCALLOCK-scan.patch
$ pgbench -T 60 -M prepared -n -f hp.sql@1 -f t1.sql@10000 postgres
SQL script 2: t1.sql
- 1277014 transactions (100.0% of total, tps = 21283.551615)
- 1184052 transactions (100.0% of total, tps = 19734.185872)
- 1188523 transactions (100.0% of total, tps = 19785.835662)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
