From: | Jason Earl <jason(dot)earl(at)simplot(dot)com> |
---|---|
To: | "Stephen Birch" <sgbirch(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: timestamped archive data index searches |
Date: | 2002-07-16 19:15:26 |
Message-ID: | 87y9cb7acx.fsf@npa01zz001.simplot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Stephen Birch" <sgbirch(at)hotmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ralph Graulich | 2002-07-16 19:37:52 | Re: size of function body |
Previous Message | Joo Paulo Batistella | 2002-07-16 19:01:55 | UPPER |