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

From: "[Quipsy] Markus Karg" <karg(at)quipsy(dot)de>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: WHERE column = X AND column = Y will always be zero matching rows
Date: 2023-08-03 14:20:57
Message-ID: 1e15568107854a75ae8dd35a5a75daa7@quipsy.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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!

I wonder why squandering any resources into a Scan here, as it is pretty obvious that the result is guaranteed to be always, under any conditions, and will always be: zero matching rows - at least in a universe where a single value cannot be X AND Y at the same time.

This can be seen as a chance to add an optimization ("col = X AND col = Y always is zero matching rows), or in case such an optimization already exists in PostgreSQL, as a bug.

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

Regards,
-Markus

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Justin Tocci 2023-08-03 14:48:03 Re: BUG #18047: ODBC to PG long transaction causes PANIC
Previous Message David Rowley 2023-08-03 11:42:02 Re: BUG #18048: Database process terminated automatically