From: Tom Lane <tgl@sss.pgh.pa.us>
To: hu_yajun@qq.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18442: Unnecessary Sort operator in indexScan Plan
In-reply-to: <18442-aca4c9134416990e@postgresql.org>
References: <18442-aca4c9134416990e@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 19 Apr 2024 03:10:24 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2135176.1713535423.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 19 Apr 2024 10:03:43 -0400
Message-ID: <2135177.1713535423@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2135177.1713535423%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> create index on t(a,b);
> postgres=3D# explain select * from t where a is null order by b; -- need
> sort
>                                    QUERY PLAN
> ------------------------------------------------------------------------=
--------
>  Sort  (cost=3D9.54..9.80 rows=3D103 width=3D8)
>    Sort Key: b
>    ->  Index Only Scan using t_a_b_idx on t  (cost=3D0.29..6.10 rows=3D1=
03
> width=3D8)
>          Index Cond: (a IS NULL)
> (4 rows)

Postgres doesn't detect that it could do this because "a IS NULL"
is not an equivalence condition.  You're not the first to suggest
that it could be treated as one, but I believe there are semantic
difficulties that would ensue.  One example is that given
"a IS NULL" and "a =3D b", the EquivalenceClass machinery would
think it can discard "a =3D b" and instead emit "b IS NULL",
which would not give the same answers.

			regards, tom lane