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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
