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

On Thu, Aug 3, 2023 at 9:21 AM [Quipsy] Markus Karg <karg(at)quipsy(dot)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