"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