public inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Mullineux <dmullx@gmail.com>
Cc: Feike Steenbergen <feikesteenbergen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
Date: Thu, 09 Jan 2025 14:39:53 -0500
Message-ID: <1903318.1736451593@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>
References: <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
	<CAGsyd8UeMRtmgrQZ1Tg7gMztr=pvF4Dp8mp0kGHzc9ct04FQhw@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dmullx@gmail.com, feikesteenbergen@gmail.com
  Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
  In-Reply-To: <1903318.1736451593@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox