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

On 2019/03/11 13:22, Justin Pryzby wrote:
> On Mon, Mar 11, 2019 at 01:06:08PM +0900, Amit Langote wrote:
>> 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>.
>
> Can I offer the following variation:
>
> | Currently, it is not recommended to have partition hierarchies with more than
> | a few hundred partitions. Larger partition hierarchies may incur long
> | planning time.
> | In addition, <command>UPDATE</command> and <command>DELETE</command>
> | commands on larger hierarchies may cause excessive memory consumption.
> | These deficiencies are likely to be fixed in a future release of
> | <productname>PostgreSQL</productname>.

Says essentially the same thing but with fewer words, so +1.

Now the question is where to put this text? Currently, we have:

5.10. Table Partitioning
5.10.1. Overview
5.10.2. Declarative Partitioning
5.10.3. Implementation Using Inheritance
5.10.4. Partition Pruning
5.10.5. Partitioning and Constraint Exclusion

Should we add 5.10.6 Notes for the above "note", or should it be stuffed
under one of the existing sub-headings?

Thanks,
Amit