Re: timestamped archive data index searches

"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