From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Mullineux <dmullx@gmail.com>
cc: Feike Steenbergen <feikesteenbergen@gmail.com>,
        pgsql-performance@postgresql.org
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
In-reply-to: 
 <CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>
References: 
 <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
 <CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>
Comments: In-reply-to David Mullineux <dmullx@gmail.com>
	message dated "Thu, 09 Jan 2025 15:52:45 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1903317.1736451593.1@sss.pgh.pa.us>
Date: Thu, 09 Jan 2025 14:39:53 -0500
Message-ID: <1903318.1736451593@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1903318.1736451593%40sss.pgh.pa.us>
Precedence: bulk

David Mullineux <dmullx@gmail.com> writes:
> On Thu, 9 Jan 2025, 12:26 Feike Steenbergen, <feikesteenbergen@gmail.com>
> wrote:
>> 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.

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

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.

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.

			regards, tom lane