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
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 |