Received: from hub.org.org (localhost [127.0.0.1]) by hub.org (8.10.1/8.11.0) with SMTP id e9PMtt695783 for ; Wed, 25 Oct 2000 18:55:55 -0400 (EDT) (envelope-from pgsql-sql-owner@hub.org) Received: from nameserve.inetmi.com (nameserve.inetmi.com [204.146.136.253]) by hub.org (8.10.1/8.11.0) with SMTP id e9PIkfO80066 for ; Wed, 25 Oct 2000 14:46:42 -0400 (EDT) (envelope-from fitzgerald@inetmi.com) Received: (qmail 11119 invoked from network); 25 Oct 2000 17:29:37 -0000 Received: from unknown (HELO LAPTOPWILL) (204.146.136.1) by 204.146.136.253 with SMTP; 25 Oct 2000 17:29:37 -0000 From: "Will Fitzgerald" To: Subject: Surprising sequence scan when function call used Date: Wed, 25 Oct 2000 13:26:51 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Importance: Normal X-Archive-Number: 200010/223 X-Sequence-Number: 1887 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