Re: timestamped archive data index searches

On Wed, Jul 17, 2002 at 08:45:53AM +0000, Stephen Birch wrote:
>
> 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.

Do you have an index on tstamp? What does EXPLAIN ANALYSE tell you? Both
with and without seq_scans enabled.

> 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.

The planner in 7.2 knows about clustering.

More details please.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.