From: [Quipsy] Markus Karg <karg@quipsy.de>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: WHERE column = X AND column = Y will always be zero matching rows
Date: Thu, 3 Aug 2023 14:20:57 +0000
Message-ID: <1e15568107854a75ae8dd35a5a75daa7@quipsy.de> (raw)
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
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: pgsql-bugs@postgresql.org
Cc: karg@quipsy.de, 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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox