public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mark Kirkwood <[email protected]>
To: [email protected]
Subject: Re: Unexpected planner choice in simple JOIN
Date: Thu, 8 Jan 2026 16:34:42 +1300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
This does seem to be related to parallel planning:
test0=# SET max_parallel_workers_per_gather=0;
SET
Time: 0.205 ms
test0=# EXPLAIN ANALYZE SELECT t0.id0, t1.val FROM tab0 AS t0 JOIN tab1
AS t1 ON (t0.id0 = t1.id0) WHERE t0.id0 < 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=97.91..64508.51 rows=5000 width=98) (actual
time=0.217..1.193 rows=3500.00 loops=1)
Buffers: shared hit=113 read=1
-> Index Only Scan using tab0_pkey on tab0 t0 (cost=0.29..4.38
rows=5 width=4) (actual time=0.007..0.010 rows=5.00 loops=1)
Index Cond: (id0 < 5)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3
-> Bitmap Heap Scan on tab1 t1 (cost=97.61..12867.78 rows=3305
width=98) (actual time=0.069..0.187 rows=700.00 loops=5)
Recheck Cond: (t0.id0 = id0)
Heap Blocks: exact=97
Buffers: shared hit=110 read=1
-> Bitmap Index Scan on tab1_id0_hash (cost=0.00..96.79
rows=3305 width=0) (actual time=0.061..0.061 rows=700.00 loops=5)
Index Cond: (id0 = t0.id0)
Index Searches: 5
Buffers: shared hit=13 read=1
Planning:
Buffers: shared hit=216 read=6
Planning Time: 1.618 ms
Execution Time: 1.385 ms
(19 rows)
Not clear to me why removing 2 workers makes the seqscan more attractive
when that part of the plan is 100x more expensive than an index scan....
On 08/01/2026 14:35, Mark Kirkwood wrote:
> I have a 2 table parent child setup (tab0 -< tab1) with a fairly small
> (100 K rows) parent and big child (100 M rows).. Exact setup is
> included below.
>
> If I do a simple range scan on small part of the pk of tab0 the
> planner chooses an index scan (pretty much as expected):
>
> test0=# EXPLAIN ANALYZE SELECT t0.id0 FROM tab0 AS t0 WHERE t0.id0 < 5;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------
>
> Index Only Scan using tab0_pkey on tab0 t0 (cost=0.29..4.38 rows=5
> width=4) (actual time=0.006..0.007 rows=5.00 loops=1)
> Index Cond: (id0 < 5)
> Heap Fetches: 0
> Index Searches: 1
> Buffers: shared hit=3
> Planning:
> Buffers: shared hit=65
> Planning Time: 0.383 ms
> Execution Time: 0.038 ms
> (9 rows)
>
> However joining it to tab1 changes this to a parallel seq scan:
>
> test0=# EXPLAIN ANALYZE SELECT t0.id0, t1.val FROM tab0 AS t0 JOIN
> tab1 AS t1 ON (t0.id0 = t1.id0) WHERE t0.id0 < 5;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------
>
> Gather (cost=1097.61..42577.77 rows=5000 width=98) (actual
> time=0.324..7.486 rows=3500.00 loops=1)
> Workers Planned: 1
> Workers Launched: 1
> Buffers: shared hit=1751
> -> Nested Loop (cost=97.61..41077.77 rows=2941 width=98) (actual
> time=1.273..3.723 rows=1750.00 loops=2)
> Buffers: shared hit=1751
> -> Parallel Seq Scan on tab0 t0 (cost=0.00..2375.29 rows=3
> width=4) (actual time=1.247..3.232 rows=2.50 loops=2)
> Filter: (id0 < 5)
> Rows Removed by Filter: 49998
> Buffers: shared hit=1640
> -> Bitmap Heap Scan on tab1 t1 (cost=97.61..12867.78
> rows=3305 width=98) (actual time=0.031..0.148 rows=700.00 loops=5)
> Recheck Cond: (t0.id0 = id0)
> Heap Blocks: exact=97
> Buffers: shared hit=111
> -> Bitmap Index Scan on tab1_id0_hash
> (cost=0.00..96.79 rows=3305 width=0) (actual time=0.024..0.024
> rows=700.00 loops=5)
> Index Cond: (id0 = t0.id0)
> Index Searches: 5
> Buffers: shared hit=14
> Planning:
> Buffers: shared hit=160
> Planning Time: 0.506 ms
> Execution Time: 7.658 ms
> (22 rows)
>
> However, disabling seq scan gets back to the index scan again, and
> what looks to be a lower cost overall plan:
>
> test0=# SET enable_seqscan=off;
> SET
> Time: 0.133 ms
> test0=# EXPLAIN ANALYZE SELECT t0.id0, t1.val FROM tab0 AS t0 JOIN
> tab1 AS t1 ON (t0.id0 = t1.id0) WHERE t0.id0 < 5;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------
>
> Nested Loop (cost=97.91..64508.51 rows=5000 width=98) (actual
> time=0.044..1.093 rows=3500.00 loops=1)
> Buffers: shared hit=113
> -> Index Only Scan using tab0_pkey on tab0 t0 (cost=0.29..4.38
> rows=5 width=4) (actual time=0.004..0.007 rows=5.00 loops=1)
> Index Cond: (id0 < 5)
> Heap Fetches: 0
> Index Searches: 1
> Buffers: shared hit=3
> -> Bitmap Heap Scan on tab1 t1 (cost=97.61..12867.78 rows=3305
> width=98) (actual time=0.029..0.153 rows=700.00 loops=5)
> Recheck Cond: (t0.id0 = id0)
> Heap Blocks: exact=97
> Buffers: shared hit=110
> -> Bitmap Index Scan on tab1_id0_hash (cost=0.00..96.79
> rows=3305 width=0) (actual time=0.022..0.022 rows=700.00 loops=5)
> Index Cond: (id0 = t0.id0)
> Index Searches: 5
> Buffers: shared hit=13
> Planning:
> Buffers: shared hit=9
> Planning Time: 0.187 ms
> Execution Time: 1.262 ms
> (19 rows)
>
>
view thread (10+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Unexpected planner choice in simple JOIN
In-Reply-To: <[email protected]>
* 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