merge_delete_source_seq_scan_reproduction.sql.s

CREATE TABLE merge_source (
    time timestamptz not null,
    value float8 not null
);

CREATE TABLE merge_target (
    device_id integer not null,
    time timestamptz not null,
    value float8 not null,
    primary key (device_id, time)
);

SELECT setseed(0.1234);
INSERT INTO merge_target
SELECT
    device_id,
    '2025-01-09T00:00:00+00'::timestamptz - random() * interval '1 year',
    random() * 10000
FROM
    generate_series(1,1000) AS d(device_id)
CROSS JOIN
    generate_series(1, 100) AS _;

INSERT INTO
    merge_source
SELECT
    '2025-01-09T00:00:00+00'::timestamptz - random() * interval '1 year',
    random() * 10000
FROM
    generate_series(1,10);

VACUUM (ANALYZE) merge_target;
VACUUM (ANALYZE) merge_source;

PREPARE example_merge AS
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;

-- This plan seems to use a Seq Scan, and applies the filter
-- during the join:

/*

Hash Cond: (t."time" = s."time")
Join Filter: (t.device_id = 18)
->  Seq Scan on merge_target t  (cost=0.00..1637.00 rows=100000 width=18)

*/
EXPLAIN EXECUTE example_merge(18);

-- Whereas I would expect the query to not use a Seq Scan here,
-- but an index scan / bitmap index scan,
-- similar to what would happen if we run
-- this query:
EXPLAIN SELECT * FROM merge_target WHERE device_id = 18;

PREPARE old_faster_plan AS
WITH deleted AS (
    DELETE FROM
        merge_target t
    WHERE
        t.device_id = $1
        AND NOT EXISTS (
            SELECT
            FROM
                merge_source s
            WHERE
                t.time = s.time
            )
)
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);

EXPLAIN (ANALYZE, BUFFERS, MEMORY) EXECUTE old_faster_plan(19);
EXPLAIN (ANALYZE, BUFFERS, MEMORY) EXECUTE example_merge(18);