Message-ID: <4d33efcf76aa2a7d48c90c74e1889f4a571a787e.camel@cybertec.at>
Subject: Re: BUG #18935: The optimiser's choice of sort doubles the
 execution time.
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: xiaohongjun@stu.xidian.edu.cn, pgsql-bugs@lists.postgresql.org
Date: Mon, 19 May 2025 17:23:58 +0200
In-Reply-To: <18935-afc77176f41fbecb@postgresql.org>
References: <18935-afc77176f41fbecb@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/4d33efcf76aa2a7d48c90c74e1889f4a571a787e.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-05-19 at 12:42 +0000, PG Bug reporting form wrote:
> 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;
>
> [explicitly disabling sort leads to a faster execution plan]

I see no bug here.
The join condition is so complicated that the planner has a hard
time estimating the number of result rows for the join.
It guesses way to low, so the sort is expensive, and avoiding it is cheaper=
.

The optimizer cannot be perfect, particularly in the face of complicated
expressions like that.

Yours,
Laurenz Albe