index and queries using '<' '>'

Поиск
Список
Период
Сортировка
От Marc Boucher
Тема index and queries using '<' '>'
Дата
Msg-id 3.0.5.32.20041118155512.00acd780@prx.mad.com
обсуждение исходный текст
Ответы Re: index and queries using '<' '>'  (Martijn van Oosterhout <kleptog@svana.org>)
Список pgsql-general
I'm using PG 7.3.4

I've a table with a column of type int8 where I store date-based values,
and an index exists for it.
The problem is that the index is almost never used with the '>' test.

# explain SELECT date FROM album WHERE (date='1093989600');
 Index Scan using date_album_key on album  (cost=0.00..86.31 rows=21 width=8)
   Index Cond: (date = 1093989600::bigint)

# explain SELECT date FROM album WHERE (date>'1093989600');
 Seq Scan on album  (cost=0.00..907.91 rows=447 width=8)
   Filter: (date > 1093989600::bigint)

# explain SELECT date FROM album WHERE (date>'1099989600');
 Index Scan using date_album_key on album  (cost=0.00..323.09 rows=84 width=8)
   Index Cond: (date > 1099989600::bigint)


It works when the query is supposed to generate low number of rows. The
problem is that the execution time is much longer with a scan.
How can I force the use of this index?


--
Marc


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

Предыдущее
От: David Pradier
Дата:
Сообщение: How to make a good documentation of a database ?
Следующее
От: Matt
Дата:
Сообщение: Re: How to make a good documentation of a database ?