PostgreSQL query acceleration where data is between two dates

Question

I have a large table (> 50 m rows) that has some data with id and timestamp:

id, timestamp, data1, ..., dataN 

... with an index with multiple columns on (id, timestamp) .

I need to query a table to select all rows with a specific identifier, where the timestamp is between the two dates that I am currently doing, using:

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

It currently takes more than 2 minutes on a high-performance computer (2x Xeon 3Ghz tri-core with HT, 16 GB of RAM, 2x 1 TB of drives in RAID 0), and I really would like to speed it up.

I found this tip that recommends using a spatial index, but the example it gives applies to IP addresses. However, the increase in speed (from 436 to 3 seconds) is impressive.

How can I use this with timestamps?

Answer 1

This tip only applies when you have two columns A and B and use queries such as:

 where 'a' between A and B 

Is not:

 where A between 'a' and 'b' 

Using an index on date(column) rather than column can speed it up a bit.

Answer 2

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

You can create great indexes, but without the right settings, the database cannot use them very efficiently.

Answer 3

Make sure the pointer is TableID + TableTimestamp, and you are executing a query like:

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

if you apply the functions to the TableTimestamp column of the table in WHERE, you cannot fully use the index.

if you already do all this, your equipment may 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..)