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(at)sss(dot)pgh(dot)pa(dot)us>
>To: "Stephen Birch" <sgbirch(at)hotmail(dot)com>
>CC: pgsql-general(at)postgreSQL(dot)org
>Subject: Re: [GENERAL] timestamped archive data index searches Date: Wed,
>17 Jul 2002 12:37:14 -0400
>
>"Stephen Birch" <sgbirch(at)hotmail(dot)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