From: Tom Lane <tgl@sss.pgh.pa.us>
To: xiaohongjun@stu.xidian.edu.cn
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18935: The optimiser's choice of sort doubles the execution
 time.
In-reply-to: <18935-afc77176f41fbecb@postgresql.org>
References: <18935-afc77176f41fbecb@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 19 May 2025 12:42:13 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <409751.1747668087.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 19 May 2025 11:21:27 -0400
Message-ID: <409752.1747668087@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/409752.1747668087%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> database4=3D# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
> ((t1.c0)=3D(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range))=
)-(range_merge(t1.c0,
> t0.c0))))))) GROUP BY t0.c0;

[ planner incorrectly prefers sort/group over hashed grouping ]

I don't think there's much to be done about this.  The core of the
problem is that the estimate of the number of rows coming into the
grouping step is off by more than two orders of magnitude:

>          ->  Nested Loop  (cost=3D0.00..363.13 rows=3D70 width=3D13) (ac=
tual
> time=3D0.055..8.431 rows=3D12688 loops=3D1)

There's little point in complaining that the cost of the grouping
is off by a factor of two when there's such a large error in its
input information.

So the main thing that would have to be done is improving the
selectivity estimate for

>                Join Filter: (t1.c0 =3D (t1.c0 - ((t1.c0 *
> '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))

If this condition weren't so obviously random junk generated by
a fuzzer, maybe people would be motivated to try to improve that.
But as it stands, there's neither a clear path to improving it
nor a lot of motivation to try.

			regards, tom lane