Re: Planning time of Generic plan for a table partitioned into a lot

On Tue, 27 Nov 2018 at 23:05, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> I found that making a generic plan of SELECT/UPDATE/DELETE for a table partitioned into thousands is slow.
> Especially, UPDATE/DELETE statement is too slow.

It's quite well known and also documented [1] that this is slow. The
manual reads:

"Currently, pruning of partitions during the planning of an UPDATE or
DELETE command is implemented using the constraint exclusion method
(however, it is controlled by the enable_partition_pruning rather than
constraint_exclusion) — see the following section for details and
caveats that apply."

and later on the same page:

"All constraints on all children of the parent table are examined
during constraint exclusion, so large numbers of children are likely
to increase query planning time considerably. So the legacy
inheritance based partitioning will work well with up to perhaps a
hundred child tables; don't try to use many thousands of children."

That documentation should be getting adjusted by [2] as that patch
aims to improve the performance of UPDATE/DELETE planning, and also
improve planning performance for when partitions are pruned. Although
I'm not sure this will do much for you SELECT case, since you're not
pruning any partitions during planning. There's been a discussion in
[3] about improving the performance of determining the relation's
size, which is known to be quite a bottleneck when generating a plan
which includes a partitioned table with a large number of partitions.

> I'm afraid that I could not come up with a good idea, but how can I shorten the creation time of a generic plan?

Since you mentioned the plan_cache_mode GUC, then I assume you're not
talking about any version of PostgreSQL that's been released, so if
you're looking for a way to make it faster in master then I'd suggest
helping with the review of [2]. If that patch does not meet your needs
then also help Thomas with [3]. If that's still not good enough then
you might need to do some research yourself. perf is your friend
there.

[1] https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
[2] https://commitfest.postgresql.org/20/1778/
[3] https://www.postgresql.org/message-id/flat/CAMyN-kCPin_stCMoXCVCq5J557e9-WEFPZTqdpO3j8wzoNVwNQ%40mail.gmail.com#e085c43b597b2775326afd9f3a2b6591

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