title: Speeding up PostgreSQL query where data is between two dates
body: <p>I have a large table (> 50m rows) which has some data with an ID and timestamp:</p>
  
  <pre><code>id, timestamp, data1, ..., dataN
  </code></pre>
  
  <p>...with a multi-column index on <code>(id, timestamp)</code>.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT * FROM mytable WHERE id = x AND timestamp BETWEEN y AND z
  </code></pre>
  
  <p>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.</p>
  
  <p>I have found <a href="http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_interval_.28of_time_or_ip_addresses.29_searching_with_spatial_indexes" rel="noreferrer">this tip</a> which recommends using a spatial index, but the example it gives is for IP addresses. However, the speed increase (436s to 3s) is impressive.</p>
  
  <p>How can I use this with timestamps?</p>