title: Efficient PostgreSQL Query for Mins and Maxis withing equal intervals in a time period
body: <p>I am using Postgres v9.2.6.</p>
  
  <p>Have a system with lots of devices that take measurements.  These measurements are stored in 
  table with three fields.</p>
  
  <ul>
  <li>device_id</li>
  <li>measurement (Indexed)</li>
  <li>time (Indexed)</li>
  </ul>
  
  <p>There could be 10 Million measurements in a single year.  Most of the time the user is only interested in 100 min max pairs within equal interval for a certain period, for example in last 24 hours or in last 53 weeks.  To get these 100 mins and maxs the period is divided into 100 equal intervals.  From each interval min and max is extracted.  Would you recommend the most efficient approach to query the data?  So far I have tried the following query:</p>
  
  <pre><code>WITH periods AS (
      SELECT time.start AS st, time.start + (interval '1 year' / 100) AS en FROM generate_series(now() - interval '1 year', now(), interval '1 year' / 100) AS time(start)
  )
  SELECT * FROM sample_data
     JOIN periods 
     ON created_at BETWEEN periods.st AND periods.en AND
     customer_id = 23
     WHERE
          sample_data.id = (SELECT id FROM sample_data WHERE created_at BETWEEN periods.st AND periods.en ORDER BY sample ASC LIMIT 1)
  </code></pre>
  
  <p>This test approach took over a minute for 1 million points on MacBook Pro.</p>
  
  <p>Thanks...</p>