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

On Wed, Apr 18, 2018 at 5:37 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> 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?

I think GUC would be useful for debugging purposes for sure. Given
that we have added this feature late in v11, there might be some bugs
that will bite customers in production. It's better to provide them
some way to work-around.

>
> 1. Add a single enable_ GUC which allows both plan-time and run-time
> pruning to be disabled.

I would go for this. Both of those features have common code and it
will get cumbersome to carefully enable/disable them separately.

> 2. Add two new enable_ GUCs, one for plan-time and one for run-time pruning.

This would give more granularity but

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

If run-time pruning is disabled, why do we want to waste CPU cycles
and memory to produce plan time details? It might be useful to do so,
if there was a large chance that people prepared a statement which
could use partition pruning with run-time pruning disables but
EXECUTEd it with run-time pruning enabled. It will be less likely that
the session which prepares a plan would change the GUCs before
executing it.

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

That will still be useful for inheritance based partitioning.

We might re-use constraint_exclusion = 'partition' to mean
enable_partition_pruning (ok, I suggested a name as well) = true,
although that's not my favourite.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company