Re: Timestamp-based indexing

Josh Berkus <josh(at)agliodbs(dot)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