On Tue, 27 Nov 2018 at 23:05, Kato, Sho <kato-...@jp.fujitsu.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