select * 
from records 
where id in ( select max(id) from records group by option_id )

Essa consulta funciona bem, mesmo em milhões de linhas. No entanto, como você pode ver no resultado da instrução de explicação:

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=30218.84..31781.62 rows=620158 width=44) (actual time=1439.251..1443.458 rows=1057 loops=1)
->  HashAggregate  (cost=30218.41..30220.41 rows=200 width=4) (actual time=1439.203..1439.503 rows=1057 loops=1)
     ->  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=1438.523..1438.807 rows=1057 loops=1)
           ->  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)
->  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

(cost=0.00..23995.15 rows=1240315 width=8) <- Aqui diz que está varrendo todas as linhas e que é obviamente ineficiente.

Eu também tentei reordenar a consulta:

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)
->  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=835.471..835.836 rows=1057 loops=1)
     ->  Seq Scan on records  (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.336..348.495 rows=1240315 loops=1)
->  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

(cost=0.00..23995.15 rows=1240315 width=8) <- Ainda está varrendo todas as linhas.

Eu tentei com e sem índice em(option_id), (option_id, id), (option_id, id desc), nenhum deles teve efeito no plano de consulta.

Existe uma maneira de executar uma consulta máxima de grupo no Postgres sem verificar todas as linhas?

O que estou procurando, programaticamente, é um índice que armazena o ID máximo para cadaoption_id conforme eles são inseridos na tabela de registros. Dessa forma, quando eu procuro o máximo de option_ids, só preciso varrer registros de índice tantas vezes quanto houver diferentes option_ids.

eu tenho vistoselect distinct on responde em todo SO de usuários de alto escalão (obrigado a @Clodoaldo Neto por me fornecer palavras-chave para pesquisar). Eis por que não funciona:

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)
  ->  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

Isso é ótimo, está usando um índice. No entanto, o uso de um índice para verificar todos os IDs não faz muito sentido. De acordo com minhas execuções, é de fato mais lento que uma simples varredura seqüencial.

Interessante o suficiente, o MySQL 5.5 é capaz de otimizar a consulta simplesmente usando um índice emrecords(option_id, id)

mysql> select count(1) from records;

+----------+
| count(1) |
+----------+
|  1086342 |
+----------+

1 row in set (0.00 sec)

mysql> 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)