Re: Fix rescan costing of single-batch hash joins

Hi Baji,

On Thu, Oct 22, 2026 at 5:43 AM Baji Shaik <baji(dot)pgdev(at)gmail(dot)com> wrote:
> While working on a performance issue, I traced a slow plan choice to how cost_rescan() costs a hash join on the inner side of a nested loop.
>
> For a single-batch hash join, cost_rescan() assumes the startup cost "is exactly the cost of hash table building" and charges nothing for it on rescan. But that startup cost also includes the outer path's startup cost, and ExecReScanHashJoin() reruns the outer side on every rescan. Also, the hash table is only reused when the inner side has no changed parameters, which the costing doesn't check. So when the hash join's input is a LATERAL subquery with ORDER BY ... LIMIT, the sort that runs on every loop is costed as if it ran once.

I agree that the cost estimation for the hash-join case does not match
what is done by ExecReScanHashJoin().

> The patch charges the outer path's own rescan cost on each rescan instead of nothing, and charges the full cost when the inner path is parameterized, since the hash table is then rebuilt every loop. It adds a test in join.sql that changes plan without the fix. make check, contrib and isolation tests pass, and no existing regression plans change. As a costing change it's for master only.

I agree that this is for master-only as well. Could you add it to the
upcoming commitfest. I'd like to review it next month.

Thanks for the report and patch!

Best regards,
Etsuro Fujita