Re: [HACKERS] path toward faster partition pruning

On 17 March 2018 at 01:55, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Hope the attached version is easier to understand.

Hi Amit,

Thanks for making the updates. I'll look at them soon.

I've been thinking about how we're making these improvements for
SELECT only. If planning for an UPDATE or DELETE of a partitioned
table then since the inheritance planner is planning each partition
individually we gain next to nothing from this patch.

Generally, it seems the aim of this patch is to improve the usability
of partitions in an OLTP type workload, most likely OLAP does not
matter as much since planner overhead, in that case, is generally less
of a concern.

I experimented with the attached small patch to see if the situation
could be improved if we first plan the entire query with all
partitions then ignore dummy rels when planning for each individual
partition.

I used something along the lines of:

# create table listp (a int, b int) partition by list(a);
# select 'create table listp'||x||' partition of listp for values
in('||x||');' from generate_series(1, <number of tables>)x;
$ echo explain update listp set b = 1 where a = 1; > bench.sql
$ pgbench -f bench.sql -n -T 30 postgres

where <number of tables> started at 1 and went up in powers of 2 until 1024.

Unpatched = your v35 patch
Patched = your v35 + the attached.

The TPS result from a 30-second pgbench run of the above query showed:

Partitions = 1
Unpatched: 7323.3
Patched: 6573.2 (-10.24%)

Partitions = 2
Unpatched: 6784.8
Patched: 6377.1 (-6.01%)

Partitions = 4
Unpatched: 5903.0
Patched: 6106.8 (3.45%)

Partitions = 8
Unpatched: 4582.0
Patched: 5579.9 (21.78%)

Partitions = 16
Unpatched: 3131.5
Patched: 4521.2 (44.38%)

Partitions = 32
Unpatched: 1779.8
Patched: 3387.8 (90.35%)

Partitions = 64
Unpatched: 821.9
Patched: 2245.4 (173.18%)

Partitions = 128
Unpatched: 322.2
Patched: 1319.6 (309.56%)

Partitions = 256
Unpatched: 84.3
Patched: 731.7 (768.27%)

Partitions = 512
Unpatched: 22.5
Patched: 382.8 (1597.74%)

Partitions = 1024
Unpatched: 5.5
Patched: 150.1 (2607.83%)

Which puts the crossover point at just 4 partitions, and just a small
overhead for 1, 2 and probably 3 partitions. The planner generated a
plan 26 times faster (!) with 1024 partitions.

Likely there's more than could be squeezed out of this if we could get
the grouping_planner() to somehow skip creating paths and performing
the join search. But that patch is not nearly as simple as the
attached.

Probably grouping_planner could also be called with inheritance_update
= false, for this one case too, which might save a small amount of
effort.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services