Re: Index lookup on > and < criteria

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