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?

Answer 1

Make sure the index is TableID+TableTimestamp, and you do a query like:

SELECT
    ....
    FROM YourTable
    WHERE TableID=..YourID.. 
        AND TableTimestamp>=..startrange.. 
        AND TableTimestamp<=..endrange..

if you apply functions to the table's TableTimestamp column in the WHERE, you will not be able to completely use the index.

if you are already doing all of this, then your hardware might not be up to the task.

if you are using version 8.2 or later, you should try:

WHERE (TableID, TableTimestamp) >= (..YourID.., ..startrange.. ) 
    and (TableID, TableTimestamp) <= (..YourID.., ..endrange..)

Answer 2

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.

Answer 3

Could you EXPLAIN the query for us? Then we know how the database executes your query. And what about the configuration? What are the settings for shared_buffers and work_mem? And when did you (or your system) the last vacuum and analyze? And last thing, what OS and pgSQL-version are you using?

You can create wonderfull indexes but without proper settings, the database can't use them very efficient.