title: Query last N related rows per row
body: <p>I have the following query which fetches the <code>id</code> of the latest N <code>observations</code> for each <code>station</code>:</p>
  
  <pre><code>SELECT id
  FROM (
    SELECT station_id, id, created_at,
           row_number() OVER(PARTITION BY station_id
                             ORDER BY created_at DESC) AS rn
    FROM (
        SELECT station_id, id, created_at
        FROM observations
    ) s
  ) s
  WHERE rn &lt;= #{n}
  ORDER BY station_id, created_at DESC;
  </code></pre>
  
  <p>I have indexes on <code>id</code>, <code>station_id</code>, <code>created_at</code>.</p>
  
  <p>This is the only solution I have come up with that can fetch more than a single record per station. However it is quite slow (154.0 ms for a table of 81000 records).</p>
  
  <p>How can I speed up the query? </p>