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)
