Hi Baji, On Thu, Oct 22, 2026 at 5:43 AM Baji Shaik <[email protected]> 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
