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

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.

Should we allow this?

To make this a bit more complex, we now also have run-time pruning
which can allow further partition pruning to be performed during
execution. I imagine if we're going to add a GUC for plan-time
pruning then we should also have one for run-time pruning. These could
also perhaps share the same GUC, so it seems there are some sub
choices to make here:

1. Add a single enable_ GUC which allows both plan-time and run-time
pruning to be disabled.
2. Add two new enable_ GUCs, one for plan-time and one for run-time pruning.
3. No new GUCs / Do nothing.

Run-time pruning is a little special here too, as it's the first
feature to exist in PostgreSQL which changes the plan in the executor.
From searching through the code I see no other enable_* GUC being
referenced in the executor. So there's also questions here as to
where we'd disable run-time pruning. We could disable it in the
planner so that the plan does not include the details that the
executor needs to enable the pruning, or we could just disable it in
the executor and have the planner still form plans with these details.
This separates #1 and #2 into:

a) Disable run-time pruning during execution.
b) Disable run-time pruning during planning.
c) Both of the above.

The differentiation of the above is important when you consider
PREPAREd statements. Currently, no enable_ GUC will affect a
pre-PREPAREd query. We might want to keep that rule despite there
being flexibility not to, in this case.

For UPDATE/DELETE:
It would also be quite strange if someone disabled plan-time pruning
and still got partition pruning. So I suggest we require both
constraint_exclusion and the plan-time GUC not off for pruning to be
enabled for UPDATE/DELETE. Alternatively, we just ditch
constraint_exclusion = 'partition'.

Personally, I'm for 2b and ditching constraint_exclusion =
'partition'. I don't see any sense in keeping constraint_exclusion =
'partition' if we have something else to mean the same thing.

Thoughts / Votes / Names for new GUCs?

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