Continuing along on my adventures of making performance improvements for partitioned tables, I discovered that getrelid is quite slow in InitPlan() during UPDATEs and DELETEs when there are many resultRelations to process. A List is a pretty poor choice for this data structure since we already know how big it is and we pretty much only ever perform lookups by the List's Nth element.
Converting this to an array obviously improves that O(n) List lookup
into an O(1) operation.
Benchmark against today's master with 300 partitions using prepared statements:
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
ALTER SYSTEM SET plan_cache_mode = 'force_generic_plan'; -- minimise
planning overhead.
update.sql:
\set p_id 29999999
update partbench set i1 = i1+1 where id = :p_id;
pgbench -n -M prepared -T 60 -f update.sql postgres
Unpatched:
tps = 558.708604 (excluding connections establishing)
tps = 556.128341 (excluding connections establishing)
tps = 569.096393 (excluding connections establishing)
Patched:
tps = 683.271356 (excluding connections establishing)
tps = 678.693578 (excluding connections establishing)
tps = 694.446564 (excluding connections establishing)
(about 22% improvement)
Of course, it would be much nicer if PlannedStmt->rtable was already
an array and we didn't have to convert it to one during executor
startup. We'd need to build an array that's a Node type for that to
work. I did suggest something ([1]) last year in this regard, but I
felt like it might be a bit of an uphill struggle to gain enough
community support to make that work. So I feel like this patch is
quite worthwhile in the meantime.
The (fairly small) patch is attached.
I'll add this to the September 'fest.
[1]
https://www.postgresql.org/message-id/flat/CAKJS1f_2SnXhPVa6eWjzy2O9A%3Docwgd0Cj-LQeWpGtrWqbUSDA%40mail.gmail.com
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
v1-0001-Change-the-executor-s-range-table-from-a-List-to-.patch
Description: Binary data
