On 29 March 2018 at 07:37, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
>
>
> On Tue, Mar 27, 2018 at 5:00 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>
>>
>> In terms of further performance optimization, if there is just one
>> WHEN AND condition and no unconditional WHEN clauses then we can add
>> the WHEN AND easily to the join query.
>>
>> That seems like an easy thing to do for PG11
>>
>
> I think we need to be careful in terms of what can be pushed down to the
> join, in presence of WHEN NOT MATCHED actions. If we push the WHEN AND qual
> to the join then I am worried that some rows which should have been reported
> "matched" and later filtered out as part of the WHEN quals, will get
> reported as "not-matched", thus triggering WHEN NOT MATCHED action.
> postgres=# EXPLAIN ANALYZE MERGE INTO target t USING source s ON t.a = s.a
> WHEN MATCHED AND t.a < 2 THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN
> INSERT VALUES (s.a, -1);
That has an unconditional WHEN clause, so would block the push down
using my stated rule above.
With something like this
MERGE INTO target t USING source s ON t.a = s.a
WHEN MATCHED AND t.a < 2 THEN UPDATE SET b = s.b;
or this
MERGE INTO target t USING source s ON t.a = s.a
WHEN MATCHED AND t.a < 2 THEN UPDATE SET b = s.b
WHEN NOT MATCHED DO NOTHING;
or this
MERGE INTO target t USING source s ON t.a = s.a
WHEN MATCHED AND t.a < 2 THEN UPDATE SET b = s.b
WHEN MATCHED DO NOTHING
WHEN NOT MATCHED DO NOTHING;
then we can push down "t.a < 2" into the WHERE clause of the join query.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
