MIME-Version: 1.0
From: Feike Steenbergen <feikesteenbergen@gmail.com>
Date: Thu, 9 Jan 2025 13:25:45 +0100
Message-ID: 
 <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
Subject: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
To: pgsql-performance@postgresql.org
Content-Type: multipart/mixed; boundary="00000000000087a373062b45148f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2%3DE94qfdPA%40mail.gmail.com>
Precedence: bulk

--00000000000087a373062b45148f
Content-Type: multipart/alternative; boundary="00000000000087a372062b45148d"

--00000000000087a372062b45148d
Content-Type: text/plain; charset="UTF-8"

I'm trying to change a few applications to fully use this, as PostgreSQL 17
added this support.

The application does something like this:

- fetch information from a source system and store it in a temp table
- run a MERGE with a table (target) in this database,
  updating, inserting and deleting in a single statement
- the target table holds information for multiple systems

The temp table (source) doesn't contain the identifier for the system, as
we can inject that at runtime.

This is the shape of the statement:

    MERGE INTO
        merge_target AS t
    USING
        merge_source AS s ON (t.time = s.time AND t.device_id = $1)
    WHEN MATCHED THEN
        UPDATE set
            value = s.value
    WHEN NOT MATCHED THEN
        INSERT (device_id, time, value) VALUES ($1, time, value)
    WHEN NOT MATCHED BY SOURCE
        AND t.device_id = $1
        THEN DELETE;

If we run this however, there is always a Seq Scan against merge_target,
whereas the filter of `AND t.device_id = $1` uses a (Bitmap) Index scan
in other types of queries.

Previously we would - in a CTE - do a delete *and then* the merge.

Which performed much better as the DELETE would not do a Seq Scan.

Attached a full reproducing test case. Some numbers:

Previously, DELETE in CTE, then merge:
    Planning Time: 6.700 ms
    Execution Time: 7.473 ms

Using the MERGE WHEN MATCHED ON SOURCE THEN DELETE:
    Planning Time: 0.994 ms
    Execution Time: 65.695 ms

My questions are:

- is the Seq Scan expected by others in this mailing list as well?
- Is it required to do the Seq Scan?
- is this something that could be optimized?

Kind regards,

Feike Steenbergen

--00000000000087a372062b45148d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I&#39;m trying to change a few applications to fully use t=
his, as PostgreSQL 17 added this support.<br><br>The application does somet=
hing like this:<br><br>- fetch information from a source system and store i=
t in a temp table<br>- run a MERGE with a table (target) in this database,<=
br>=C2=A0 updating, inserting and deleting in a single statement<br>- the t=
arget table holds information for multiple systems<br><br>The temp table (s=
ource) doesn&#39;t contain the identifier for the system, as<br>we can inje=
ct that at runtime.<br><br>This is the shape of the statement:<br><br>=C2=
=A0 =C2=A0 MERGE INTO<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 merge_target AS t<br>=
=C2=A0 =C2=A0 USING<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 merge_source AS s ON (t.=
time =3D s.time AND t.device_id =3D $1)<br>=C2=A0 =C2=A0 WHEN MATCHED THEN<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 UPDATE set<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 value =3D s.value<br>=C2=A0 =C2=A0 WHEN NOT MATCHED THEN<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT (device_id, time, value) VALUES ($1, tim=
e, value)<br>=C2=A0 =C2=A0 WHEN NOT MATCHED BY SOURCE<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 AND t.device_id =3D $1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 THEN DELET=
E;<br><br>If we run this however, there is always a Seq Scan against merge_=
target,<br>whereas the filter of `AND t.device_id =3D $1` uses a (Bitmap) I=
ndex scan<br>in other types of queries.<br><br>Previously we would - in a C=
TE - do a delete *and then* the merge.<br><br>Which performed much better a=
s the DELETE would not do a Seq Scan.<br><br>Attached a full reproducing te=
st case. Some numbers:<br><br>Previously, DELETE in CTE, then merge:<br>=C2=
=A0 =C2=A0 Planning Time: 6.700 ms<br>=C2=A0 =C2=A0 Execution Time: 7.473 m=
s<br><br>Using the MERGE WHEN MATCHED ON SOURCE THEN DELETE:<br>=C2=A0 =C2=
=A0 Planning Time: 0.994 ms<br>=C2=A0 =C2=A0 Execution Time: 65.695 ms<br><=
br>My questions are:<br><br>- is the Seq Scan expected by others in this ma=
iling list as well?<br>- Is it required to do the Seq Scan?<br>- is this so=
mething that could be optimized?<br><br>Kind regards,<br><br>Feike Steenber=
gen</div>

--00000000000087a372062b45148d--

--00000000000087a373062b45148f
Content-Type: application/octet-stream;
	name="merge_delete_source_seq_scan_reproduction.sql.s"
Content-Disposition: attachment;
	filename="merge_delete_source_seq_scan_reproduction.sql.s"
Content-Transfer-Encoding: base64
Content-ID: <f_m5paw8m10>
X-Attachment-Id: f_m5paw8m10

Q1JFQVRFIFRBQkxFIG1lcmdlX3NvdXJjZSAoCiAgICB0aW1lIHRpbWVzdGFtcHR6IG5vdCBudWxs
LAogICAgdmFsdWUgZmxvYXQ4IG5vdCBudWxsCik7CgpDUkVBVEUgVEFCTEUgbWVyZ2VfdGFyZ2V0
ICgKICAgIGRldmljZV9pZCBpbnRlZ2VyIG5vdCBudWxsLAogICAgdGltZSB0aW1lc3RhbXB0eiBu
b3QgbnVsbCwKICAgIHZhbHVlIGZsb2F0OCBub3QgbnVsbCwKICAgIHByaW1hcnkga2V5IChkZXZp
Y2VfaWQsIHRpbWUpCik7CgpTRUxFQ1Qgc2V0c2VlZCgwLjEyMzQpOwpJTlNFUlQgSU5UTyBtZXJn
ZV90YXJnZXQKU0VMRUNUCiAgICBkZXZpY2VfaWQsCiAgICAnMjAyNS0wMS0wOVQwMDowMDowMCsw
MCc6OnRpbWVzdGFtcHR6IC0gcmFuZG9tKCkgKiBpbnRlcnZhbCAnMSB5ZWFyJywKICAgIHJhbmRv
bSgpICogMTAwMDAKRlJPTQogICAgZ2VuZXJhdGVfc2VyaWVzKDEsMTAwMCkgQVMgZChkZXZpY2Vf
aWQpCkNST1NTIEpPSU4KICAgIGdlbmVyYXRlX3NlcmllcygxLCAxMDApIEFTIF87CgpJTlNFUlQg
SU5UTwogICAgbWVyZ2Vfc291cmNlClNFTEVDVAogICAgJzIwMjUtMDEtMDlUMDA6MDA6MDArMDAn
Ojp0aW1lc3RhbXB0eiAtIHJhbmRvbSgpICogaW50ZXJ2YWwgJzEgeWVhcicsCiAgICByYW5kb20o
KSAqIDEwMDAwCkZST00KICAgIGdlbmVyYXRlX3NlcmllcygxLDEwKTsKClZBQ1VVTSAoQU5BTFla
RSkgbWVyZ2VfdGFyZ2V0OwpWQUNVVU0gKEFOQUxZWkUpIG1lcmdlX3NvdXJjZTsKClBSRVBBUkUg
ZXhhbXBsZV9tZXJnZSBBUwpNRVJHRSBJTlRPCiAgICBtZXJnZV90YXJnZXQgQVMgdApVU0lORwog
ICAgbWVyZ2Vfc291cmNlIEFTIHMgT04gKHQudGltZSA9IHMudGltZSBBTkQgdC5kZXZpY2VfaWQg
PSAkMSkKV0hFTiBNQVRDSEVEIFRIRU4KICAgIFVQREFURSBzZXQKICAgICAgICB2YWx1ZSA9IHMu
dmFsdWUKV0hFTiBOT1QgTUFUQ0hFRCBUSEVOCiAgICBJTlNFUlQgKGRldmljZV9pZCwgdGltZSwg
dmFsdWUpIFZBTFVFUyAoJDEsIHRpbWUsIHZhbHVlKQpXSEVOIE5PVCBNQVRDSEVEIEJZIFNPVVJD
RQogICAgQU5EIHQuZGV2aWNlX2lkID0gJDEKICAgIFRIRU4gREVMRVRFOwoKLS0gVGhpcyBwbGFu
IHNlZW1zIHRvIHVzZSBhIFNlcSBTY2FuLCBhbmQgYXBwbGllcyB0aGUgZmlsdGVyCi0tIGR1cmlu
ZyB0aGUgam9pbjoKCi8qCgpIYXNoIENvbmQ6ICh0LiJ0aW1lIiA9IHMuInRpbWUiKQpKb2luIEZp
bHRlcjogKHQuZGV2aWNlX2lkID0gMTgpCi0+ICBTZXEgU2NhbiBvbiBtZXJnZV90YXJnZXQgdCAg
KGNvc3Q9MC4wMC4uMTYzNy4wMCByb3dzPTEwMDAwMCB3aWR0aD0xOCkKCiovCkVYUExBSU4gRVhF
Q1VURSBleGFtcGxlX21lcmdlKDE4KTsKCi0tIFdoZXJlYXMgSSB3b3VsZCBleHBlY3QgdGhlIHF1
ZXJ5IHRvIG5vdCB1c2UgYSBTZXEgU2NhbiBoZXJlLAotLSBidXQgYW4gaW5kZXggc2NhbiAvIGJp
dG1hcCBpbmRleCBzY2FuLAotLSBzaW1pbGFyIHRvIHdoYXQgd291bGQgaGFwcGVuIGlmIHdlIHJ1
bgotLSB0aGlzIHF1ZXJ5OgpFWFBMQUlOIFNFTEVDVCAqIEZST00gbWVyZ2VfdGFyZ2V0IFdIRVJF
IGRldmljZV9pZCA9IDE4OwoKUFJFUEFSRSBvbGRfZmFzdGVyX3BsYW4gQVMKV0lUSCBkZWxldGVk
IEFTICgKICAgIERFTEVURSBGUk9NCiAgICAgICAgbWVyZ2VfdGFyZ2V0IHQKICAgIFdIRVJFCiAg
ICAgICAgdC5kZXZpY2VfaWQgPSAkMQogICAgICAgIEFORCBOT1QgRVhJU1RTICgKICAgICAgICAg
ICAgU0VMRUNUCiAgICAgICAgICAgIEZST00KICAgICAgICAgICAgICAgIG1lcmdlX3NvdXJjZSBz
CiAgICAgICAgICAgIFdIRVJFCiAgICAgICAgICAgICAgICB0LnRpbWUgPSBzLnRpbWUKICAgICAg
ICAgICAgKQopCk1FUkdFIElOVE8KICAgIG1lcmdlX3RhcmdldCBBUyB0ClVTSU5HCiAgICBtZXJn
ZV9zb3VyY2UgQVMgcyBPTiAodC50aW1lID0gcy50aW1lIEFORCB0LmRldmljZV9pZCA9ICQxKQpX
SEVOIE1BVENIRUQgVEhFTgogICAgVVBEQVRFIHNldAogICAgICAgIHZhbHVlID0gcy52YWx1ZQpX
SEVOIE5PVCBNQVRDSEVEIFRIRU4KICAgIElOU0VSVCAoZGV2aWNlX2lkLCB0aW1lLCB2YWx1ZSkg
VkFMVUVTICgkMSwgdGltZSwgdmFsdWUpOwoKRVhQTEFJTiAoQU5BTFlaRSwgQlVGRkVSUywgTUVN
T1JZKSBFWEVDVVRFIG9sZF9mYXN0ZXJfcGxhbigxOSk7CkVYUExBSU4gKEFOQUxZWkUsIEJVRkZF
UlMsIE1FTU9SWSkgRVhFQ1VURSBleGFtcGxlX21lcmdlKDE4KTsK
--00000000000087a373062b45148f--