Re: [HACKERS] path toward faster partition pruning

Hi David.

On 2018/03/19 16:18, David Rowley wrote:
> 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.

Unfortunately, yes. :-(

Just recently, I replied to a pgsql-bugs report by someone who had OOM
kill a backend running `delete from
partitioned_table_with_7202_partitions` on their test system [1]. That'd
be because running inheritance_planner on a partitioned table doesn't cope
very well beyond a few hundred partitions, as we've also written in our
partitioning/inheritance documentation.

> 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.

Yes, that makes sense.

> 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.

Nice!

> 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.

Yeah, that'd be nice. Do you think that we cannot fix update/delete on
partitioned tables until we have such a patch though? IOW, did you intend
the patch you posted to just be a PoC to demonstrate that we can save tons
just by not doing grouping_planner() on pruned partitions?

BTW, maybe you know, but if we want this to prune same partitions as are
pruned during select (due to the new pruning facility), we'd need to teach
get_relation_constraints() to not fetch the partition constraint
(RelationGetPartitionQual) at all. My patch currently teaches it to avoid
fetching the partition constraint only for select. If we include the
partition constraint in the list of constraints returned by
get_relation_constraints, we'd still be redundantly executing the
constraint exclusion logic for the selected partitions via the
grouping_planner() call on those partitions.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/fecdef72-8c2a-0794-8e0a-2ad76db82c68@lab.ntt.co.jp