MIME-Version: 1.0
References: 
 <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
In-Reply-To: 
 <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
From: Feike Steenbergen <feikesteenbergen@gmail.com>
Date: Thu, 9 Jan 2025 13:39:13 +0100
Message-ID: 
 <CAK_s-G3+2NnjYxixwBq2xgoJS8iBi6=4JDJzWDdg8oJZ0_vUZA@mail.gmail.com>
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a867e3062b454440"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK_s-G3%2B2NnjYxixwBq2xgoJS8iBi6%3D4JDJzWDdg8oJZ0_vUZA%40mail.gmail.com>
Precedence: bulk

--000000000000a867e3062b454440
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a867e3062b454440
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+QXMgYSBmb2xsb3d1cCwgdGhlc2UgYXJlIHRoZSBgRVhQTEFJTiAoQU5B
TFlaRSwgQ09TVFMgT0ZGLCBUSU1JTkcgT0ZGKWA8YnI+cGxhbnMgZm9yIHRoZSBwcmV2aW91cyB2
ZXJzaW9uIG9mIHRoZSBxdWVyeSBhbmQgdGhlIG9uZSB1c2luZyBOT1QgTUFUQ0hFRCBCWSBTT1VS
Q0UuPGJyPjxicj5QcmV2aW91cyBwbGFuOiDCoEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9ODQgZGlydGll
ZD0xIHdyaXR0ZW49MTxicj5OZXcgcGxhbjogwqAgwqAgwqAgQnVmZmVyczogc2hhcmVkIGhpdD0x
MDA2OTA8YnI+PGJyPipPbGQgcGxhbiwgdXNpbmcgZGVsZXRlIGluIENURSwgdGhlbiBNRVJHRSo8
YnI+PGJyPsKgTWVyZ2Ugb24gbWVyZ2VfdGFyZ2V0IHQgKGFjdHVhbCB0aW1lPTAuMTE3Li4wLjEx
OSByb3dzPTAgbG9vcHM9MSk8YnI+wqAgwqBUdXBsZXM6IGluc2VydGVkPTEwPGJyPsKgIMKgQnVm
ZmVyczogc2hhcmVkIGhpdD04NCBkaXJ0aWVkPTEgd3JpdHRlbj0xPGJyPsKgIMKgSS9PIFRpbWlu
Z3M6IHNoYXJlZCB3cml0ZT0wLjAxMjxicj7CoCDCoENURSBkZWxldGVkPGJyPsKgIMKgIMKgLSZn
dDsgwqBEZWxldGUgb24gbWVyZ2VfdGFyZ2V0IHRfMSAoYWN0dWFsIHRpbWU9MC4yMjIuLjAuMjIy
IHJvd3M9MCBsb29wcz0xKTxicj7CoCDCoCDCoCDCoCDCoCDCoEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9
MzA2PGJyPsKgIMKgIMKgIMKgIMKgIMKgLSZndDsgwqBIYXNoIEFudGkgSm9pbiAoYWN0dWFsIHRp
bWU9MC4wMzQuLjAuMTg3IHJvd3M9MTAwIGxvb3BzPTEpPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgSGFzaCBDb25kOiAodF8xLiZxdW90O3RpbWUmcXVvdDsgPSBzXzEuJnF1b3Q7dGltZSZx
dW90Oyk8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBCdWZmZXJzOiBzaGFyZWQgaGl0PTEw
Njxicj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoC0mZ3Q7IMKgQml0bWFwIEhlYXAgU2NhbiBv
biBtZXJnZV90YXJnZXQgdF8xIChhY3R1YWwgdGltZT0wLjAyMS4uMC4xNjMgcm93cz0xMDAgbG9v
cHM9MSk8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBSZWNoZWNrIENvbmQ6
IChkZXZpY2VfaWQgPSAxOSk8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBI
ZWFwIEJsb2NrczogZXhhY3Q9MTAyPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgQnVmZmVyczogc2hhcmVkIGhpdD0xMDU8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAtJmd0OyDCoEJpdG1hcCBJbmRleCBTY2FuIG9uIG1lcmdlX3RhcmdldF9wa2V5IChh
Y3R1YWwgdGltZT0wLjAxMC4uMC4wMTAgcm93cz0xMTAgbG9vcHM9MSk8YnI+wqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBJbmRleCBDb25kOiAoZGV2aWNlX2lkID0g
MTkpPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgQnVmZmVy
czogc2hhcmVkIGhpdD0zPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgLSZndDsgwqBIYXNo
IChhY3R1YWwgdGltZT0wLjAwNi4uMC4wMDYgcm93cz0xMCBsb29wcz0xKTxicj7CoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTxicj7CoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoC0mZ3Q7IMKgU2VxIFNjYW4gb24gbWVyZ2Vfc291
cmNlIHNfMSAoYWN0dWFsIHRpbWU9MC4wMDEuLjAuMDAyIHJvd3M9MTAgbG9vcHM9MSk8YnI+wqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBCdWZmZXJzOiBzaGFyZWQg
aGl0PTE8YnI+wqAgwqAtJmd0OyDCoE5lc3RlZCBMb29wIExlZnQgSm9pbiAoYWN0dWFsIHRpbWU9
MC4wMjAuLjAuMDMxIHJvd3M9MTAgbG9vcHM9MSk8YnI+wqAgwqAgwqAgwqAgwqBCdWZmZXJzOiBz
aGFyZWQgaGl0PTMxPGJyPsKgIMKgIMKgIMKgIMKgLSZndDsgwqBTZXEgU2NhbiBvbiBtZXJnZV9z
b3VyY2UgcyAoYWN0dWFsIHRpbWU9MC4wMDYuLjAuMDA3IHJvd3M9MTAgbG9vcHM9MSk8YnI+wqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqBCdWZmZXJzOiBzaGFyZWQgaGl0PTE8YnI+wqAgwqAgwqAgwqAg
wqAtJmd0OyDCoEluZGV4IFNjYW4gdXNpbmcgbWVyZ2VfdGFyZ2V0X3BrZXkgb24gbWVyZ2VfdGFy
Z2V0IHQgKGFjdHVhbCB0aW1lPTAuMDAyLi4wLjAwMiByb3dzPTAgbG9vcHM9MTApPGJyPsKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgSW5kZXggQ29uZDogKChkZXZpY2VfaWQgPSAxOSkgQU5EICgmcXVv
dDt0aW1lJnF1b3Q7ID0gcy4mcXVvdDt0aW1lJnF1b3Q7KSk8YnI+wqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqBCdWZmZXJzOiBzaGFyZWQgaGl0PTMwPGJyPsKgUGxhbm5pbmc6PGJyPsKgIMKgTWVtb3J5
OiB1c2VkPTgwa0IgwqBhbGxvY2F0ZWQ9MTQ0a0I8YnI+wqBQbGFubmluZyBUaW1lOiAwLjE4OSBt
czxicj7CoEV4ZWN1dGlvbiBUaW1lOiAwLjQxMiBtczxicj48YnI+PGJyPipOZXcgcGxhbiwgdXNp
bmcgTUVSR0UgV0hFTiBOT1QgTUFUQ0hFRCBCWSBTT1VSQ0UgQU5EIGRldmljZV9pZD0kMSo8YnI+
PGJyPsKgTWVyZ2Ugb24gbWVyZ2VfdGFyZ2V0IHQgKGFjdHVhbCByb3dzPTAgbG9vcHM9MSk8YnI+
wqAgwqBUdXBsZXM6IGluc2VydGVkPTEwIGRlbGV0ZWQ9MTAwIHNraXBwZWQ9OTk4MTA8YnI+wqAg
wqBCdWZmZXJzOiBzaGFyZWQgaGl0PTEwMDY5MDxicj7CoCDCoC0mZ3Q7IMKgSGFzaCBGdWxsIEpv
aW4gKGFjdHVhbCByb3dzPTk5OTIwIGxvb3BzPTEpPGJyPsKgIMKgIMKgIMKgIMKgSGFzaCBDb25k
OiAodC4mcXVvdDt0aW1lJnF1b3Q7ID0gcy4mcXVvdDt0aW1lJnF1b3Q7KTxicj7CoCDCoCDCoCDC
oCDCoEpvaW4gRmlsdGVyOiAodC5kZXZpY2VfaWQgPSAxOCk8YnI+wqAgwqAgwqAgwqAgwqBSb3dz
IFJlbW92ZWQgYnkgSm9pbiBGaWx0ZXI6IDEwPGJyPsKgIMKgIMKgIMKgIMKgQnVmZmVyczogc2hh
cmVkIGhpdD02Mzk8YnI+wqAgwqAgwqAgwqAgwqAtJmd0OyDCoFNlcSBTY2FuIG9uIG1lcmdlX3Rh
cmdldCB0IChhY3R1YWwgcm93cz05OTkxMCBsb29wcz0xKTxicj7CoCDCoCDCoCDCoCDCoCDCoCDC
oCDCoEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9NjM4PGJyPsKgIMKgIMKgIMKgIMKgLSZndDsgwqBIYXNo
IChhY3R1YWwgcm93cz0xMCBsb29wcz0xKTxicj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoEJ1Y2tl
dHM6IDEwMjQgwqBCYXRjaGVzOiAxIMKgTWVtb3J5IFVzYWdlOiA5a0I8YnI+wqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqBCdWZmZXJzOiBzaGFyZWQgaGl0PTE8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAtJmd0OyDCoFNlcSBTY2FuIG9uIG1lcmdlX3NvdXJjZSBzIChhY3R1YWwgcm93cz0xMCBsb29w
cz0xKTxicj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoEJ1ZmZlcnM6IHNoYXJlZCBo
aXQ9MTxicj7CoFBsYW5uaW5nOjxicj7CoCDCoE1lbW9yeTogdXNlZD00MWtCIMKgYWxsb2NhdGVk
PTgwa0I8YnI+wqBQbGFubmluZyBUaW1lOiAwLjY0MiBtczxicj7CoEV4ZWN1dGlvbiBUaW1lOiA1
NS4yMTMgbXM8YnI+PGJyPjwvZGl2Pg0K
--000000000000a867e3062b454440--