I have a table, login, which has a field by the same name; there's an index
on that field. I was surprised to discover that a SELECT which compares the
login field to a constant uses an Index scan, but if it is compared to a
function call--for example, lower()--a sequence scan is forced.
Any idea why?
Here are more details:
vdsq=> select version(); version
---------------------------------------------------------------------PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by
gccegcs-2.91.66
(1 row)
vdsq=> \d login Table "login" Attribute | Type | Modifier
--------------+-------------+-----------------------------------------------
-id | integer | not null default nextval('login_id_seq'::text)login | char(8) | not
nullpassword | char(8) | not nulljobid | integer | not nulltype | smallint | not
nullentryid | integer |lastactivity | timestamp |trackid | varchar(50) |roundid | integer |
Indices: idx_login_entryid, idx_login_jobid, idx_login_login, idx_login_password,
idx_login_type, login_pkey
vdsq=> explain select * from login where login.login = 'foo';
NOTICE: QUERY PLAN:
Index Scan using idx_login_login on login (cost=0.00..582.61 rows=609
width=62)
EXPLAIN
vdsq=> explain select * from login where login.login = lower('foo');
NOTICE: QUERY PLAN:
Seq Scan on login (cost=0.00..1361.86 rows=609 width=62)
EXPLAIN