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

On 2019/03/11 0:25, Justin Pryzby wrote:
> On Sun, Mar 10, 2019 at 10:53:02PM +1300, David Rowley wrote:
>> 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."
>
>> 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.
>
> +1
>
> I believe I was of the same mind when I wrote:
> https://www.postgresql.org/message-id/flat/20180525215002.GD14378%40telsasoft.com#c9de33b17fe63cecad4ac30fb1662531

I agree PG 11 didn't improve things enough to have removed such a warning
from the documentation even for partitioning. Actually, we only ever had
a warning about constraint exclusion getting slower as more children are
added, but nothing about UPDATE/DELETE planning being slow in itself;
perhaps more importantly, much slower than SELECT. It seems very hard to
put that in the documentation though.

In PG 10:

Excluding unnecessary partitions is slow, especially as the number of
partitions increases, because constraint exclusion needs to look at each
partition to determine whether it could be excluded. Also, planning for
UPDATE and DELETE queries is significantly slower than for SELECT queries
for $REASONS. Given that, it is wise to use up to a few hundred
partitions but not more.

PG 11 moved the needle a bit for SELECT queries:

Excluding unnecessary partitions is slow for UPDATE and DELETE queries,
especially as the number of partitions increases, because constraint
exclusion needs to look at each partition to determine whether it could be
excluded. Also, planning for UPDATE and DELETE queries is significantly
slower than for SELECT queries for $REASONS. Given that, it is wise to
use up to a few hundred partitions but not more.

Thanks,
Amit