On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > I did a bit of performance testing, both a plain pgbench and the > suggested test case with 4096 partitions. I can't detect any > performance improvements. In fact, within the noise, it tends to be > just a bit on the slower side. > > So I'd like to kick it back to the patch submitter now and ask for more > justification and performance analysis. > > Perhaps "speeding up planning with partitions" needs to be accepted first?
Yeah, I think it likely will require that patch to be able to measure the gains from this patch. If planning a SELECT to a partitioned table with a large number of partitions using PREPAREd statements, when we attempt the generic plan on the 6th execution, it does cause the local lock table to expand to fit all the locks for each partition. This does cause the LockReleaseAll() to become slow due to the hash_seq_search having to skip over many empty buckets. Since generating a custom plan for a partitioned table with many partitions is still slow in master, then I very much imagine you'll struggle to see the gains brought by this patch. I did a quick benchmark too and couldn't measure anything: 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; bench.sql \set p_a 13315 select * from hp where a = :p_a; Master: $ pgbench -M prepared -n -T 60 -f bench.sql postgres tps = 31.844468 (excluding connections establishing) tps = 32.950154 (excluding connections establishing) tps = 31.534761 (excluding connections establishing) Patched: $ pgbench -M prepared -n -T 60 -f bench.sql postgres tps = 30.099133 (excluding connections establishing) tps = 32.157328 (excluding connections establishing) tps = 32.329884 (excluding connections establishing) The situation won't be any better with plan_cache_mode = force_generic_plan either. In this case, we'll only plan once but we'll also have to obtain and release a lock for each partition for each execution of the prepared statement. LockReleaseAll() is going to be slow in that case because it actually has to release a lot of locks. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
