is this a bug or do I not understand the query planner?

From: Rob Prowel <tempest766(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: is this a bug or do I not understand the query planner?
Date: 2005-11-03 23:36:48
Message-ID: 20051103233648.44895.qmail@web60019.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

two almost identical queries: one searches for

read in ('N','n')

and the other searches for

read in ('Y','y').

the (explain) SQL statement says that one uses the
index on the (read) field and the other does a
sequential table scan. Why!!!???? I can think of no
logical reason for this behavior.

usenet=# \d+ article
Table "public.article"
Column | Type | Modifiers
| Description
---------+------------------------+------------------------------+-------------
msg | integer | not null
|
thedate | date | not null
|
subject | character varying(300) |
|
lines | integer | not null default 0
|
read | character(1) | not null default
'N'::bpchar |
ng | integer | not null default 0
|
author | integer | not null default 0
|
Indexes:
"article_pkey" PRIMARY KEY, btree (msg)
"article_read" btree ("read")
Has OIDs: yes

usenet=# explain select * from article where read in
('Y','y');
QUERY PLAN

--------------------------------------------------------------------------------------------
Index Scan using article_read, article_read on
article (cost=0.00..4.03 rows=1 width=107)
Index Cond: (("read" = 'Y'::bpchar) OR ("read" =
'y'::bpchar))
(2 rows)

usenet=# explain select * from article where read in
('N','n');
QUERY PLAN

-------------------------------------------------------------------
Seq Scan on article (cost=0.00..68661.02
rows=2018135 width=107)
Filter: (("read" = 'N'::bpchar) OR ("read" =
'n'::bpchar))
(2 rows)


__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Qingqing Zhou 2005-11-03 23:55:03 Re: is this a bug or do I not understand the query planner?
Previous Message Alvaro Herrera 2005-11-03 20:40:46 Re: attislocal value changed with dump