Speeding up PostgreSQL query where data is between two dates

sql performance postgresql spatial-index
Roger picture Roger · Mar 17, 2010 · Viewed 14.5k times · Source

I have a large table (> 50m rows) which has some data with an ID and timestamp:

id, timestamp, data1, ..., dataN

...with a multi-column index on (id, timestamp).

I need to query the table to select all rows with a certain ID where the timestamp is between two dates, which I am currently doing using:

SELECT * FROM mytable WHERE id = x AND timestamp BETWEEN y AND z

This currently takes over 2 minutes on a high end machine (2x 3Ghz dual-core Xeons w/HT, 16GB RAM, 2x 1TB drives in RAID 0) and I'd really like to speed it up.

I have found this tip which recommends using a spatial index, but the example it gives is for IP addresses. However, the speed increase (436s to 3s) is impressive.

How can I use this with timestamps?

Answer

Konrad Garus picture Konrad Garus · Mar 17, 2010

That tip is only suitable when you have two columns A and B and use queries like:

where 'a' between A and B

That's not:

where A between 'a' and 'b'

Using index on date(column) rather than column could speed it up a little bit.

Related questions

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
How big is too big for a PostgreSQL table?

I'm working on the design for a RoR project for my company, and our development team has already run into a bit of a debate about the design, specifically the database. We have a model called Message that needs to …

Read More