Re: Unexpected planner choice in simple JOIN - Mailing list pgsql-performance

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)
>
>