public inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Feike Steenbergen <feikesteenbergen@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
Date: Thu, 9 Jan 2025 13:25:45 +0100
Message-ID: <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com> (raw)

I'm trying to change a few applications to fully use this, as PostgreSQL 17
added this support.

The application does something like this:

- fetch information from a source system and store it in a temp table
- run a MERGE with a table (target) in this database,
  updating, inserting and deleting in a single statement
- the target table holds information for multiple systems

The temp table (source) doesn't contain the identifier for the system, as
we can inject that at runtime.

This is the shape of the statement:

    MERGE INTO
        merge_target AS t
    USING
        merge_source AS s ON (t.time = s.time AND t.device_id = $1)
    WHEN MATCHED THEN
        UPDATE set
            value = s.value
    WHEN NOT MATCHED THEN
        INSERT (device_id, time, value) VALUES ($1, time, value)
    WHEN NOT MATCHED BY SOURCE
        AND t.device_id = $1
        THEN DELETE;

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.

Previously we would - in a CTE - do a delete *and then* the merge.

Which performed much better as the DELETE would not do a Seq Scan.

Attached a full reproducing test case. Some numbers:

Previously, DELETE in CTE, then merge:
    Planning Time: 6.700 ms
    Execution Time: 7.473 ms

Using the MERGE WHEN MATCHED ON SOURCE THEN DELETE:
    Planning Time: 0.994 ms
    Execution Time: 65.695 ms

My questions are:

- is the Seq Scan expected by others in this mailing list as well?
- Is it required to do the Seq Scan?
- is this something that could be optimized?

Kind regards,

Feike Steenbergen


Attachments:

  [application/octet-stream] merge_delete_source_seq_scan_reproduction.sql.s (2.2K, 3-merge_delete_source_seq_scan_reproduction.sql.s)
  download

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: feikesteenbergen@gmail.com
  Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
  In-Reply-To: <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>

* 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