Re: MERGE INTO... WHEN NOT MATCHED BY SOURCE index usage

On Mon, 23 Feb 2026 at 15:19, Lea Führer <lea(at)codecat(dot)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_KH7kn85arfqkdzvs80GOCCKvz9YbU2%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 = $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