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(dot)pedersen(at)redhat(dot)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