Re: How to make partitioning scale better for larger numbers of partitions

On 2018/07/13 16:29, Kato, Sho wrote:
> I also benchmark PG10.
> Actually, SELECT latency on PG11beta2 + patch1 is faster than PG10.
>
> SELECT latency with 800 leaf partition
> --------------------------------------
> PG10　5.62 ms
> PG11 3.869 ms
>
> But, even PG11, SELECT statement takes 21.102ms on benchmark with 1600 leaf partitions.
> It takes a long time though partition pruning algorithm of PG11 is binary search.

Yeah, pruning algorithm change evidently removed only a small percentage
of the overhead.

>> The overheads I mention stem from the fact that for partitioning we still rely on the old planner code that's used to perform inheritance planning, which requires to lock and open *all* partitions.
>
> I debug update statement execution on partitioned table.
> range_table_mutator seems process all leaf partitions.

That's one of the the symptoms of it, yes.

With the existing code for UPDATE/DELETE planning of partitioned tables,
the whole Query structure is modified to translate the parent's attribute
numbers to partition attribute numbers and planned freshly *for every
partition*. range_table_mutator() is invoked sometime during that
translation process and itself loops over all partitions, so I'd think it
is susceptible to being prominently visible in perf profiles.

Thanks,
Amit