Re: timestamped archive data index searches

The select is something like..

SELECT AVG(x) FROM arch WHERE tstamp > :t

or

SELECT * FROM arch WHERE tstamp > :t.

I am using embedded SQL and the variable t is set to the current time minus
one hour (60*60). The results are correct. It is just taking far too long.

Yes, I have been using ANALYZE, that is how I know a sequential search has
been selected. I have also tried telling the database to not use sequential
searches using the appropriate SET command.

As I mentioned, I believe this may be a 'feature' of PostgreSQL that will be
encountered by many people. Since the incoming data is timestamped as it
arrives, the time column (I called it tstamp) will always be ordered. This
fools the optimizer into thinking a sequential search would be faster.

In fact, my query only needs to look at a small subset of the stored data,
just the recently inserted records. But the whole database is always
checked.

I love PostgreSQL and have used it for many years, but this has me stumped!

Steve


>From: Jason Earl <jason.earl@simplot.com>
>To: "Stephen Birch" <sgbirch@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] timestamped archive data index searches
>Date: 16 Jul 2002 13:15:26 -0600
>
>"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




_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com