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

On Tue, Apr 17, 2018 at 6:12 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 18 April 2018 at 13:03, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> > My initial reaction is that we need to fix the bug introduced in v10 -
> > leaving constraint_exclusion working as it has historically and not
> affect
> > the new-as-of-10 ability to prune (maybe better termed as skip...)
> > partitions known during execution to contain no qualified tuples.
>
> Can you explain which bug in PG10 you are talking about? Did you
> perhaps mean PG11?
>

​"​In PG10 the planner's partition pruning could be disabled by changing
the constraint_exclusion GUC to off. This is still the case for PG11,
but only for UPDATE and DELETE queries. There is currently no way to
disable partition pruning for SELECT."

I read the word "currently" in your initial paragraph as meaning "currently
released", hence version v10. Re-reading it now I'm understanding you
meant currently to mean v11 and thus now so do I.

I'm not onboard with overloading the constraint_exclusion GUC any
> further to mean something it shouldn't. The PG11 partition pruning
> code does not use CHECK constraints to eliminate partitions, so I see
> no reason why constraint_exclusion should turn it on or off.

You propose that the "This is still the case for PG11, but only for UPDATE
and DELETE queries" is actually wrong and none of the query types should be
impacted?

​Basically go with partition pruning is always on, check constraint
evaluation defaults to off and can be turned on - and the current default
for "constraint_exclusion" changes to 'off' and if someone tries to
explicitly set it to 'partition' it fails. Add some new knobs for
partitions if desired.

I'd go that route in a green-field...I'm less convinced it is the best way
forward from today. non-partition related exclusion is something I'm not
understanding conceptually; and I don't know why one, outside of debugging
system code, would want to not perform partition related exclusion. I
could live with straight removal of the existing option and behave as if it
was indeed set to 'partition'.

David J.