MIME-Version: 1.0
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>
In-Reply-To: <d9620380-9e84-4256-8593-d3830495dc81@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 8 Jan 2026 17:14:55 +1300
Message-ID: 
 <CAApHDvoGwHEx1cFQyy-p_ExDMbkYZQFxE4h0qt1ubkd=CHrioA@mail.gmail.com>
Subject: Re: Unexpected planner choice in simple JOIN
To: Mark Kirkwood <mark.kirkwood@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvoGwHEx1cFQyy-p_ExDMbkYZQFxE4h0qt1ubkd%3DCHrioA%40mail.gmail.com>
Precedence: bulk

On Thu, 8 Jan 2026 at 17:03, Mark Kirkwood <mark.kirkwood@gmail.com> wrote:
>
> I don't think so - while the case I posted used a hash index on the
> child table, exactly the sane behaviour happens if it is a btree (I
> probably should have mentioned that sorry). Background is I discovered
> this while playing about with hash indexes...which I must say - someone
> has done excellent work on as in this *particular cases* they are
> getting me better query performance!

Ok, it seems related to the min_parallel_index_scan_size GUC. If you
zero that, do you get a better plan?

I think the problem is that because the best form of plan for joining
this tiny set of rows to the huge table is a parameterised nested
loop, to parallelise that loop, you need a Parallel node on the outer
side of the Nested Loop. If the index's size is below
min_parallel_index_scan_size then we won't build a partial path for
it.

David