Message-ID: <02d73d5c-bfd8-4d88-9c5d-2f298bcffa78@gmail.com>
Date: Thu, 8 Jan 2026 17:44:25 +1300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Unexpected planner choice in simple JOIN
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <dgrowleyml@gmail.com>,
 pgsql-performance@lists.postgresql.org
References: <71afdb09-0583-4af2-a3b3-50da4f987b9b@gmail.com>
 <f16e6fd6-7b7e-4c09-b26e-d7979ef86d2e@gmail.com>
 <CAApHDvqLR27kZkK5zaK2-6LQo_RB_wnVg8DHfHj17cTbr+RHtQ@mail.gmail.com>
 <d9620380-9e84-4256-8593-d3830495dc81@gmail.com>
 <CAApHDvoGwHEx1cFQyy-p_ExDMbkYZQFxE4h0qt1ubkd=CHrioA@mail.gmail.com>
 <a91f8d2e-64d2-4074-96d2-744a9a604f4e@gmail.com>
 <1895375.1767846864@sss.pgh.pa.us>
Content-Language: en-US
From: Mark Kirkwood <mark.kirkwood@gmail.com>
In-Reply-To: <1895375.1767846864@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/02d73d5c-bfd8-4d88-9c5d-2f298bcffa78%40gmail.com>
Precedence: bulk

Yeah, that seems to do the trick:

test0=# SET parallel_setup_cost=24000;
SET
Time: 0.150 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.84..64355.16 rows=5000 width=98) (actual 
time=0.038..0.819 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.003..0.005 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.55..12837.19 rows=3297 
width=98) (actual time=0.023..0.111 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.73 
rows=3297 width=0) (actual time=0.018..0.018 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.168 ms
  Execution Time: 0.935 ms
(19 rows)

A point comes to mind - this is not a particularly unusual setup (i.e 
relatively small parent table with big child one), so maybe the defaults 
are not ideal here?

Anyway,, thanks for your help!

regards

Mark

On 08/01/2026 17:34, Tom Lane wrote:
> Mark Kirkwood <mark.kirkwood@gmail.com> writes:
>> Good suggestion. The results are...interesting:
> It's clear that the planner is underestimating the costs of using a
> parallel worker in your setup.  Try increasing parallel_setup_cost
> and/or parallel_tuple_cost to bring things more in line with reality.
>
> 			regards, tom lane