Re: timestamped archive data index searches

On Sun, Jul 21, 2002 at 07:32:22 +0000,
Stephen Birch <sgbirch(at)hotmail(dot)com> wrote:
> I am still puzzled by the systems use of sequence scans. Using Tom's
> suggestion, I am now able to get a reasonable response time on the 1M
> record database by searching on the tstamp field.
>
> But ... I tried asking the database what the earliest record is:
>
> SELECT MIN(tstamp) FROM det;
>
> This used a sequence scan even if I do a SET ENABLE_SEQSCAN to off.
>
> Shouldn't this also use an index?

No because there isn't hardcoded special knowledge about the min and max
aggregate functions. This gets discussed on the lists pretty often so
you should be able to find more detailed discussions in the archives.
If there is a usable index on column of interest you should rewrite
your query to use order by and limit. For example:
select tstamp from det order by tstamp limit 1;