Thread

Re: WHERE column = X AND column = Y will always be zero matching rows

Jaime Casanova <jcasanov@systemguards.com.ec> — 2023-08-03T17:55:18Z

On Thu, Aug 3, 2023 at 9:21 AM [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> Hello PostgreSQL Developers,
>
> I am using PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit.
>
> (In the following X and Y are literals; X <> Y)
> I noticed is that…
>
> EXPLAIN SELECT column FROM Table WHERE column = X AND column = Y
>
> …says that PostgreSQL actually wants to perform a Scan!
>

Actually no... you're original complain, never execute the scan
(because of the filter is recognized as false)
the one, with "col is null and col = Y" on the other side do execute the scan

postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 select generate_series(1, 100000);
INSERT 0 100000

postgres=# explain select * from t1 where id =5 and id =6;
                        QUERY PLAN
-----------------------------------------------------------
 Result  (cost=0.00..1855.06 rows=1 width=4)
   One-Time Filter: false
   ->  Seq Scan on t1  (cost=0.00..1855.06 rows=1 width=4)
         Filter: (id = 5)
(4 filas)

postgres=# explain analyze select * from t1 where id =5 and id =6;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Result  (cost=0.00..1693.00 rows=1 width=4) (actual time=0.005..0.007
rows=0 loops=1)
   One-Time Filter: false
   ->  Seq Scan on t1  (cost=0.00..1693.00 rows=1 width=4) (never executed)
         Filter: (id = 5)
 Planning Time: 0.200 ms
 Execution Time: 0.056 ms
(6 filas)

--
Jaime Casanova
SYSTEMGUARDS