title: Optimize groupwise maximum query
body: <pre><code>select * 
  from records 
  where id in ( select max(id) from records group by option_id )
  </code></pre>
  
  <p>This query works fine even on millions of rows. However as you can see from the result of explain statement:</p>
  
  <pre><code>                                               QUERY PLAN
  -------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=30218.84..31781.62 rows=620158 width=44) (actual time=1439.251..1443.458 rows=1057 loops=1)
  -&gt;  HashAggregate  (cost=30218.41..30220.41 rows=200 width=4) (actual time=1439.203..1439.503 rows=1057 loops=1)
       -&gt;  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=1438.523..1438.807 rows=1057 loops=1)
             -&gt;  Seq Scan on records records_1  (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.103..527.914 rows=1240315 loops=1)
  -&gt;  Index Scan using records_pkey on records  (cost=0.43..7.80 rows=1 width=44) (actual time=0.002..0.003 rows=1 loops=1057)
       Index Cond: (id = (max(records_1.id)))
  Total runtime: 1443.752 ms
  </code></pre>
  
  <p><code>(cost=0.00..23995.15 rows=1240315 width=8)</code> &lt;- Here it says it is scanning all rows and that is obviously inefficient. </p>
  
  <p>I also tried reordering the query:</p>
  
  <pre><code>select r.* from records r
  inner join (select max(id) id from records group by option_id) r2 on r2.id= r.id;
  
                                                 QUERY PLAN
  -------------------------------------------------------------------------------------------------------------------------------
  
  Nested Loop  (cost=30197.15..37741.04 rows=964 width=44) (actual time=835.519..840.452 rows=1057 loops=1)
  -&gt;  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=835.471..835.836 rows=1057 loops=1)
       -&gt;  Seq Scan on records  (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.336..348.495 rows=1240315 loops=1)
  -&gt;  Index Scan using records_pkey on records r  (cost=0.43..7.80 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=1057)
       Index Cond: (id = (max(records.id)))
  Total runtime: 840.809 ms
  </code></pre>
  
  <p><code>(cost=0.00..23995.15 rows=1240315 width=8)</code> &lt;- Still scanning all rows.</p>
  
  <p><strong>I tried with and without index on <code>(option_id)</code>, <code>(option_id, id)</code>, <code>(option_id, id desc)</code>, none of them had any effect on the query plan.</strong></p>
  
  <p>Is there a way of executing a groupwise maximum query in Postgres without scanning all rows?</p>
  
  <p>What I am looking for, programmatically, is an index which stores the maximum id for each <code>option_id</code> as they are inserted into the records table. That way, when I query for maximums of option_ids, I should only need to scan index records as many times as there are different option_ids.</p>
  
  <p>I've seen <code>select distinct on</code> answers all over SO from high ranking users (thanks to @Clodoaldo Neto for giving me keywords to search for). Here's why it doesn't work:</p>
  
  <pre><code>create index index_name on records(option_id, id desc)
  
  select distinct on (option_id) *
  from records
  order by option_id, id desc
                                                 QUERY PLAN
  ------------------------------------------------------------------------------------------------------------------------------------------------------------
  Unique  (cost=0.43..76053.10 rows=964 width=44) (actual time=0.049..1668.545 rows=1056 loops=1)
    -&gt;  Index Scan using records_option_id_id_idx on records  (cost=0.43..73337.25 rows=1086342 width=44) (actual time=0.046..1368.300 rows=1086342 loops=1)
  Total runtime: 1668.817 ms
  </code></pre>
  
  <p>That's great, it's using an index. However using an index to scan all ids doesn't really make much sense. According to my executions, it is in fact slower than a simple sequential scan. </p>
  
  <p>Interesting enough, MySQL 5.5 is able to optimize the query simply using an index on <code>records(option_id, id)</code></p>
  
  <pre><code>mysql&gt; select count(1) from records;
  
  +----------+
  | count(1) |
  +----------+
  |  1086342 |
  +----------+
  
  1 row in set (0.00 sec)
  
  mysql&gt; explain extended select * from records
         inner join ( select max(id) max_id from records group by option_id ) mr
                                                        on mr.max_id= records.id;
  
  +------+----------+--------------------------+
  | rows | filtered | Extra                    |
  +------+----------+--------------------------+
  | 1056 |   100.00 |                          |
  |    1 |   100.00 |                          |
  |  201 |   100.00 | Using index for group-by |
  +------+----------+--------------------------+
  
  3 rows in set, 1 warning (0.02 sec)
  </code></pre>