Re: timestamped archive data index searches

It is now in th4e FAQ on the web site.

---------------------------------------------------------------------------

Bruno Wolff III wrote:
> 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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026