| From: | Marc Boucher <pgml(at)gmx(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | index and queries using '<' '>' |
| Date: | 2004-11-18 14:55:12 |
| Message-ID: | 3.0.5.32.20041118155512.00acd780@prx.mad.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matt | 2004-11-18 15:02:59 | Re: How to make a good documentation of a database ? |
| Previous Message | David Pradier | 2004-11-18 14:31:30 | How to make a good documentation of a database ? |