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

On Fri, 11 May 2018 at 17:37, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 5. The last sentence in caveats, that is,
>
> "Partitioning using these techniques will work well with up to perhaps a
> hundred partitions; don't try to use many thousands of partitions."
>
> should perhaps be reworded as:
>
> "So the legacy inheritance based partitioning will work well with up to
> perhaps a hundred partitions; don't try to use many thousands of partitions."

(digging up 10-month-old thread [1])

There was a report [2] on -general today where someone had a 4000
partition partitioned table and were complaining about memory
consumption in the planner during DELETE. They didn't mention the
exact version they were using, but mentioned that the problem exists
on 10, 11 and master. Of course, we're well aware of this issue with
DELETE and UPDATE of large partition hierarchies, Amit has been
working hard with trying to solve it for PG12.

In the -general post, I was just about to point them at the part in
the documents that warn against these large partition hierarchies, but
it looks like the warning was removed in bebc46931a1, or at least
modified to say that constraint exclusion with heritance tables is
slow. I really wonder if we shouldn't put something back in there to
warn against this sort of thing. It might be a bit late for the
people who've read the docs and done it already, but a warning might
at least stop new people making the mistake.

Hopefully one day we can remove the warning again, but it won't be for PG12.

Thoughts?

[1] https://www.postgresql.org/message-id/6bc4e96a-0e30-e9b6-dcc7-791c7486a491%40lab.ntt.co.jp
[2] https://www.postgresql.org/message-id/739b7a5e-1192-1011-5aa2-41adad55682d%40perfexpert.ch

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