On Tue, 26 Mar 2019 at 21:55, David Rowley <david.row...@2ndquadrant.com> wrote: > > On Tue, 26 Mar 2019 at 21:23, Tsunakawa, Takayuki > <tsunakawa.ta...@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's LockReleaseAll() 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
