Re: timestamped archive data index searches

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?

Steve



>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "Stephen Birch" <sgbirch@hotmail.com>
>CC: pgsql-general@postgreSQL.org
>Subject: Re: [GENERAL] timestamped archive data index searches Date: Wed,
>17 Jul 2002 12:37:14 -0400
>
>"Stephen Birch" <sgbirch@hotmail.com> writes:
> > I see the same problem if I query the database using psql. But to answer
> > your question, here is an example query that fails to use the index on
> > tstamp.
>
> > select sum(vol) from tdet where tstamp > 1026921570;
>
>Some experimentation shows that that expression is actually interpreted
>as
>    where text(tstamp) > '1026921570'::text
>No wonder it ain't using the index :-(.  I'm surprised that you believe
>the results are correct --- most display styles for timestamps wouldn't
>come anywhere near making this work as a textual comparison.
>
>There are various hacks for converting numeric Unix timestamps to
>Postgres timestamps.  The logically cleanest way is
>
>regression=# select 'epoch'::timestamptz + '1026921570 seconds'::interval;
>         ?column?
>------------------------
>  2002-07-17 11:59:30-04
>(1 row)
>
>If you write your query as
>     select sum(vol) from tdet where tstamp > ('epoch'::timestamptz +
>'1026921570 seconds'::interval);
>you should find that it'll use the index.
>
> > Also, I can get the same effect using pgsql with something like:
> > select sum(vol) from tdet where date(tstamp) = '2002-07-17';
>
> > Again, I would hope this would use the index on tstamp to select a small
> > subset of the very large database.
>
>Not unless you build the index on date(tstamp).
>
>            regards, tom lane




_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com