The system seems to think that a scan is cheap because the table is so small. Have you ever ANALYZEd that table? Also, EXPLAIN ANALYZE gives a much better idea of what is going on... On Thu, Nov 18, 2004 at 03:55:12PM +0100, Marc Boucher wrote: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
pgpDNe1amJYhC.pgp
Description: PGP signature
