On Tue, Nov 01, 2005 at 12:18:19PM -0600, David Durham wrote: > Apologies if this questions is asked often. I'm doing some select > statements based on a timestamp field. I have an index on the field, > and when I use the '=' operator the index is used. However, if I use > the '>' or '<' operators, then it does a full table scan. I've got > around 6 million rows, so I would think that an index scan would be more > appropriate.
No need to guess: run the queries with enable_seqscan disabled and see if an index scan is indeed faster. > select max(myTimeStamp) from myTable; In current releases min() and max() can't use indexes; search the archives for numerous discussions of the reasons. The workarounds are, respectively: SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp LIMIT 1; SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp DESC LIMIT 1; In 8.1 min() and max() are optimized to do the above. > select * from myTable where myTimeStamp < '10/2/2005'; > select * from myTable where myTimeStamp < '10/2/2005' and myTimeStamp > >= '10/1/2005'; How many rows do these queries return? If they return a significant portion of the table then the planner might think that a sequential scan would be faster than an index scan. It would be useful to see the EXPLAIN ANALYZE output of these queries so we can see how accurate the planner's row count estimates are. Has the table been vacuumed and analyzed? If so, and if the planner's row count estimates aren't close to the actual row counts, then you might benefit from increasing the statistics target for the myTimeStamp column. How much memory do you have and what's your effective_cache_size setting? That's one of the settings that influences the planner's decision. Also, what version of PostgreSQL are you running? BTW, pgsql-performance would be a more appropriate list to discuss performance issues. -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match