seq scan on a table whereas an index exist

Поиск
Список
Период
Сортировка
От Nicolas GIMMILLARO
Тема seq scan on a table whereas an index exist
Дата
Msg-id 3AA4C883.2FB0AC51@wmi.fr
обсуждение исходный текст
Ответы Re: seq scan on a table whereas an index exist  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Using libpq to access a repote database from a c trigger function breaks.
Следующее
От: Sean Kelly
Дата:
Сообщение: "select ... where field like lower('%text%')" fails