Re: speeding up planning with partitions

On Wed, Aug 29, 2018 at 5:36 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> It is more or less well known that the planner doesn't perform well with
> more than a few hundred partitions even when only a handful of partitions
> are ultimately included in the plan. Situation has improved a bit in PG
> 11 where we replaced the older method of pruning partitions one-by-one
> using constraint exclusion with a much faster method that finds relevant
> partitions by using partitioning metadata. However, we could only use it
> for SELECT queries, because UPDATE/DELETE are handled by a completely
> different code path, whose structure doesn't allow it to call the new
> pruning module's functionality. Actually, not being able to use the new
> pruning is not the only problem for UPDATE/DELETE, more on which further
> below.
>
>
> pgbench -n -T 60 -f update.sql
>
> nparts master 0001 0002 0003
> ====== ====== ==== ==== ====
> 0 2856 2893 2862 2816
> 8 507 1115 1447 1872
> 16 260 765 1173 1892
> 32 119 483 922 1884
> 64 59 282 615 1881
> 128 29 153 378 1835
> 256 14 79 210 1803
> 512 5 40 113 1728
> 1024 2 17 57 1616
> 2048 0* 9 30 1471
> 4096 0+ 4 15 1236
> 8192 0= 2 7 975
>
> * 0.46
> + 0.0064
> = 0 (OOM on a virtual machine with 4GB RAM)
>

The idea looks interesting while going through the patch I observed
this comment.

/*
* inheritance_planner
* Generate Paths in the case where the result relation is an
* inheritance set.
*
* We have to handle this case differently from cases where a source relation
* is an inheritance set. Source inheritance is expanded at the bottom of the
* plan tree (see allpaths.c), but target inheritance has to be expanded at
* the top.

I think with your patch these comments needs to be change?

if (parse->resultRelation &&
- rt_fetch(parse->resultRelation, parse->rtable)->inh)
+ rt_fetch(parse->resultRelation, parse->rtable)->inh &&
+ rt_fetch(parse->resultRelation, parse->rtable)->relkind !=
+ RELKIND_PARTITIONED_TABLE)
inheritance_planner(root);
else
grouping_planner(root, false, tuple_fraction);

I think we can add some comments to explain if the target rel itself
is partitioned rel then why
we can directly go to the grouping planner.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com