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@hotmail.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026