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
merge_delete_source_seq_scan_reproduction.sql.s
Description: Binary data
