Surprising sequence scan when function call used

From: "Will Fitzgerald" <fitzgerald(at)inetmi(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Surprising sequence scan when function call used
Date: 2000-10-25 17:26:51
Message-ID: KPELIDPNOGGPCLGOMDLFOEIKCDAA.fitzgerald@inetmi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 gcc egcs-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 null
password | char(8) | not null
jobid | integer | not null
type | smallint | not null
entryid | 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jie Liang 2000-10-25 17:44:48 How to call a shell command in rule
Previous Message Clayton Cottingham 2000-10-25 16:16:21 Re: How to call a shell command in rule