public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Feike Steenbergen <feikesteenbergen@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Unexpected Seq Scan's when using MERGE WHEN NOT MATCHED BY SOURCE
Date: Thu, 9 Jan 2025 13:39:13 +0100
Message-ID: <CAK_s-G3+2NnjYxixwBq2xgoJS8iBi6=4JDJzWDdg8oJZ0_vUZA@mail.gmail.com> (raw)
In-Reply-To: <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
References: <CAK_s-G0NrC_KH7kn85arfqkdzvs80GOCCKvz9YbU2=E94qfdPA@mail.gmail.com>
As a followup, these are the `EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)`
plans for the previous version of the query and the one using NOT MATCHED
BY SOURCE.
Previous plan: Buffers: shared hit=84 dirtied=1 written=1
New plan: Buffers: shared hit=100690
*Old plan, using delete in CTE, then MERGE*
Merge on merge_target t (actual time=0.117..0.119 rows=0 loops=1)
Tuples: inserted=10
Buffers: shared hit=84 dirtied=1 written=1
I/O Timings: shared write=0.012
CTE deleted
-> Delete on merge_target t_1 (actual time=0.222..0.222 rows=0
loops=1)
Buffers: shared hit=306
-> Hash Anti Join (actual time=0.034..0.187 rows=100 loops=1)
Hash Cond: (t_1."time" = s_1."time")
Buffers: shared hit=106
-> Bitmap Heap Scan on merge_target t_1 (actual
time=0.021..0.163 rows=100 loops=1)
Recheck Cond: (device_id = 19)
Heap Blocks: exact=102
Buffers: shared hit=105
-> Bitmap Index Scan on merge_target_pkey (actual
time=0.010..0.010 rows=110 loops=1)
Index Cond: (device_id = 19)
Buffers: shared hit=3
-> Hash (actual time=0.006..0.006 rows=10 loops=1)
Buffers: shared hit=1
-> Seq Scan on merge_source s_1 (actual
time=0.001..0.002 rows=10 loops=1)
Buffers: shared hit=1
-> Nested Loop Left Join (actual time=0.020..0.031 rows=10 loops=1)
Buffers: shared hit=31
-> Seq Scan on merge_source s (actual time=0.006..0.007 rows=10
loops=1)
Buffers: shared hit=1
-> Index Scan using merge_target_pkey on merge_target t (actual
time=0.002..0.002 rows=0 loops=10)
Index Cond: ((device_id = 19) AND ("time" = s."time"))
Buffers: shared hit=30
Planning:
Memory: used=80kB allocated=144kB
Planning Time: 0.189 ms
Execution Time: 0.412 ms
*New plan, using MERGE WHEN NOT MATCHED BY SOURCE AND device_id=$1*
Merge on merge_target t (actual rows=0 loops=1)
Tuples: inserted=10 deleted=100 skipped=99810
Buffers: shared hit=100690
-> Hash Full Join (actual rows=99920 loops=1)
Hash Cond: (t."time" = s."time")
Join Filter: (t.device_id = 18)
Rows Removed by Join Filter: 10
Buffers: shared hit=639
-> Seq Scan on merge_target t (actual rows=99910 loops=1)
Buffers: shared hit=638
-> Hash (actual rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on merge_source s (actual rows=10 loops=1)
Buffers: shared hit=1
Planning:
Memory: used=41kB allocated=80kB
Planning Time: 0.642 ms
Execution Time: 55.213 ms
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-G3+2NnjYxixwBq2xgoJS8iBi6=4JDJzWDdg8oJZ0_vUZA@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