Re: Should we add GUCs to allow partition pruning to be disabled? - Mailing list pgsql-hackers

On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2018/04/19 13:32, Ashutosh Bapat wrote:
>> On Thu, Apr 19, 2018 at 2:54 AM, David Rowley
>>> The more I think about this the more undecided I am as to whether we
>>> need to add a GUC for this at all, so I'm keen to hear more people
>>> voice their opinion about this.  If bugs are the only true reason to
>>> add it, then the need for the GUC should diminish every day that
>>> nobody reports any bugs.
>>>
>>
>> Apart from bugs, I think, this GUC can be used to avoid extra planning
>> time/memory/CPU incurred in pruning, when users know for sure that
>> pruning is not going to happen e.g. the cases like no qual on
>> partition key or no equality qual on hash partition key etc. Do we
>> know how much planning time can be saved this way?
>
> I can imagine having a enable_partition_pruning which defaults to true, if
> only to avoid the performance overhead of pruning code when a user knows
> for sure that it won't help for some queries.  Although, I'm a bit dubious
> why they'd write such queries if they're using partitioning in the first
> place.
>
> Also, I'd think that enable_partition_pruning set to false means pruning
> doesn't occur at all, not even using constraint exclusion.  That is,
> behavior equivalent of constraint_exclusion < partition (that is, off/on).
>
> Also, if we do have such a GUC, it should apply to all command types,
> including UPDATE and DELETE which don't yet invoke the new pruning code,
> from the start.  So, if enable_partition_pruning is false, we won't load
> the partition constraints at all, which we currently do for UPDATE and
> DELETE so that constraint exclusion can be used for pruning.  OTOH, if
> enable_partition_pruning is on, we perform constraint exclusion -based
> pruning for UPDATE and DELETE irrespective of the setting of
> constraint_exclusion GUC.  In other words, we completely dissociate
> partitioned table pruning from the setting of constraint_exclusion.

Isn't word "dissociate" turns the last sentence into a sentence
contradicting everything you wrote prior to it?

I think we should keep these two things separate.
enable_partition_pruning affects the partition pruning based on the
partition bounds and that currently does not work for UPDATE/DELETE.
When it does work in those case, we might think of not loading
partition bound based constraints. constraint_exclusion affects
whether constraints can be used to exclude a relation (with partition
option affecting the child tables). Once we stop loading partition
bound based constraints, constraint exclusion would stop pruning
partitions based on the bounds. There's no point in confusing users
with by adding dependencies between these two GUCs.

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