Re: BUG #19386: Unnecessary Sort in query plan for SELECT literal with ORDER BY

Andrei Lepikhov <lepihov(at)gmail(dot)com> writes:
> Another question - should we do anything to optimise this quite narrow
> (at least it seems so for me) case and stop simplification of the clause?

I don't think so. Shorn of irrelevancies, what we have here is

regression=# create table foo (b bool);
CREATE TABLE
regression=# explain select * from foo where b = true order by b;
QUERY PLAN
-------------------------------------------------------------
Sort (cost=111.56..115.07 rows=1405 width=1)
Sort Key: b
-> Seq Scan on foo (cost=0.00..38.10 rows=1405 width=1)
Filter: b

The planner can omit the sort step if it sees that the sort key has
been equated to a constant:

regression=# create table bar (i int);
CREATE TABLE
regression=# explain select * from bar where i = 42 order by i;
QUERY PLAN
-----------------------------------------------------
Seq Scan on bar (cost=0.00..41.88 rows=13 width=4)
Filter: (i = 42)

However, in the case of a bool column we go out of our way to
convert "b = true" and similar cases to "b" or "NOT b" so that
we won't be fooled by variant phrasings of equivalent conditions.
That doesn't happen in the OP's generic-plan case because the
original is "b = parameter". The planner knows the parameter
expression will be effectively constant in any one run, so it
elides the Sort even though it doesn't know the parameter's
value. But it doesn't see plain "b" as a phrasing of "b = true".

We could, I imagine, allow the EquivalenceClass machinery to treat
"WHERE b" as a shorthand for "b = true", but that would lead to
cluttering the mechanism with a ton of almost-always-useless
EquivalenceClasses, because it's hard to see how such a decision
doesn't result in every random WHERE qual producing an
EquivalenceClass. I think that would slow down planning of most
queries by more than such an optimization is worth.

regards, tom lane