On Thu, 2023-08-03 at 14:20 +0000, [Quipsy] Markus Karg wrote:
> 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!
This is not a bug.
> 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.
Such an optimization, while certainly possible, would not be free, and each
query with more than one WHERE condition would have to pay the price.
On the other hand, only ill-written queries would benefit.
Since PostgreSQL only caches execution plans in special cases, anything that
slows down the optimizer should improve enough statements that it is a net win.
I don't see that here (but that is of course a matter of opinion).
Yours,
Laurenz Albe