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

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. I don't want any new GUC to
work only for SELECT now and UPDATE/DELETE only later when we teach the
code path handling the latter to use the new pruning implementation. In
other words, I don't want a situation where two parameters control pruning
for partitioned tables in PG 11.

BTW, should this thread be listed somewhere on the open items page?

Thanks,
Amit