MIME-Version: 1.0
References: 
 <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
 <CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>
 <1903318.1736451593@sss.pgh.pa.us>
In-Reply-To: <1903318.1736451593@sss.pgh.pa.us>
From: Feike Steenbergen <feikesteenbergen@gmail.com>
Date: Mon, 27 Jan 2025 10:48:52 +0100
Message-ID: 
 <CAK_s-G0REudTC3AE3BokocKM5jSgwPP-+7FFDw32Pp3JdFRU7A@mail.gmail.com>
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Mullineux <dmullx@gmail.com>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000093791e062cacfc92"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK_s-G0REudTC3AE3BokocKM5jSgwPP-%2B7FFDw32Pp3JdFRU7A%40mail.gmail.com>
Precedence: bulk

--00000000000093791e062cacfc92
Content-Type: text/plain; charset="UTF-8"

On Thu, 9 Jan 2025 at 20:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Yeah. That prevents pushing down the join condition, since in a FULL join
all
> rows of both sides will contribute to the result; none can be removed
ahead of
> the join.

> Not sure if this pattern is common enough to be worth trying to implement
such
> an optimization.

I'm not going to pursue this much further, however for those reading along/
future reference, what we're trying to do is:

- for a given target table
- merge a subset of the data using a source table (only update any
significant
changes). The subset filter `WHERE device_id=$1` is applied to both the JOIN
as well as the NOT MATCHED BY SOURCE part of the merge.
- when using MERGE ... NOT MATCHED BY SOURCE THEN DELETE currently (pg17)
reads
the whole of the target table before applying the subset filter. As we plan
to
merge only very small subsets (1/10,000 or so), this means that for now,
this
isn't usable for that use case.

For example, for a monitoring system, we have 10's of thousands of remote
systems, for which we want to merge a snapshot of their current state
inside a
central target table. That use case is currently not well supported with
MERGE.
(The workaround is a DELETE inside a CTE).

I would expect this use case to be quite common, however, as this feature is
only available in PG17, it may not be used yet with MERGE, so I don't expect
any others to voice the same concern.

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

<div dir=3D"ltr"><br><br>On Thu, 9 Jan 2025 at 20:39, Tom Lane &lt;<a href=
=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br><br>&gt; =
Yeah. That prevents pushing down the join condition, since in a FULL join a=
ll<br>&gt; rows of both sides will contribute to the result; none can be re=
moved ahead of<br>&gt; the join.<br><br>&gt; Not sure if this pattern is co=
mmon enough to be worth trying to implement such<br>&gt; an optimization.<b=
r><br>I&#39;m not going to pursue this much further, however for those read=
ing along/<br>future reference, what we&#39;re trying to do is:<br><br>- fo=
r a given target table<br>- merge a subset of the data using a source table=
 (only update any significant<br>changes). The subset filter `WHERE device_=
id=3D$1` is applied to both the JOIN<br>as well as the NOT MATCHED BY SOURC=
E part of the merge.<br>- when using MERGE ... NOT MATCHED BY SOURCE THEN D=
ELETE currently (pg17) reads<br>the whole of the target table before applyi=
ng the subset filter. As we plan to<br>merge only very small subsets (1/10,=
000 or so), this means that for now, this<br>isn&#39;t usable for that use =
case.<br><br>For example, for a monitoring system, we have 10&#39;s of thou=
sands of remote<br>systems, for which we want to merge a snapshot of their =
current state inside a<br>central target table. That use case is currently =
not well supported with MERGE.<br>(The workaround is a DELETE inside a CTE)=
.<br><br>I would expect this use case to be quite common, however, as this =
feature is<br>only available in PG17, it may not be used yet with MERGE, so=
 I don&#39;t expect<br>any others to voice the same concern.<br><br></div>

--00000000000093791e062cacfc92--