Re: Should we add GUCs to allow partition pruning to be disabled?

On 2019/03/11 11:13, David Rowley wrote:
> On Mon, 11 Mar 2019 at 15:00, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>>
>> On Mon, 11 Mar 2019 at 14:33, Amit Langote
>> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> PG 11 moved the needle a bit for SELECT queries:
>>>
>>> Excluding unnecessary partitions is slow for UPDATE and DELETE queries,
>>
>> With those words I expect the user might be surprised that it's still
>> slow after doing SET enable_partition_pruning = off;
>
> I had in mind in 10, 11 and master add a note to mention:

Thanks for putting this together.

> Currently, it is not recommended to have partition hierarchies more
> than a few hundred partitions. Larger partition hierarchies can
> suffer from slow planning times with <command>SELECT</command>
> queries. Planning times for <command>UPDATE</command> and
> <command>DELETE</command> commands may also suffer slow planning
> times, but in addition, memory consumption may also become an issue
> due to how the planner currently plans the query once per partition.
> These limitations are likely to be resolved in a future version of
> <productname>PostgreSQL</productname>.

How about slightly rewriting the sentence toward the end as:

memory consumption may also become an issue, because planner currently
plans the query once for every partition.

> I've not really thought too much on the fact that the issue also
> exists with inheritance tables in earlier version too.

That's fine maybe.

Thanks,
Amit