Hello,
I spent some time to test the new features on partitioning with the
beta1. I noticed a potentially huge performance regression with
plan-time partition pruning.
To show the issue, I used this DO statement to generate some partitions,
one per day :
DO $$
DECLARE
part_date date;
ddl text;
BEGIN
CREATE TABLE t1 (
num INTEGER NOT NULL,
dt DATE NOT NULL
) PARTITION BY LIST (dt);
FOR part_date IN SELECT d FROM generate_series(date '2010-01-01',
'2020-12-31', interval '1 day') d LOOP
ddl := 'CREATE TABLE t1_' || to_char(part_date, 'YYYY_MM_DD') || E'
PARTITION OF t1 FOR VALUES IN (\'' || part_date || E'\')';
EXECUTE ddl;
END LOOP;
END;
$$;
Then I used the following to compare the planning time :
explain (analyze) SELECT * FROM t1 WHERE dt = '2018-05-25';
With PostgreSQL 10, planning time is 66ms, in v11, planning rise to
143ms. I also did a little test with more than 20k partitions, and while
the planning time was reasonable with PG10 (287.453 ms), it exploded
with v11 with 4578.054 ms.
Perf showed that thes functions find_appinfos_by_relids and
bms_is_member consumes most of the CPU time with v11. With v10, this
functions don't appear. It seems that find_appinfos_by_relids was
introduced by commit 480f1f4329f.
Regards,
Thomas
