Speeding up PostgreSQL query where data is between two dates

Question

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?

Accepted Answer

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.

Speeding up PostgreSQL query where data is between two dates

Solution

Problem