On 29 March 2018 at 07:37, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > > On Tue, Mar 27, 2018 at 5:00 PM, Simon Riggs <si...@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
