pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: WHERE column = X AND column = Y will always be zero matching rows
2+ messages / 1 participants
[nested] [flat]
* AW: WHERE column = X AND column = Y will always be zero matching rows
@ 2023-08-03 14:53 [Quipsy] Markus Karg <karg@quipsy.de>
1 sibling, 0 replies; 2+ messages in thread
From: [Quipsy] Markus Karg @ 2023-08-03 14:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
David,
thank you for your opinion, so it is a chance for future optimization.
Whether or not this „should not happen“ is a different discussion. 😉
-Markus
Von: David G. Johnston <david.g.johnston@gmail.com>
Gesendet: Donnerstag, 3. August 2023 16:48
An: [Quipsy] Markus Karg <karg@quipsy.de>
Cc: pgsql-bugs@lists.postgresql.org
Betreff: Re: WHERE column = X AND column = Y will always be zero matching rows
On Thursday, August 3, 2023, [Quipsy] Markus Karg <karg@quipsy.de<mailto:karg@quipsy.de>> wrote:
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
Mostly because no one wants to volunteer to write code to deal with poorly written queries, nor, more technically, expend compute resources looking for situations that should not happen.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
* AW: WHERE column = X AND column = Y will always be zero matching rows
@ 2023-08-04 06:12 [Quipsy] Markus Karg <karg@quipsy.de>
1 sibling, 0 replies; 2+ messages in thread
From: [Quipsy] Markus Karg @ 2023-08-04 06:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Tom,
thanks a lot for your kind explanation, this was very interesting to read!
In fact, I have missed the "never executed" marks.
BTW, I did not "complain". I just noticed something and was unclear if this is a bug or not, and I clearly told so in the opening of my posting. It was definitively not my intention to bother or annoy anybody, actually. Sorry to have troubled so many in this mailing list.
-Markus
-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: Donnerstag, 3. August 2023 19:59
An: [Quipsy] Markus Karg <karg@quipsy.de>
Cc: pgsql-bugs@lists.postgresql.org
Betreff: Re: WHERE column = X AND column = Y will always be zero matching rows
"[Quipsy] Markus Karg" <karg@quipsy.de> writes:
> (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!
You should learn to read plans before complaining about them.
What you actually get is something like
regression=# explain analyze select * from tenk1 where hundred = 42 and hundred = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Result (cost=5.04..224.95 rows=1 width=244) (actual time=0.001..0.002 rows=0 loops=1)
One-Time Filter: false
-> Bitmap Heap Scan on tenk1 (cost=5.04..224.95 rows=1 width=244) (never executed)
Recheck Cond: (hundred = 42)
-> Bitmap Index Scan on tenk1_hundred (cost=0.00..5.04 rows=100 width=0) (never executed)
Index Cond: (hundred = 42) Planning Time: 0.148 ms Execution Time: 0.046 ms
(8 rows)
See the "never executed" markings? The one-time filter prevents anything below the Result from actually doing anything.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-08-04 06:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-03 14:53 ` [Quipsy] Markus Karg <karg@quipsy.de>
2023-08-04 06:12 ` [Quipsy] Markus Karg <karg@quipsy.de>
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