Re: BUG #18442: Unnecessary Sort operator in indexScan Plan - Mailing list pgsql-bugs

PG Bug reporting form <noreply@postgresql.org> writes:
> create index on t(a,b);
> postgres=# explain select * from t where a is null order by b; -- need
> sort
>                                    QUERY PLAN
> --------------------------------------------------------------------------------
>  Sort  (cost=9.54..9.80 rows=103 width=8)
>    Sort Key: b
>    ->  Index Only Scan using t_a_b_idx on t  (cost=0.29..6.10 rows=103
> width=8)
>          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 = b", the EquivalenceClass machinery would
think it can discard "a = b" and instead emit "b IS NULL",
which would not give the same answers.

            regards, tom lane