I was working a project that needed to be able to look up records, from a certain time frame. Interestingly enough, I discovered that the AGE() function cannot use indexes. My workaround, is to create a functional index that extracts the unix timestamp from the TIMESTAMP data type, and uses a BETWEEN clause.

So:

SELECT * FROM my_table
WHERE AGE(NOW(), my_timestamp) <= '1 day'::interval;

Becomes:

CREATE INDEX my_table_epoch_idx ON my_table
  (EXTRACT(epoch FROM my_timestamp));

SELECT * FROM my_table WHERE EXTRACT(epoch FROM my_timestamp)
  BETWEEN EXTRACT(epoch FROM NOW()) - 86400
  AND EXTRACT(epoch FROM NOW());

Both queries fetch items where my_timestamp is less than 24 hours old, but the latter has the ability to use an index.

This entry was posted on Monday, February 9th, 2009 at 3:44 pm and is filed under PostgreSQL, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.