MIME-Version: 1.0
References: <e16bade2-8942-4027-a1b2-7fea058cecf7@codecat.at>
In-Reply-To: <e16bade2-8942-4027-a1b2-7fea058cecf7@codecat.at>
From: Feike Steenbergen <feikesteenbergen@gmail.com>
Date: Wed, 25 Feb 2026 17:35:34 +0100
Message-ID: 
 <CAK_s-G3MKiLACGZqdrg7ZeGYjWPJGXJUSq5erhb4GsXHHy-SuA@mail.gmail.com>
Subject: Re: MERGE INTO... WHEN NOT MATCHED BY SOURCE index usage
To: =?UTF-8?Q?Lea_F=C3=BChrer?= <lea@codecat.at>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008622c7064ba898bb"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK_s-G3MKiLACGZqdrg7ZeGYjWPJGXJUSq5erhb4GsXHHy-SuA%40mail.gmail.com>
Precedence: bulk

--0000000000008622c7064ba898bb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, 23 Feb 2026 at 15:19, Lea F=C3=BChrer <lea@codecat.at> wrote:
> It seems to me like one very common use-case, and one I have bumped into
> often, is to update a n:m resolution table, like this for example:

Something similar was previously discussed in January 2025 as well:

https://www.postgresql.org/message-id/flat/CAK_s-G0NrC_KH7kn85arfqkdzvs80GO=
CCKvz9YbU2%3DE94qfdPA%40mail.gmail.com

To summarize:

Tom Lane wrote:

> I may not have fully wrapped my head around this example, but I think
> that the fact that "t.device_id =3D $1" appears in both the ON condition
> and the WHEN NOT MATCHED BY SOURCE condition means that only t rows
> meeting that condition are of interest, so that in principle we could
> optimize by pushing that down to the scan of t.  But as you can see,
> we don't.  Not sure if this pattern is common enough to be worth
> trying to implement such an optimization.

So at least there's 1 more who uses this pattern!

Kind regards

Feike

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

<div dir=3D"ltr">On Mon, 23 Feb 2026 at 15:19, Lea F=C3=BChrer &lt;<a href=
=3D"mailto:lea@codecat.at">lea@codecat.at</a>&gt; wrote:<br>&gt; It seems t=
o me like one very common use-case, and one I have bumped into<br>&gt; ofte=
n, is to update a n:m resolution table, like this for example:<br><br>Somet=
hing similar was previously discussed in January 2025 as well:<br><br><a hr=
ef=3D"https://www.postgresql.org/message-id/flat/CAK_s-G0NrC_KH7kn85arfqkdz=
vs80GOCCKvz9YbU2%3DE94qfdPA%40mail.gmail.com">https://www.postgresql.org/me=
ssage-id/flat/CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2%3DE94qfdPA%40mail.=
gmail.com</a><br><br>To summarize:<br><br>Tom Lane wrote:<br><br>&gt; I may=
 not have fully wrapped my head around this example, but I think<br>&gt; th=
at the fact that &quot;t.device_id =3D $1&quot; appears in both the ON cond=
ition<br>&gt; and the WHEN NOT MATCHED BY SOURCE condition means that only =
t rows<br>&gt; meeting that condition are of interest, so that in principle=
 we could<br>&gt; optimize by pushing that down to the scan of t.=C2=A0 But=
 as you can see,<br>&gt; we don&#39;t.=C2=A0 Not sure if this pattern is co=
mmon enough to be worth<br>&gt; trying to implement such an optimization.<b=
r><br>So at least there&#39;s 1 more who uses this pattern!<br><br>Kind reg=
ards<br><br>Feike</div>

--0000000000008622c7064ba898bb--