Skip site navigation (1) Skip section navigation (2)

Re: Timestamp-based indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
Cc: "Harmon S(dot) Nine" <hnine(at)netarx(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Timestamp-based indexing
Date: 2004-08-16 23:25:41
Message-ID: 18544.1092698741@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Mark KirkwoodDate: 2004-08-17 06:26:15
Subject: Re: Strange problems with more memory.
Previous:From: Josh BerkusDate: 2004-08-16 23:16:27
Subject: Re: Index type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group