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

On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2018/04/19 21:50, Ashutosh Bapat wrote:
>> On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote
>>> 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.
>
> Yes, that's what I meant.
>
> To clarify: if we're going to add a new parameter *for partitioned tables*
> to configure whether or not pruning occurs, even if UPDATE and DELETE now
> rely on constraint exclusion for pruning, we should ignore the setting of
> constraint_exclusion the configuration parameter. For UPDATE and DELETE,
> if enable_partition_pruning is on, we proceed to prune using constraint
> exclusion (because that's the only method available now), irrespective of
> the setting of constraint_exclusion.
>
> So to users, enable_partition_pruning should be the only way to configure
> whether or not pruning occurs.
>
> Does that make sense?
>
> It seems like talking about the finer implementation details is making
> this discussion a bit confusing.
>
>> 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.
>
> That's exactly what I'm trying to propose.

Not really. By pruning based on the partition bounds I didn't mean
constraint exclusion working on partition bound based constraints.

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