Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group