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

Hi David.

Thanks for writing the patch.

On 2018/04/20 14:47, David Rowley wrote:
> On 20 April 2018 at 14:07, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> 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.
>
> I hope the attached implements what is being discussed here.
>
> Please test it to ensure it behaves as you'd expect.
>
> I was a little unsure if the new GUCs declaration should live in
> costsize.c or not since it really has no effect on plan costs, but in
> the end, I stuck it there anyway so that it can be with its friends.

The patch looks good except one thing, which I was trying to emphasize
shouldn't be the behavior.

drop table p;
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);

set enable_partition_pruning to off;

-- ok
explain select * from p where a = 1;
QUERY PLAN
----------------------------------------------------------
Append (cost=0.00..83.88 rows=26 width=4)
-> Seq Scan on p1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> Seq Scan on p2 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
(5 rows)

reset enable_partition_pruning;
-- ok
explain select * from p where a = 1;
QUERY PLAN
----------------------------------------------------------
Append (cost=0.00..41.94 rows=13 width=4)
-> Seq Scan on p1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
(3 rows)

set enable_partition_pruning to off;

-- ok
explain update p set a = 2 where a = 1;
QUERY PLAN
-----------------------------------------------------------
Update on p (cost=0.00..83.75 rows=26 width=10)
Update on p1
Update on p2
-> Seq Scan on p1 (cost=0.00..41.88 rows=13 width=10)
Filter: (a = 1)
-> Seq Scan on p2 (cost=0.00..41.88 rows=13 width=10)
Filter: (a = 1)
(7 rows)

reset enable_partition_pruning;

-- ok
explain update p set a = 2 where a = 1;
QUERY PLAN
-----------------------------------------------------------
Update on p (cost=0.00..41.88 rows=13 width=10)
Update on p1
-> Seq Scan on p1 (cost=0.00..41.88 rows=13 width=10)
Filter: (a = 1)
(4 rows)

set constraint_exclusion to off;

-- not ok!
explain update p set a = 2 where a = 1;
QUERY PLAN
-----------------------------------------------------------
Update on p (cost=0.00..83.75 rows=26 width=10)
Update on p1
Update on p2
-> Seq Scan on p1 (cost=0.00..41.88 rows=13 width=10)
Filter: (a = 1)
-> Seq Scan on p2 (cost=0.00..41.88 rows=13 width=10)
Filter: (a = 1)
(7 rows)

I think we should teach relation_excluded_by_constraints() to forge ahead
based on the value of enable_partition_pruning, ignoring whatever
constraint_exclusion has been set to. What do you think of doing that
sort of thing?

Thanks,
Amit