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

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>.