seq scan on a table whereas an index exist

From: Nicolas GIMMILLARO <Nicolas(dot)GIMMILLARO(at)wmi(dot)fr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: seq scan on a table whereas an index exist
Date: 2001-03-06 11:22:43
Message-ID: 3AA4C883.2FB0AC51@wmi.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I don't understand the query plan used to resolve this query :
select * from web_access where date between '01/01/2001' and
'06/03/2001'

The web_access table as this structure :

Table "web_access"
Attribute | Type | Modifier
-----------+--------------+----------
source | varchar(128) | not null
date | date |
heure | time |
action | varchar(128) |
code1 | integer |
code2 | integer |
util | text |
refer | varchar(128) |
browser | varchar(20) |
brorel | varchar(10) |
os | varchar(20) |
osrel | varchar(10) |
Indices: web_access_date,
web_access_source

Index "web_access_date"
Attribute | Type
-----------+------
date | date
btree

A classical select on table web_access uses a seq scan :
-----------------------------------------------------------------------------------------------------------

explain select * from web_access where date between '01/01/2001' and
'06/03/2001';
NOTICE: QUERY PLAN:

Seq Scan on web_access (cost=0.00..35380.36 rows=117694 width=116)
-----------------------------------------------------------------------------------------------------------

The same query with 2 sub-queries uses an index scan :
-----------------------------------------------------------------------------------------------------------

explain select * from web_access where date between (select
'01/01/2001'::date) and (select '06/03/2001'::date);
NOTICE: QUERY PLAN:

Index Scan using web_access_date on web_access (cost=0.00..26858.67
rows=7908 width=116)
InitPlan
-> Result (cost=0.00..0.00 rows=0 width=0)
-> Result (cost=0.00..0.00 rows=0 width=0)
-----------------------------------------------------------------------------------------------------------

What's wrong with my "select * from web_access where date between
'01/01/2001' and '06/03/2001' " query ?

Nicolas GIMMILLARO

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-03-06 11:34:00 "select ... where field like lower('%text%')" fails
Previous Message Justin Clift 2001-03-06 07:13:07 Irony bug