Postgres: Optimizing querying by datetime

sql performance postgresql postgresql-performance
Henley Chiu picture Henley Chiu · May 19, 2013 · Viewed 15k times · Source

I have a table that has a datetime field "updated_at". A lot of my queries will be querying on this field using range queries such as rows that have updated_at > a certain date.

I already added an index to updated_at, but most of my queries are still very slow, even when I had a limit to the number of rows return.

What else can I do to optimize queries that query on datetime fields?

Answer

Bohemian picture Bohemian · May 19, 2013

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 if this approach improved your query’s performance.

Related questions

Improving query speed: simple SELECT in big postgres table

I'm having trouble regarding speed in a SELECT query on a Postgres database. I have a table with two integer columns as key: (int1,int2) This table has around 70 million rows. I need to make two kind of simple SELECT …

Read More
Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
SQL, Postgres OIDs, What are they and why are they useful?

I am looking at some PostgreSQL table creation and I stumbled upon this: CREATE TABLE ( ... ) WITH ( OIDS = FALSE ); I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not …

Read More