From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Fixing some ancient errors in hash join costing
In-reply-to: <2380165.1766871097@sss.pgh.pa.us>
References: <2380165.1766871097@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Sat, 27 Dec 2025 16:31:37 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2551252.1766952955.1@sss.pgh.pa.us>
Date: Sun, 28 Dec 2025 15:15:56 -0500
Message-ID: <2551253.1766952956@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2551253.1766952956%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> I remain a bit confused by the change in postgres_fdw.out though.
> It's deciding to push an ORDER BY down to the remote side when
> it didn't before, which seems like an improvement; but I fail to
> see how a marginal change in hash join costing would lead to that.
> Perhaps that is worth looking into more closely.

I dug into that bit today, and concluded that it's a "nothing to
see here" case.  We are comparing the costs of doing a sort step
locally vs remotely --- but if the remote server is identically
configured, which it surely is in this test, then cost_sort()
will produce the same answers on both sides, and we are comparing
path costs that are the same to within roundoff error and
cost-quantization effects.  My patch does move the underlying
semijoin's cost just a hair, and that results in changes in what
add_path does with the locally-sorted versus remotely-sorted paths,
but really there's no reason to prefer one over the other.

I was amused to notice that the postgres_fdw.out change made in my
patch reverts one made in aa86129e1 (which also affected semijoin
costing).  So we've had trouble before with that test case being
fundamentally unstable.  I wonder if we shouldn't do something to try
to stabilize it?  I see that the test immediately before this one
forces the matter by turning off enable_sort (which'd affect only
the local side not the remote).  That's a hack all right but maybe
we should extend it to this test.

			regards, tom lane