Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE

[Feike Steenbergen <feikesteenbergen@xxxxxxxxx>]

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

Attachment: merge_delete_source_seq_scan_reproduction.sql.s
Description: Binary data