Query lenta no Postgres: do pg_stat_statements ao índice certo

Como achar qual consulta derruba o banco, ler o plano de execução sem adivinhação e escolher o índice que resolve — em vez de criar cinco na esperança.

Contexto: o que estava rodando

Uma tela de pedidos com filtro por status e ordenação por data. Cerca de 900 mil linhas na tabela. Funcionou bem por um ano, ficou lenta, e então começou a quebrar.

O erro

vercel logs --prodexit 1

PrismaClientKnownRequestError:
Invalid `prisma.pedido.findMany()` invocation:

Error occurred during query execution:
PostgresError { code: "57014",
  message: "canceling statement due to statement timeout" }

O statement_timeout estava fazendo o trabalho dele: matar consulta que passa do limite antes que ela derrube o banco inteiro. O timeout é o sintoma, não a doença — e uma consulta lenta também segura conexão do pool por mais tempo, o que multiplica o estrago.

Diagnóstico

Achar a consulta certa, não a mais lenta

O primeiro instinto é procurar a consulta com maior tempo médio. É o critério errado. Uma consulta de 3 segundos que roda uma vez por dia é irrelevante; uma de 80 ms que roda dez mil vezes por minuto é o que consome o banco.

Ordene por tempo total:

diagnostico/01-piores-consultas.sql

-- Precisa da extensão habilitada. No Supabase: Database → Extensions.
create extension if not exists pg_stat_statements;

select
  calls,
  round(mean_exec_time::numeric, 2)  as media_ms,
  round(total_exec_time::numeric, 2) as total_ms,
  rows,
  round(100 * total_exec_time / sum(total_exec_time) over (), 1) as pct,
  query
from pg_stat_statements
where query not ilike '%pg_stat_statements%'
order by total_exec_time desc
limit 10;

O resultado foi direto ao ponto:

psql · resultado

 calls  | media_ms | total_ms  | rows   | pct  | query
--------+----------+-----------+--------+------+---------------------------------
 184203 |   412.77 | 76039... |  18420 | 71.4 | select ... from pedidos where status = $1 order by criado_em desc limit $2
  92100 |     8.11 |    746... | 921000 |  0.7 | select ... from clientes where id = $1

Uma única consulta respondia por 71% do tempo de banco da aplicação.

Ler o plano de execução

Com a consulta identificada, EXPLAIN responde por que ela é lenta. Duas opções importam: ANALYZE executa de verdade e mostra os números reais, e BUFFERS mostra quantas páginas foram lidas.

diagnostico/02-plano.sql

explain (analyze, buffers, format text)
select id, cliente_id, total_cents, criado_em
from pedidos
where status = 'pendente'
order by criado_em desc
limit 50;

psql · plano

Limit  (cost=48291.02..48296.85 rows=50 width=32)
       (actual time=406.118..406.131 rows=50 loops=1)
  ->  Sort  (cost=48291.02..48512.44 rows=88568 width=32)
            (actual time=406.116..406.123 rows=50 loops=1)
        Sort Key: criado_em DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Seq Scan on pedidos  (cost=0.00..45346.00 rows=88568 width=32)
                                 (actual time=0.031..381.204 rows=91240 loops=1)
              Filter: (status = 'pendente'::text)
              Rows Removed by Filter: 812760
              Buffers: shared hit=1204 read=34142
Planning Time: 0.142 ms
Execution Time: 406.170 ms

Três linhas contam a história inteira:

Seq Scan on pedidos — o Postgres leu a tabela toda. Não havia índice utilizável para status.

Rows Removed by Filter: 812760 — leu 904 mil linhas para devolver 50. Descartou 812 mil, uma a uma.

Buffers: read=34142 — 34 mil páginas de 8 KB vindas do disco, cerca de 270 MB de leitura para uma tela de 50 itens.

Estimativa contra realidade

Vale conferir o par rows=88568 (estimativa) e actual rows=91240 (real). Aqui estão próximos, então as estatísticas estão boas. Quando divergem por ordens de grandeza, o problema não é falta de índice — é estatística desatualizada, e a correção é analyze na tabela antes de qualquer outra coisa. É também o primeiro passo obrigatório depois de uma carga grande, como a de uma migração.

analyze pedidos;

A solução

Índice composto, na ordem certa.

A consulta filtra por status e ordena por criado_em. Um índice que cobre as duas coisas permite ao Postgres pular direto para as linhas certas já ordenadas — sem varredura e sem passo de ordenação.
migracao/01-indice.sql
```
create index concurrently pedidos_status_criado_em_idx
  on pedidos (status, criado_em desc);
```
A ordem das colunas não é arbitrária. A regra é: primeiro as colunas usadas em igualdade, depois as de ordenação ou intervalo. Invertido — (criado_em, status) — o índice não serve para filtrar por status.

O desc acompanha o order by da consulta. Sem ele o índice ainda é usado, mas o Postgres precisa percorrê-lo ao contrário; com ele, a leitura é direta.

Sempre CONCURRENTLY em produção
Um create index comum trava escritas na tabela durante toda a criação. Em 900 mil linhas isso é uma indisponibilidade. O concurrently demora mais e não bloqueia — mas não pode rodar dentro de transação, o que significa configuração extra em algumas ferramentas de migration.
Índice parcial quando o filtro é sempre o mesmo.

Se a tela só mostra pendentes e eles são 10% da tabela, o índice não precisa indexar os outros 90%:
```
create index concurrently pedidos_pendentes_idx
  on pedidos (criado_em desc)
  where status = 'pendente';
```
Fica menor, cabe melhor em memória e torna as escritas de pedidos já concluídos mais baratas — elas não mexem neste índice.
Incluir as colunas lidas, quando valer a pena.

Mesmo achando a linha pelo índice, o Postgres vai à tabela buscar as demais colunas. INCLUDE guarda esses valores no próprio índice e evita a segunda visita:
```
create index concurrently pedidos_status_criado_em_cobrindo_idx
  on pedidos (status, criado_em desc)
  include (cliente_id, total_cents);
```
Custo: índice maior e escrita mais cara. Vale quando a consulta é muito frequente e lê poucas colunas — exatamente o caso de uma listagem.
Matar o count(*) da paginação.

Este é o custo escondido de quase toda tela paginada. select count(*) from pedidos where status = 'pendente' percorre todas as linhas que batem, e nenhum índice evita isso.

Para “página 1 de 1.824”, troque por paginação por cursor:
consultas/pedidos-pagina.sql
```
-- Primeira página
select id, cliente_id, total_cents, criado_em
from pedidos
where status = 'pendente'
order by criado_em desc, id desc
limit 50;

-- Próxima página: continua de onde parou, sem offset
select id, cliente_id, total_cents, criado_em
from pedidos
where status = 'pendente'
  and (criado_em, id) < ($1, $2)   -- último par da página anterior
order by criado_em desc, id desc
limit 50;
```
O id no desempate impede que linhas com o mesmo timestamp sejam puladas ou repetidas. E o custo passa a ser constante — a página 1.800 é tão rápida quanto a primeira, ao contrário do offset, que precisa contar e descartar tudo que veio antes.

Quando o total precisa mesmo aparecer, uma estimativa costuma bastar:
```
select reltuples::bigint as aproximado
from pg_class where relname = 'pedidos';
```

Como confirmar que resolveu

O mesmo EXPLAIN, agora:

psql · plano depois do índice

Limit  (cost=0.42..12.71 rows=50 width=32)
       (actual time=0.038..0.121 rows=50 loops=1)
  ->  Index Scan using pedidos_status_criado_em_idx on pedidos
        (cost=0.42..21764.18 rows=88568 width=32)
        (actual time=0.036..0.113 rows=50 loops=1)
        Index Cond: (status = 'pendente'::text)
        Buffers: shared hit=54
Planning Time: 0.198 ms
Execution Time: 0.149 ms

Index Scan no lugar de Seq Scan, o passo de Sort desapareceu, 54 páginas em vez de 34 mil, e 0,15 ms em vez de 406 ms. Duas mil e setecentas vezes mais rápido.

O índice está sendo usado de verdade:

select indexrelname, idx_scan, idx_tup_read
from pg_stat_user_indexes
where relname = 'pedidos'
order by idx_scan desc;

idx_scan em zero depois de horas de tráfego significa índice que só custa escrita. Considere remover.

Reveja o ranking depois de um dia:

select pg_stat_statements_reset();
-- espere o tráfego normal, e rode a consulta do início de novo

A consulta que respondia por 71% precisa sair do topo. Se outra assumiu o lugar, o processo recomeça — e agora você já sabe como.

Armadilhas que sobram depois disso

Índice demais é pior que índice de menos. Cada insert e update atualiza todos os índices da tabela. Um índice não usado é custo puro em toda escrita.

Função na coluna anula o índice. where lower(email) = $1 não usa índice em email. Ou você indexa a expressão — create index on usuarios (lower(email)) — ou usa a coluna crua.

Tipo diferente também anula. Comparar varchar com text, ou bigint com número literal fora de faixa, faz o planejador converter e abandonar o índice. Seq Scan num filtro que deveria ser indexado costuma ser isto.

O plano depende dos dados. Com poucas linhas, o Postgres escolhe Seq Scan de propósito, porque ler tudo é mais barato que consultar índice. Testar otimização num banco de desenvolvimento com mil linhas não prova nada.

statement_timeout continua sendo boa ideia. Ele não é o problema, é a proteção. Depois de corrigir a consulta, mantenha o limite — ele vai avisar da próxima.