Date index not used when selecting a date range

Поиск
Список
Период
Сортировка
От Poul Møller Hansen
Тема Date index not used when selecting a date range
Дата
Msg-id 488DC514.6030804@pbnet.dk
обсуждение исходный текст
Ответы Re: Date index not used when selecting a date range  ("Francisco Reyes" <lists@stringsutils.com>)
Список pgsql-general
I'm wondering why this index is not used for my query.

This is the index:
CREATE INDEX idx_stat_date_node_type
  ON public.stat
  USING btree
  (date, node, "type");

When quering an exact date, it is used

explain SELECT * FROM public.stat WHERE node = '1010101010' AND date =
'2008-01-01'

"Index Scan using idx_stat_date_node_type on stat  (cost=0.00..279.38
rows=150 width=146)"
"  Index Cond: ((date = '2008-01-01'::date) AND ((node)::text =
'1010101010'::text))"


But when selecting a date range I get this

explain SELECT * FROM public.stat WHERE node = '1010101010' AND
 ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)

"Bitmap Heap Scan on stat  (cost=710.14..179319.44 rows=39174 width=146)"
"  Recheck Cond: ((node)::text = '1010101010'::text)"
"  Filter: ((date <= '2008-06-30'::date) AND (date >= '2008-01-01'::date))"
"  ->  Bitmap Index Scan on idx_stat_node_id  (cost=0.00..710.14
rows=55182 width=0)"
"        Index Cond: ((node)::text = '1010101010'::text)"


How can I change my query so it will use the index ?

Thanks
Poul


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

Предыдущее
От: "Abhirama Mallela"
Дата:
Сообщение: Setting up the postgres codebase in Eclipse
Следующее
От: Alex Gen
Дата:
Сообщение: Connecting to an existing transaction state.