Re: timestamped archive data index searches

"Stephen Birch" <sgbirch@hotmail.com> writes:

> I know that the question of forcing PostgreSQL to use an index
> during search ops is an FAQ and have worked with each of the
> suggested solutions to no avail.
>
> > From the nature of those questions, it looks like the problem I
> > have to solve is common and unsolved. I am using the database to
> > archive data arriving at a rate of about 100 records a minute, the
> > old data needs to be stored hence the use of a database. Each
> > record is timestamped as it is inserted in the database.
>
> The system needs a web site that can display data from (say) the last
> hour of data.
>
> Now, when the database is searched using a select on the timestamp it
> never uses the index on that field  no matter how I set the db
> params. I think that the query optimizer is noticing the sequential
> nature of the timestamp field and assuming that an index will always
> slow the query.
>
> The problem is that the retrieval of the past hour's data has to scan
> the entire database and so is very, very slow.
>
> Any ideas?

What exactly does the query look like?  Have you ANALYZED the data?  I
do something very similar to this and it should be possible to get
PostgreSQL to use the index.

Jason