Re: timestamped archive data index searches

Thank you for your reply, as usual you give tons to think about.

If I have understood your reasoning, I would expect your suggestion to work.
But it still seems to be using a sequence scan :-(

I also tried the following, which explain said is also using a sequence scan
:-(

SELECT sum(vol) FROM det
WHERE tstamp > (current_timestamp - '5 seconds'::interval);

Any ideas?

(all tests done on SuSE 7.3, kernel updated to 2.4.18 and PostgeSQL at
7.2.1)

>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

_________________________________________________________________
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com