Postgres: Optimizing querying by datetime

Usually database optimizers won't chose to use indexes for open-ended ranges, such a updated_at > somedate.

But, in many cases the datatime column won't exceed "now", so you can preserve the semantic of > somedate by converting the condition to a range by using between like this:

where updated_at between somedate and current_timestamp

A between predicate is much more likely to cause the optimizer to chose to use an index.

Please post of this approach improved your quarry's performance.

Assuming that the index is being used but performance is still poor, the only remedy I can think of is to cluster the table by that index: http://www.postgresql.org/docs/9.1/static/sql-cluster.html

This will move the rows with the same update_at value to be co-located physically, improving performance of a query that access that table via the index, particularly for large range scans.

Pay attention to the warnings in the documentation though, and note that as rows are updated the clustering is not preserved.

Also:

When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.

Based on these restrictions it may not be a viable solution for your case, but might be useful to others.

For any given query, the use of an index depends on the cost of using that index compared to a sequential scan

Frequently developers think that because there is an index, a query should run faster, and if a query runs slow, an index is the solution. This is usually the case when the query will return few tuples. But as the number of tuples in the result increases, the cost of using an index might increase.

You are using postgres. Postgres does not support clustering around a given attribute. That means that postgres, when confronted with a range query (of the type att > a and att < b) needs to compute an estimation of the number of tuples in the result (make sure you vacuum your database frequently) and the cost of using an index compared to doing a sequential scan. it will then decide what method to use.

you can inspect this decision by running

EXPLAIN ANALYZE <query>; 

in psql. It will tell you if it uses an index or not.

If you really, really want to use the indexes instead of a sequential scan (sometimes it is needed) and you really really know what you are doing, you can change the cost of a sequential scan in the planner constants or disable sequential scans in favor of any other method. See this page for the details:

http://www.postgresql.org/docs/9.1/static/runtime-config-query.html

Make sure you browse the correct version of the documentation.

--dmg