From: Tom Lane <tgl@sss.pgh.pa.us>
To: "[Quipsy] Markus Karg" <karg@quipsy.de>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: WHERE column = X AND column = Y will always be zero matching rows
In-reply-to: <1e15568107854a75ae8dd35a5a75daa7@quipsy.de>
References: <1e15568107854a75ae8dd35a5a75daa7@quipsy.de>
Comments: In-reply-to "[Quipsy] Markus Karg" <karg@quipsy.de>
	message dated "Thu, 03 Aug 2023 14:20:57 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3497514.1691085559.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 03 Aug 2023 13:59:19 -0400
Message-ID: <3497515.1691085559@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3497515.1691085559%40sss.pgh.pa.us>
Precedence: bulk

"[Quipsy] Markus Karg" <karg@quipsy.de> writes:
> (In the following X and Y are literals; X <> Y)
> I noticed is that...
> EXPLAIN SELECT column FROM Table WHERE column =3D X AND column =3D Y
> ...says that PostgreSQL actually wants to perform a Scan!

You should learn to read plans before complaining about them.

What you actually get is something like

regression=3D# explain analyze select * from tenk1 where hundred =3D 42 an=
d hundred =3D 99;
                                             QUERY PLAN                   =
                           =

--------------------------------------------------------------------------=
---------------------------
 Result  (cost=3D5.04..224.95 rows=3D1 width=3D244) (actual time=3D0.001..=
0.002 rows=3D0 loops=3D1)
   One-Time Filter: false
   ->  Bitmap Heap Scan on tenk1  (cost=3D5.04..224.95 rows=3D1 width=3D24=
4) (never executed)
         Recheck Cond: (hundred =3D 42)
         ->  Bitmap Index Scan on tenk1_hundred  (cost=3D0.00..5.04 rows=3D=
100 width=3D0) (never executed)
               Index Cond: (hundred =3D 42)
 Planning Time: 0.148 ms
 Execution Time: 0.046 ms
(8 rows)

See the "never executed" markings?  The one-time filter prevents
anything below the Result from actually doing anything.

=46rom memory, the reason it's like this is that it's the most
straightforward extension of cases involving pseudoconstant
conditions, that is parameters not known at plan time.
If you had something like "WHERE var =3D param1 AND var =3D const1"
then you might get something involving a "param1 =3D const1"
one-time filter, and below that a plan to retrieve rows with
"var =3D const1", which would only be run when the run-time-supplied
parameter happens to match the constant.

David's statement that we don't want to write code for such cases
isn't completely true, but it is true that we don't want to expend
code on producing a pretty-looking plan for them.

> BTW the same happens for column IS NULL AND column =3D Y.

That indeed is a case we haven't bothered with, and probably
never will.  The multiple-constants-equated-to-the-same-thing
situation is dealt with by the equivalence class machinery,
which has many other purposes but happens to be able to detect
that situation cheaply.  But IS NULL is not an equivalence
condition (as we define those, anyway).  Likewise, we won't
reduce something like "var > C1 AND var < C1" to constant-false;
there's just not enough win probability to justify spending
planner cycles looking for such cases.

			regards, tom lane