Re: Timestamp-based indexing

Josh Berkus <josh@agliodbs.com> writes:
>> monitor=# explain analyze select * from "eventtable" where timestamp >
>> CURRENT_TIMESTAMP - INTERVAL '10 minutes';

> Hmmm.  What verison of PostgreSQL are you running?  I seem to remember an
> issue in one version with selecting comparisons against now().

I'm also wondering about the exact datatype of the "timestamp" column.
If it's timestamp without timezone, then the above is a cross-datatype
comparison (timestamp vs timestamptz) and hence not indexable before
8.0.  This could be fixed easily by using the right current-time
function, viz LOCALTIMESTAMP not CURRENT_TIMESTAMP.  (Consistency has
obviously never been a high priority with the SQL committee :-(.)

Less easily but possibly better in the long run, change the column type
to timestamp with time zone.  IMHO, columns representing definable
real-world time instants should always be timestamptz, because the other
way leaves you open to serious confusion about what the time value
really means.

            regards, tom lane