As a followup, these are the `EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)`
plans for the previous version of the query and the one using NOT MATCHED
BY SOURCE.
Previous plan: Buffers: shared hit=84 dirtied=1 written=1
New plan: Buffers: shared hit=100690
*Old plan, using delete in CTE, then MERGE*
Merge on merge_target t (actual time=0.117..0.119 rows=0 loops=1)
Tuples: inserted=10
Buffers: shared hit=84 dirtied=1 written=1
I/O Timings: shared write=0.012
CTE deleted
-> Delete on merge_target t_1 (actual time=0.222..0.222 rows=0
loops=1)
Buffers: shared hit=306
-> Hash Anti Join (actual time=0.034..0.187 rows=100 loops=1)
Hash Cond: (t_1."time" = s_1."time")
Buffers: shared hit=106
-> Bitmap Heap Scan on merge_target t_1 (actual
time=0.021..0.163 rows=100 loops=1)
Recheck Cond: (device_id = 19)
Heap Blocks: exact=102
Buffers: shared hit=105
-> Bitmap Index Scan on merge_target_pkey (actual
time=0.010..0.010 rows=110 loops=1)
Index Cond: (device_id = 19)
Buffers: shared hit=3
-> Hash (actual time=0.006..0.006 rows=10 loops=1)
Buffers: shared hit=1
-> Seq Scan on merge_source s_1 (actual
time=0.001..0.002 rows=10 loops=1)
Buffers: shared hit=1
-> Nested Loop Left Join (actual time=0.020..0.031 rows=10 loops=1)
Buffers: shared hit=31
-> Seq Scan on merge_source s (actual time=0.006..0.007 rows=10
loops=1)
Buffers: shared hit=1
-> Index Scan using merge_target_pkey on merge_target t (actual
time=0.002..0.002 rows=0 loops=10)
Index Cond: ((device_id = 19) AND ("time" = s."time"))
Buffers: shared hit=30
Planning:
Memory: used=80kB allocated=144kB
Planning Time: 0.189 ms
Execution Time: 0.412 ms
*New plan, using MERGE WHEN NOT MATCHED BY SOURCE AND device_id=$1*
Merge on merge_target t (actual rows=0 loops=1)
Tuples: inserted=10 deleted=100 skipped=99810
Buffers: shared hit=100690
-> Hash Full Join (actual rows=99920 loops=1)
Hash Cond: (t."time" = s."time")
Join Filter: (t.device_id = 18)
Rows Removed by Join Filter: 10
Buffers: shared hit=639
-> Seq Scan on merge_target t (actual rows=99910 loops=1)
Buffers: shared hit=638
-> Hash (actual rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on merge_source s (actual rows=10 loops=1)
Buffers: shared hit=1
Planning:
Memory: used=41kB allocated=80kB
Planning Time: 0.642 ms
Execution Time: 55.213 ms
