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

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.

Now as Justin pointed out upthread, the new GUC might cause confusion for
users who are long accustomed to using constraint_exclusion for this, but
I'm not sure anybody tries to change its setting a lot. The new GUC
defaulting to pruning=on would be useful for occasional debugging, as we
all seem to more or less agree.

Thanks,
Amit