Re: [HACKERS] Runtime Partition Pruning

Hi David.

On 2018/04/04 11:10, David Rowley wrote:
> On 4 April 2018 at 05:44, Jesper Pedersen <jesper.pedersen@redhat.com> wrote:
>> Also, I'm seeing a regression for check-world in
>> src/test/regress/results/inherit.out
>>
>> ***************
>> *** 642,648 ****
>>   ---------------------+---+---+-----
>>    mlparted_tab_part1  | 1 | a |
>>    mlparted_tab_part2a | 2 | a |
>> !  mlparted_tab_part2b | 2 | b | xxx
>>    mlparted_tab_part3  | 3 | a | xxx
>>   (4 rows)
>>
>> --- 642,648 ----
>>   ---------------------+---+---+-----
>>    mlparted_tab_part1  | 1 | a |
>>    mlparted_tab_part2a | 2 | a |
>> !  mlparted_tab_part2b | 2 | b |
>>    mlparted_tab_part3  | 3 | a | xxx
>>   (4 rows)
>>
>> I'll spend some more time tomorrow.
> 
> Yeah, it's a bug in v46 faster partition pruning. Discussing a fix for
> that with Amit over on [2].

I'm not sure if we've yet discussed anything that'd be related to this on
the faster pruning thread.  It seems that the difference arises from
mlparted_tab_part2b not being selected for an update query that's executed
just before this test.  When I execute an equivalent select query to check
if mlparted_tab_part2b is inadvertently pruned due to the new code, I
don't see the latest faster pruning patch doing it:

explain (costs off)
select *
from mlparted_tab mlp,
    (select a from some_tab union all select a+1 from some_tab) ss (a)
where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
                                    QUERY PLAN

----------------------------------------------------------------------------------
 Nested Loop
   Join Filter: (((mlp.a = some_tab.a) AND (mlp.b = 'b'::bpchar)) OR
(mlp.a = 3))
   ->  Append
         ->  Seq Scan on some_tab
         ->  Seq Scan on some_tab some_tab_1
   ->  Materialize
         ->  Append
               ->  Seq Scan on mlparted_tab_part1 mlp
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
               ->  Seq Scan on mlparted_tab_part2b mlp_1
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
               ->  Seq Scan on mlparted_tab_part3 mlp_2
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
(13 rows)

For the original update query, constraint exclusion selects the same set
of partitions:

explain (costs off) update mlparted_tab mlp set c = 'xxx'
from (select a from some_tab union all select a+1 from some_tab) ss (a)
where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
                                          QUERY PLAN

----------------------------------------------------------------------------------------------
 Update on mlparted_tab mlp
   Update on mlparted_tab_part1 mlp_1
   Update on mlparted_tab_part2b mlp_2
   Update on mlparted_tab_part3 mlp_3
   ->  Nested Loop
         Join Filter: (((mlp_1.a = some_tab.a) AND (mlp_1.b =
'b'::bpchar)) OR (mlp_1.a = 3))
         ->  Append
               ->  Seq Scan on some_tab
               ->  Seq Scan on some_tab some_tab_1
         ->  Materialize
               ->  Seq Scan on mlparted_tab_part1 mlp_1
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
   ->  Nested Loop
         Join Filter: (((mlp_2.a = some_tab.a) AND (mlp_2.b =
'b'::bpchar)) OR (mlp_2.a = 3))
         ->  Append
               ->  Seq Scan on some_tab
               ->  Seq Scan on some_tab some_tab_1
         ->  Materialize
               ->  Seq Scan on mlparted_tab_part2b mlp_2
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
   ->  Nested Loop
         Join Filter: (((mlp_3.a = some_tab.a) AND (mlp_3.b =
'b'::bpchar)) OR (mlp_3.a = 3))
         ->  Append
               ->  Seq Scan on some_tab
               ->  Seq Scan on some_tab some_tab_1
         ->  Materialize
               ->  Seq Scan on mlparted_tab_part3 mlp_3
                     Filter: ((b = 'b'::bpchar) OR (a = 3))
(28 rows)

What am I missing?

Thanks,
Amit