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

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@lab.ntt.co.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