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: David Mullineux <dmullx@gmail.com>
Date: Thu, 9 Jan 2025 15:52:45 +0000
Message-ID: 
 <CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
To: Feike Steenbergen <feikesteenbergen@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b2b31d062b47f84d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGsyd8UeMRtmgrQZ1Tg7gMztr%3DpvF4Dp8mp0kGHzc9ct04FQhw%40mail.gmail.com>
Precedence: bulk

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

On Thu, 9 Jan 2025, 12:26 Feike Steenbergen, <feikesteenbergen@gmail.com>
wrote:

> 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
>

An excellent post. I wish all posters provided nice contained example cases
like this one.

I note ,in the documentation, that a Warning box got added which says
this...

If both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY TARGET] clauses
are specified, the MERGE command will perform a FULL join between
data_source and the target table. For this to work, at least one
join_condition subexpression must use an operator that can support a hash
join, or all of the subexpressions must use operators that can support a
merge join.


This could be a hint as to the reason maybe ? The NOT MATCHED BY SOURCE is
new feature to 17. I'm looking forward to others replies here.

>

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote gmail_quote_contai=
ner"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 9 Jan 2025, 12:26 Feike =
Steenbergen, &lt;<a href=3D"mailto:feikesteenbergen@gmail.com">feikesteenbe=
rgen@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div =
dir=3D"ltr">I&#39;m trying to change a few applications to fully use this, =
as PostgreSQL 17 added this support.<br><br>The application does something =
like this:<br><br>- fetch information from a source system and store it 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 targ=
et table holds information for multiple systems<br><br>The temp table (sour=
ce) doesn&#39;t contain the identifier for the system, as<br>we can inject =
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.tim=
e =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, time, =
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 DELETE;<b=
r><br>If we run this however, there is always a Seq Scan against merge_targ=
et,<br>whereas the filter of `AND t.device_id =3D $1` uses a (Bitmap) Index=
 scan<br>in other types of queries.<br><br>Previously we would - in a CTE -=
 do a delete *and then* the merge.<br><br>Which performed much better as th=
e DELETE would not do a Seq Scan.<br><br>Attached a full reproducing test c=
ase. 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 ms<br=
><br>Using the MERGE WHEN MATCHED ON SOURCE THEN DELETE:<br>=C2=A0 =C2=A0 P=
lanning 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 mailing=
 list as well?<br>- Is it required to do the Seq Scan?<br>- is this somethi=
ng that could be optimized?<br><br>Kind regards,<br><br>Feike Steenbergen</=
div></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"auto">=
An excellent post. I wish all posters provided nice contained example cases=
 like this one.</div><div dir=3D"auto"><br></div><div dir=3D"auto">I note ,=
in the documentation, that a Warning box got added which says this...</div>=
<div dir=3D"auto"><br></div><div dir=3D"auto">If both WHEN NOT MATCHED BY S=
OURCE and WHEN NOT MATCHED [BY TARGET] clauses are specified, the MERGE com=
mand will perform a FULL join between data_source and the target table. For=
 this to work, at least one join_condition subexpression must use an operat=
or that can support a hash join, or all of the subexpressions must use oper=
ators that can support a merge join.</div><div dir=3D"auto"><br></div><div =
dir=3D"auto"><br></div><div dir=3D"auto">This could be a hint as to the rea=
son maybe ? The NOT MATCHED BY SOURCE is new feature to 17. I&#39;m looking=
 forward to others replies here.</div><div dir=3D"auto"><div class=3D"gmail=
_quote gmail_quote_container"><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--000000000000b2b31d062b47f84d--