From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: dqetool@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18764: server closed the connection unexpectedly
In-reply-to: 
 <CAApHDvo8FfuOiXYnJ1n2fZh8nwrDwkSU7=k6So+u-pYDOkN4YQ@mail.gmail.com>
References: <18764-63ad667ea26e877a@postgresql.org>
 <446342.1735923417@sss.pgh.pa.us> <468120.1735927103@sss.pgh.pa.us>
 <CAApHDvo8FfuOiXYnJ1n2fZh8nwrDwkSU7=k6So+u-pYDOkN4YQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 09 Jan 2025 16:30:43 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1795555.1736396078.1@sss.pgh.pa.us>
Date: Wed, 08 Jan 2025 23:14:38 -0500
Message-ID: <1795556.1736396078@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1795556.1736396078%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 4 Jan 2025 at 06:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There is no value in forcing a sort of the subquery's output,
>> and the previous code didn't do so:

> The reason for the 2nd sort, (ignoring the invalidity of the pathkeys)
> is due to how build_setop_child_paths() tries sorting the
> cheapest_input_path.  The problem is that the sorted path gets added
> to the same RelOptInfo as the cheapest_input_path so if add_path()
> sees they're fuzzily the same cost, the sorted has better pathkeys, so
> the actual cheapest path is thrown away. I think the reason this
> happens in this case is that there are just not that many rows to
> sort, and it's fairly expensive to get those rows.

Yeah, same conclusion Richard and I came to.  It's not that big a
problem (aside from the validity issue) because this path would only
win if the sorting cost is estimated to be insignificant relative
to the subquery's other costs.  So while it might be nice to improve
that in future, I'm not very concerned about it now.

			regards, tom lane