Índices e o Planejador

Conceito

O Postgres tem o planejador baseado em custo mais sofisticado entre os bancos open source, e o conjunto de tipos de índice mais rico de qualquer banco relacional. As duas coisas juntas explicam por que ele resolve tanta coisa — e por que às vezes ele "decide errado".

Os tipos de índice, e quando cada um é o certo

E dois modificadores que são exclusividade prática do Postgres:

-- ÍNDICE PARCIAL: indexa só o subconjunto que a query usa
CREATE INDEX pedidos_pendentes ON pedidos (criado_em) WHERE status = 'pendente';
-- 10 mil linhas indexadas em vez de 50 milhões. Menor, mais quente no cache,
-- e mais barato de manter na escrita.

-- ÍNDICE POR EXPRESSÃO: indexa o resultado de uma função
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);   -- usa o índice

O índice parcial é a ferramenta mais subutilizada do Postgres. Fila de trabalho, soft delete (WHERE deletado_em IS NULL), registros ativos — todos são casos onde 99% da tabela não precisa estar no índice.

Como o Postgres lê um índice: três estratégias

Index Scan          → percorre o índice, busca cada linha no heap (aleatório).
                      Bom para poucas linhas.
Bitmap Heap Scan    → coleta todos os ctid no índice, ORDENA por página,
                      e lê o heap sequencialmente. Bom para muitas linhas.
                      Pode combinar VÁRIOS índices (BitmapAnd / BitmapOr).
Index Only Scan     → responde SÓ com o índice, sem tocar o heap.
                      Exige que a Visibility Map diga "página toda visível" — ou seja,
                      DEPENDE DE VACUUM RECENTE. Ver [[4. MVCC, Tuplas e VACUUM]].

Isto é uma diferença estrutural em relação ao InnoDB: como o Postgres é heap e não índice clusterizado, o índice guarda ctid (endereço físico) e sempre precisa de um segundo acesso — a menos que o index-only scan seja possível.

-- INCLUDE: colunas carregadas no índice só para habilitar index-only scan
CREATE INDEX ON pedidos (cliente_id) INCLUDE (total, status);
-- a coluna não entra na chave (não serve para filtro/ordem), mas evita ir ao heap

O planejador: custo, não regra

O Postgres estima o custo de cada plano e escolhe o menor. As estimativas vêm de pg_statistic (populado por ANALYZE): cardinalidade, valores mais comuns (most common values), histograma, correlação física.

custo ≈ (páginas sequenciais × seq_page_cost)
      + (páginas aleatórias  × random_page_cost)
      + (linhas processadas  × cpu_tuple_cost)
      + ...

Dois parâmetros dominam a decisão "índice vs varredura":

• random_page_cost (padrão 4.0) — herança de disco mecânico. Em SSD, use 1.1. Deixar 4.0 é a causa nº 1 de "ele não usa meu índice".
• effective_cache_size — quanto ele acredita que está em cache. Alto → favorece índice.

E quando a estimativa erra por correlação entre colunas, existe remédio:

-- o planejador assume independência: P(cidade='SP' E uf='SP') = P(a)×P(b).
-- Mas cidade determina uf! A estimativa sai 100× menor que a real.
CREATE STATISTICS s_cidade_uf (dependencies, ndistinct) ON cidade, uf FROM enderecos;
ANALYZE enderecos;

Trade-offs

Todo índice é um imposto na escrita. Cada INSERT escreve em todos os índices; cada UPDATE de coluna indexada impede o HOT update (4. MVCC, Tuplas e VACUUM). Índices não usados são custo puro — e é fácil descobrir quais são (query abaixo).

GIN é caro de escrever. Um índice GIN em jsonb de uma tabela com escrita intensa pode dominar o custo do INSERT. Mitigue com fastupdate (padrão ligado, que acumula em lista pendente) e ciente de que a lista pendente é limpa pelo VACUUM.

Índice composto tem ordem e ela importa. (a, b) serve WHERE a = ?, WHERE a = ? AND b = ? e ORDER BY a, b — mas historicamente não servia WHERE b = ?. O Postgres 18 trouxe skip scan, que permite usar o índice filtrando só pela segunda coluna quando a primeira tem poucos valores distintos. Ajuda, mas não substitui a ordem correta.

Mais estatística resolve mais que mais índice. Muitos planos ruins não são falta de índice — são estimativa errada. ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 (padrão 100) e CREATE STATISTICS corrigem casos que nenhum índice novo resolveria.

O trade-off central: o planejador do Postgres é excelente quando as estatísticas descrevem a realidade e os custos descrevem o hardware. Quase todo "o planejador é burro" é, na verdade, estatística velha ou random_page_cost de 2005.

Exemplo prático

Ler um EXPLAIN de verdade

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT p.id, p.total FROM pedidos p
 WHERE p.cliente_id = 4711 AND p.status = 'pago'
 ORDER BY p.criado_em DESC LIMIT 20;

Limit  (cost=0.43..8.90 rows=20 width=20) (actual time=0.032..0.180 rows=20 loops=1)
  Buffers: shared hit=24 read=2
  ->  Index Scan using pedidos_cliente_status_criado on pedidos p
        Index Cond: ((cliente_id = 4711) AND (status = 'pago'::text))
        (actual time=0.028..0.170 rows=20 loops=1)
Planning Time: 0.21 ms
Execution Time: 0.20 ms

O que olhar, em ordem de importância:

1. rows estimado vs actual rows. Diferença de 10× ou mais = estatística ruim → é a raiz do problema, não o plano.
2. Buffers: shared hit vs read. hit é RAM, read é disco. Muito read repetido = cache pequeno ou query varrendo demais.
3. Rows Removed by Filter. Se o índice trouxe 500 mil linhas e o filtro descartou 499.980, falta uma coluna no índice.
4. loops. loops=5000 num nó interno é o 7. Problema N+1 dentro do próprio plano (nested loop mal escolhido).
5. Sort Method: external merge Disk: 42MB. O sort não caiu em work_mem e foi para disco: suba work_mem nesta sessão.

Higiene de índices

-- índices que ninguém usa: custo de escrita sem benefício de leitura
SELECT s.relname AS tabela, s.indexrelname AS indice,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamanho, s.idx_scan
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE s.idx_scan = 0 AND NOT i.indisunique
 ORDER BY pg_relation_size(s.indexrelid) DESC;

-- tabelas sofrendo seq scan em volume
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / nullif(seq_scan, 0) AS linhas_por_scan
  FROM pg_stat_user_tables
 WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10;

Como isso falha em produção

Aprofundamento aplicado

Formular uma hipótese sobre seletividade

Consulta de exemplo: últimos pedidos de um cliente, ordenados por data e ID. Um índice (cliente_id, criado_em DESC, id DESC) pode alinhar igualdade inicial e ordenação. Buscar apenas por criado_em, porém, muda o caminho e precisa de outro exame.

Não conclua que o índice é bom apenas porque apareceu no plano. Observe quantas linhas foram lidas para produzir o resultado e quanto trabalho foi repetido. Em nós com vários loops, números por execução precisam ser interpretados junto com a quantidade de chamadas. Referência: leitura de planos.

Experimento com dados desiguais

Compare um cliente com 5 pedidos e outro com 500 mil. Se a consulta retorna uma grande fração da tabela, uma leitura sequencial pode ser uma escolha racional. Estatísticas inadequadas podem esconder essa diferença do planejador.

Exercício: compare os dois clientes com EXPLAIN (ANALYZE, BUFFERS) em um ambiente de estudo. Resultado esperado: justifique o plano por seletividade, leituras e estimativa versus realidade; os custos estimados não são milissegundos.

Relacionado

• 4. MVCC, Tuplas e VACUUM
• 7. Diagnóstico e Tuning
• 2. Arquitetura de Processos e Memória
• 5. Índices
• SQL Tuning
• 7. Problema N+1
• 4. Índices e o Otimizador

---

Parte de MOC PostgreSQL · Indexes (docs)