While reviewing some other patches to improve partitioning performance I noticed that one of the loops in ExecFindInitialMatchingSubPlans() could be coded a bit more efficiently. The current code loops over all the original subplans checking if the subplan is newly pruned, if it is, the code sets the new_subplan_indexes array element to -1, else it sets it assigns the new subplan index. This can be done more efficiently if we make this array 1-based and initialise the whole thing to 0 then just loop over the non-pruned subplans instead of all subplans. Pruning all but 1 subplan is quite common.
In profiles, I'd seen ExecFindInitialMatchingSubPlans() consume about
5.2% percent of CPU time. With the patch that dropped to 0.72%.
A quick test with just 300 partitions shows about a 2.3% performance
improvement. Hardly groundbreaking, but it seems like a small enough
change for it to be worth it.
The test was conducted as follows:
postgresql.conf:
plan_cache_mode = 'force_generic_plan'
max_parallel_workers_per_gather = 0
setup:
CREATE TABLE partbench (id BIGINT NOT NULL, i1 INT NOT NULL, i2 INT
NOT NULL, i3 INT NOT NULL, i4 INT NOT NULL, i5 INT NOT NULL) PARTITION
BY RANGE (id);
\o /dev/null
select 'CREATE TABLE partbench' || x::text || ' PARTITION OF partbench
FOR VALUES FROM (' || (x*100000)::text || ') TO (' ||
((x+1)*100000)::text || ');' from generate_Series(0,299) x;
\gexec
\o
select.sql:
\set p_id 29999999
select * from partbench where id = :p_id;
Test:
$ pgbench -n -f select.sql -M prepared -T 60 postgres
Unpatched:
tps = 6946.940678 (excluding connections establishing)
tps = 6913.993655 (excluding connections establishing)
tps = 6854.693214 (excluding connections establishing)
Patched
tps = 7066.854267 (excluding connections establishing)
tps = 7082.890458 (excluding connections establishing)
tps = 7052.255429 (excluding connections establishing)
Patch attached. I'll park this here until the November 'fest.
I've also included an additional test to ensure the other_subplans
gets updated correctly. The other tests for this seem to only perform
run-time pruning during init plan and do no further pruning, so don't
fully test that other_subplans gets updated correctly.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
v1-0001-Improve-performance-of-run-time-partition-pruning.patch
Description: Binary data
