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

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:

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

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

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