public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19386: Unnecessary Sort in query plan for SELECT literal with ORDER BY
Date: Wed, 21 Jan 2026 10:42:07 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

Andrei Lepikhov <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19386: Unnecessary Sort in query plan for SELECT literal with ORDER BY
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox