title: How to efficiently select rows having a MIN date in postgres
body: <p>I need to quickly select a value ( <code>baz</code> ) from the "earliest" ( <code>MIN(save_date)</code> ) rows grouped by an their <code>foo_id</code>. The following query returns the correct rows (well almost, it can return multiples for each foo_id when there are duplicate save_dates).</p>
  
  <p>The <code>foos</code> table contains about 55k rows and the <code>samples</code> table contains about 25 million rows.</p>
  
  <pre><code>CREATE TABLE foos (
      foo_id     int,
      val        varchar(40),
      # ref_id is a FK, constraint omitted for brevity
      ref_id     int
  )
  CREATE TABLE samples (
      sample_id  int,
      save_date  date,
      baz        smallint,
      # foo_id is a FK, constraint omitted for brevity
      foo_id     int
  )
  
  WITH foo ( foo_id, val ) AS (
          SELECT foo_id, val FROM foos
          WHERE foos.ref_id = 1
      ORDER BY foos.val ASC
      LIMIT 25 OFFSET 0
  )
  SELECT foo.val, firsts.baz
  FROM foo
  LEFT JOIN (
      SELECT A.baz, A.foo_id
      FROM samples A
      INNER JOIN (
          SELECT foo_id, MIN( save_date ) AS save_date
          FROM samples
          GROUP BY foo_id
      ) B
      USING ( foo_id, save_date )
  ) firsts USING ( foo_id )
  </code></pre>
  
  <p>This query currently takes over 100 seconds; I'd like to see this return in ~1 second (or less!).</p>
  
  <p><strong>How can I write this query to be optimal?</strong></p>
  
  <hr>
  
  <h2>Updated; adding <code>explains</code>:</h2>
  
  <p>Obviously the actual query I'm using isn't using tables foo, baz, etc.</p>
  
  <p>The "dumbed down" example query's (from above) <code>explain</code>:</p>
  
  <pre><code>Hash Right Join  (cost=337.69..635.47 rows=3 width=100)
    Hash Cond: (a.foo_id = foo.foo_id)
    CTE foo
      -&gt;  Limit  (cost=71.52..71.53 rows=3 width=102)
            -&gt;  Sort  (cost=71.52..71.53 rows=3 width=102)
                  Sort Key: foos.val
                  -&gt;  Seq Scan on foos  (cost=0.00..71.50 rows=3 width=102)
                        Filter: (ref_id = 1)
    -&gt;  Hash Join  (cost=265.25..562.90 rows=9 width=6)
          Hash Cond: ((a.foo_id = samples.foo_id) AND (a.save_date = (min(samples.save_date))))
          -&gt;  Seq Scan on samples a  (cost=0.00..195.00 rows=1850 width=10)
          -&gt;  Hash  (cost=244.25..244.25 rows=200 width=8)
                -&gt;  HashAggregate  (cost=204.25..224.25 rows=200 width=8)
                      -&gt;  Seq Scan on samples  (cost=0.00..195.00 rows=1850 width=8)
    -&gt;  Hash  (cost=0.60..0.60 rows=3 width=102)
          -&gt;  CTE Scan on foo  (cost=0.00..0.60 rows=3 width=102)
  </code></pre>