Lição 3 · núcleo ~1 hora

Seletividade, estatísticas e correlação

«Criei o índice e o Postgres não o usa.» Quase sempre ele tem razão — e quando não tem, a causa tem nome e mede-se.

1. Objetivo

No fim consegues, perante um índice que não está a ser usado, decidir entre as três causas possíveis — o planeador ter razão, as estatísticas estarem erradas, ou a query impedir o uso do índice — e dizer qual é, com um comando de cada vez. E consegues medir na tua máquina o limiar a partir do qual deixa de compensar, em vez de citar uma percentagem que alguém escreveu num blogue.

2. Recuperar

⭐ De memória, antes de ler

A terceira pergunta é de duas lições atrás — o espaçamento é de propósito.

Que número no plano revela que um Index Only Scan foi afinal à tabela?
Resposta
Heap Fetches. O nome do nó não muda; esse número sim.
Porque é que o Bitmap Heap Scan lê as páginas por ordem física?
Resposta
Para trocar saltos aleatórios (4× mais caros) por leitura quase sequencial, e tocar cada página uma só vez.
Qual é a fórmula do custo de um Seq Scan com um filtro simples?
Resposta
relpages × 1 + reltuples × 0.01 + reltuples × 0.0025. Na tabela de treino: 8348 + 10 000 + 2500 = 20 848.

3. O mecanismo

O planeador está a fazer uma aposta

Para decidir entre índice e varredura, o planeador precisa de saber quantas linhas a query vai devolver — e precisa de o saber antes de a executar. Não pode contar; tem de estimar. Estima a partir de estatísticas que o ANALYZE recolheu de uma amostra da tabela e guardou em pg_stats.

Três colunas dessa tabela explicam quase tudo o que acontece nesta lição:

Estatística	O que é	Para que serve
`n_distinct`	Quantos valores diferentes há. Negativo = fração das linhas (−1 = todos distintos)	Estimar quantas linhas devolve um `=` num valor qualquer
`most_common_vals` / `_freqs`	Os valores mais frequentes e a fração de cada	Estimar bem os valores desequilibrados — sem isto, 97% e 1% seriam tratados da mesma maneira
`correlation`	De −1 a 1: o quanto a ordem dos valores acompanha a ordem física das linhas na tabela	Estimar quantas páginas distintas é preciso visitar — a peça que quase toda a gente esquece

Seletividade não é a mesma coisa que agrupamento

Esta é a ideia que separa perceber de decorar, e por isso vale a pena isolá-la:

⭐ Duas perguntas diferentes

Seletividade: que fração das linhas a condição deixa passar.
Agrupamento (correlação): essas linhas estão juntas em poucas páginas, ou espalhadas por todas?

Um índice só poupa I/O se as duas responderem bem. 1% das linhas espalhadas por 100% das páginas não poupa nada — e é exatamente o que medimos na lição 1 com estado = 'cancelada'.

Daí a conta que o planeador faz, e que é a tradução da lição 0:

Seq Scan: todas as páginas, em sequência → relpages × 1
Índice: altura da árvore + páginas distintas a visitar, algumas ao calhas → ~páginas_visitadas × (1 a 4)

O índice ganha enquanto páginas visitadas × fator de dispersão for menor que relpages. Não há percentagem nenhuma nesta frase — há uma comparação entre dois números que dependem dos dados.

⚠️ Corrigido a 2026-09-15 — a regra dos «20%» não sobreviveu à medição

A primeira versão desta lição dizia: «o Postgres deixa de usar o índice quando a query devolve mais de cerca de 20% das linhas». É um número que circula muito e que eu escrevi de memória, sem fonte.

Está errado. Medido na tabela de treino, em Postgres 17.11 com os valores por omissão: numa coluna com correlação ≈ 0, o planeador continuou a usar o índice até 50% das linhas, e só trocou para Seq Scan a partir de 52%. Com random_page_cost = 1.1 (o valor típico para SSD), ainda usava o índice a 60%.

Porquê a correção e não um número novo: qualquer número que eu escrevesse aqui seria o da minha máquina. O limiar é consequência de random_page_cost, do tamanho da tabela, da correlação da coluna e da largura das linhas. O que esta lição ensina é a medi-lo — E1 faz isso — e não a decorá-lo.

As três causas, e como se distinguem

Quando um índice não é usado, é uma destas. Por esta ordem de probabilidade:

O planeador tem razão. A query devolve grande parte das linhas, ou a tabela é pequena. Diagnóstico: comparar os custos dos dois planos. Se o Seq Scan é mesmo mais barato, acabou.
As estatísticas estão erradas. A estimativa rows= está muito longe do actual rows=. Diagnóstico: EXPLAIN ANALYZE e comparar os dois números. Correção: ANALYZE.
A query impede o uso do índice. Função sobre a coluna, tipos que não batem certo, LIKE '%x'. Diagnóstico: olhar para o WHERE. Correção: reescrever, ou índice de expressão (lição 6).

4. Exemplo trabalhado

Um índice que não é usado. Vamos descobrir porquê, sem adivinhar.

Passo 1 — confirmar que o índice existe e a query não o usa

Porquê primeiro: mais vezes do que parece, o índice não está na coluna que se julga.

CREATE INDEX idx_estado ON encomendas (estado);
ANALYZE encomendas;
EXPLAIN (ANALYZE) SELECT * FROM encomendas WHERE estado = 'entregue';

Seq Scan on encomendas (cost=0.00..20848.00 rows=969633 width=36)
                       (actual time=0.004..38.078 rows=970000 loops=1)
  Filter: (estado = 'entregue'::text)
  Rows Removed by Filter: 30000

Passo 2 — comparar a estimativa com a realidade antes de culpar o planeador

Porquê agora: é o teste que separa a causa 2 das outras duas, e faz-se num relance.

rows=969633 estimadas contra rows=970000 reais. Erro de 0,04%. As estatísticas estão excelentes. A causa 2 está eliminada.

Passo 3 — perguntar se o planeador tem razão

A query devolve 97% da tabela. Ler 97% das linhas através de um índice significa visitar praticamente todas as páginas, e ainda por cima em saltos, pagando a subida e descida da árvore por cima. O Seq Scan lê as mesmas páginas, em sequência, sem índice nenhum pelo meio.

Podemos forçar e medir, em vez de argumentar:

SET enable_seqscan = off;
EXPLAIN (ANALYZE) SELECT * FROM encomendas WHERE estado = 'entregue';
RESET enable_seqscan;

✅ O planeador tinha razão

Forçado a usar o índice, o plano fica mais caro — que é a resposta que queríamos. Repara na técnica: enable_seqscan = off não é uma correção, é um instrumento de diagnóstico. Serve para perguntar «e se?», nunca para deixar em produção.

Passo 4 — o mesmo índice, com um valor diferente

Porquê este passo: porque mostra que a pergunta «este índice serve?» está mal formulada. O índice é o mesmo; o que muda é o valor procurado.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM encomendas WHERE estado = 'cancelada';

Index Scan using idx_estado on encomendas (actual rows=10000)
  Buffers: shared hit=8348 read=11

Agora usa o índice — mas olha para o número de páginas: 8348, exatamente as mesmas que o Seq Scan leria. As 10 000 linhas «canceladas» (1% da tabela!) estão espalhadas por praticamente todas as páginas.

Ficou mais rápido (6 ms contra 15) porque devolve 97 vezes menos linhas e poupa o trabalho de CPU de filtrar e transportar as outras. Não poupou I/O nenhum. Se a tabela não coubesse em memória, este plano não teria sido melhor.

A causa está em pg_stats:

SELECT attname, n_distinct, most_common_vals, round(correlation::numeric,3)
FROM pg_stats WHERE tablename = 'encomendas' AND attname IN ('estado','id');

  attname   | n_distinct |       most_common_vals        | correlacao
------------+------------+-------------------------------+------------
 estado     |          3 | {entregue,pendente,cancelada} |      0.940
 id         |         -1 |                               |      1.000

⚠️ Correlação alta sem agrupamento — a armadilha deste exemplo

estado tem correlação 0,940, que parece ótimo, e no entanto as linhas estão espalhadas. Porquê? Porque a correlação mede se a ordem dos valores acompanha a ordem física — e com apenas três valores distintos, um padrão cíclico (uma «cancelada» a cada 100 linhas) dá correlação alta e agrupamento nenhum.

Lição: a correlação é um indício, não uma prova. A prova é o BUFFERS. É por isso que este curso insiste em medir em vez de inferir de uma estatística.

Passo 5 — o caso em que o planeador se engana mesmo

Até agora ele teve razão. Vamos construir o caso em que não tem, para saberes reconhecê-lo:

CREATE TABLE enviesada (id bigint, v integer);
INSERT INTO enviesada SELECT i, i%1000 FROM generate_series(1,1000000) i;
CREATE INDEX ON enviesada(v);
ANALYZE enviesada;
-- v=7 é 1/1000 das linhas. Estatísticas certas:
EXPLAIN (ANALYZE) SELECT * FROM enviesada WHERE v = 7;

Bitmap Heap Scan (cost=12.13..2546.64 rows=994) (actual rows=1000)   <- ótimo

-- agora 800 000 linhas novas, TODAS com v=7, e ninguém corre ANALYZE:
INSERT INTO enviesada SELECT 2000000+i, 7 FROM generate_series(1,800000) i;
EXPLAIN (ANALYZE) SELECT * FROM enviesada WHERE v = 7;

Bitmap Heap Scan (cost=22.29..4582.60 rows=1789) (actual rows=801000)

🔴 Estimou 1789. Eram 801 000. Erro de 448 vezes.

Com uma estimativa destas, todas as decisões a jusante ficam erradas: o planeador escolhe um bitmap scan convencido de que vai buscar mil linhas, dimensiona memória para mil, e num JOIN escolheria um nested loop que se repete 801 000 vezes em vez de uma junção por dispersão.

ANALYZE enviesada;
EXPLAIN (ANALYZE) SELECT * FROM enviesada WHERE v = 7;

Bitmap Heap Scan (cost=8898.32..28605.57 rows=798180) (actual rows=801000)   <- 0,35% de erro

Um ANALYZE resolveu. É por isto que a comparação rows= contra actual rows= é a primeira coisa a olhar em qualquer plano.

⭐ Uma subtileza que me surpreendeu ao medir

Se só o tamanho da tabela mudar (linhas novas com a mesma distribuição), o planeador aguenta-se surpreendentemente bem sem ANALYZE: ele não confia cegamente no relpages guardado, consulta o tamanho real do ficheiro e escala a estimativa. Numa tabela que cresceu 2000× sem ANALYZE, a estimativa ficou a menos de 10% do valor real.

O que ele não corrige é a distribuição. Por isso o caso perigoso não é «a tabela cresceu» — é «a mistura de valores mudou»: uma coluna de estado que passou a ter um valor dominante, uma importação que carregou tudo com a mesma data, um tenant_id novo que ficou com metade das linhas.

5. Exemplo com lacunas

Completa o diagnóstico

Dado. Tabela eventos, 20 000 000 de linhas, índice em tipo. A query SELECT * FROM eventos WHERE tipo = 'erro' faz Seq Scan e demora 40 s.

Passo 1 (dado). EXPLAIN ANALYZE mostra rows=1000000 … actual rows=1200.

Passo 2 (completa). Qual das três causas é? ______ Como se vê? ______

Passo 3 (completa). Que comando corres? ______

Passo 4 (dado). Corres, e a estimativa passa a rows=1150. O plano muda para Bitmap Heap Scan e a query demora 80 ms.

Passo 5 (completa). Terminaste? O que verificas a seguir para isto não voltar?

Ver os passos em falta

Passo 2: causa 2, estatísticas erradas. Vê-se no desencontro entre rows=1000000 e actual rows=1200 — um fator de ~830. O planeador escolheu Seq Scan de forma perfeitamente racional dada a informação que tinha: se fossem mesmo um milhão de linhas em vinte milhões, a varredura seria a escolha certa.

Passo 3: ANALYZE eventos;

Passo 5: não terminaste. Duas coisas:

Porque é que as estatísticas estavam assim? Provavelmente o autovacuum não está a chegar a esta tabela — tabelas grandes com escrita constante batem nos limiares por omissão tarde demais. Vê-se em SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='eventos';
Se a coluna for desequilibrada (um tipo raríssimo entre milhões), aumentar a resolução da amostra ajuda a manter a estimativa boa: ALTER TABLE eventos ALTER COLUMN tipo SET STATISTICS 500; seguido de ANALYZE.

Corrigir o sintoma e não a causa garante que voltas a esta query daqui a um mês.

6. Erros comuns

A ideia errada	Como se reconhece	O que é mesmo
«Acima de ~20% das linhas o índice deixa de ser usado»	Citar uma percentagem fixa; decidir sem medir	Medido nesta tabela: o índice foi usado até 50%, e até 60% com `random_page_cost=1.1`. O limiar depende da dispersão, do parâmetro e da tabela. Mede-se (E1)
«Poucas linhas devolvidas ⇒ o índice poupa I/O»	Surpresa quando 1% das linhas lê a tabela toda	Seletividade e agrupamento são coisas diferentes. 1% espalhado por 100% das páginas não poupa página nenhuma — confirma-se no `BUFFERS`
«O planeador está errado»	Chegar aí antes de comparar `rows=` com `actual rows=`	Na maioria dos casos ele está certo, ou está a decidir bem com números maus. A distinção faz-se em dois segundos e evita horas
«`enable_seqscan = off` corrigiu o problema»	Aparecer em código de aplicação ou em configuração do servidor	É um instrumento de diagnóstico: serve para perguntar «e se?». Deixá-lo ligado esconde a causa e estraga todos os outros planos da sessão
«Correlação alta ⇒ linhas agrupadas»	Confiar na estatística sem olhar para o `BUFFERS`	Com poucos valores distintos, um padrão cíclico dá correlação alta e dispersão total — `estado` tem 0,940 e lê as 8348 páginas
«A tabela cresceu, logo as estatísticas estão obsoletas»	Correr `ANALYZE` por reflexo, sem olhar para o desencontro	O planeador corrige a escala pelo tamanho real do ficheiro. O que o cega é a distribuição ter mudado

7. Praticar

●●○

E1 — código que corre: mede o limiar da tua máquina

Em vez de decorar uma percentagem, descobre a tua. Corre isto e encontra o valor de X em que o plano muda de índice para Seq Scan:

-- total_cents vai de 100 a 50000; a fração é (X-100)/49900
EXPLAIN SELECT * FROM encomendas WHERE total_cents < 25000;   -- ~50%
EXPLAIN SELECT * FROM encomendas WHERE total_cents < 26000;   -- ~52%

Depois repete com SET random_page_cost = 1.1; e explica a diferença.

Solução

Medido em Postgres 17.11, valores por omissão:

Fração das linhas	`random_page_cost` = 4	`random_page_cost` = 1.1
50%	Bitmap Heap Scan	Bitmap Heap Scan
52%	Seq Scan	Index Scan
60%	Seq Scan	Index Scan
80%	Seq Scan	Seq Scan

Porquê a diferença: random_page_cost é o preço de uma leitura ao calhas. Baixá-lo de 4 para 1,1 torna os saltos quase tão baratos como a leitura sequencial, e o índice passa a ganhar em mais casos. Num SSD isso corresponde melhor à realidade do que o 4, que foi calibrado para discos com braço mecânico.

🔴 O que este exercício ensina: não há «o limiar do Postgres». Há o limiar desta tabela, com esta dispersão, neste servidor, com esta configuração. Qualquer pessoa que te dê uma percentagem fixa está a descrever a máquina dela.

●●○

E2 — três queries, três causas

Cada uma destas faz Seq Scan apesar de haver índice em criada_em. Diz qual das três causas é, e como a confirmas com um comando:

SELECT * FROM encomendas WHERE date_part('year', criada_em) = 2025;
SELECT * FROM encomendas WHERE criada_em > '2023-01-01';
Uma tabela onde acabaste de inserir 5 milhões de linhas com datas todas do mesmo dia.

Solução

Causa 3 — a query impede. A função destrói a ordenação de que a árvore depende: não há intervalo contíguo de date_part(...) nas folhas, que guardam criada_em. Confirma-se reescrevendo como intervalo: WHERE criada_em >= '2025-01-01' AND criada_em < '2026-01-01' — se o plano mudar, era isso.
Causa 1 — o planeador tem razão. Todas as datas da tabela são posteriores a 2023-01-01, logo a condição devolve 100% das linhas. Confirma-se com SELECT count(*) FROM encomendas WHERE criada_em > '2023-01-01'; comparado com o total.
Causa 2 — estatísticas erradas. A distribuição mudou radicalmente e o ANALYZE ainda não passou. Confirma-se com EXPLAIN ANALYZE: o desencontro entre rows= e actual rows= vai ser enorme.

●●●

E3 — o caso que parece contraditório

Duas queries na mesma tabela, com o mesmo índice, devolvendo o mesmo número de linhas (10 000 em 1 000 000). Uma lê 8359 páginas; a outra lê 118. Como é isto possível, e que estatística o prevê?

Constrói o caso na base de treino e mede-o.

Solução

Possível porque o que decide é onde estão as linhas, não quantas são.

WHERE estado = 'cancelada' — 10 000 linhas, uma a cada 100, espalhadas por todas as páginas → 8359 páginas.
WHERE id BETWEEN 1 AND 10000 — 10 000 linhas consecutivas, e como id foi inserido por ordem crescente, estão em páginas contíguas → ~90 páginas (10 000 linhas ÷ ~120 linhas por página).

Medido em Postgres 17.11:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM encomendas WHERE id BETWEEN 1 AND 10000;
  Index Scan using encomendas_pkey ... Buffers: shared hit=7 read=111   (118 páginas, 2,0 ms)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM encomendas WHERE estado = 'cancelada';
  Index Scan using idx_estado ... Buffers: shared hit=2764 read=5595   (8359 páginas, 18,8 ms)

A estatística que o prevê é correlation: 1,000 para id (ordem perfeita) contra um valor sem significado útil para estado.

⭐ E é daqui que sai a razão de existir o comando CLUSTER, que reordena fisicamente a tabela segundo um índice. Não o damos neste curso — mas agora sabes que problema é que ele resolve, e que o preço é reescrever a tabela inteira e a ordem degradar-se outra vez com as escritas seguintes.

8. Quiz

9. Explica por palavras tuas

Explica porque é que «esta query só devolve 1% da tabela» não chega para saber se um índice vai ajudar. O que é que falta perguntar? Sem usar «seletividade», «correlação» nem «página». Uma analogia com procurar coisas numa casa costuma resultar.

Esta lição foi corrigida à vista: dizia «20%» e o número estava errado. O que é que isso te diz sobre afirmações com números que leres por aí — incluindo nas lições seguintes? Não é uma pergunta retórica. Diz o que vais fazer de diferente a seguir.

Entregar

Texto da entrega (só aparece se a cópia automática falhar)

Guardar em topicos/indices-btree-sql/respostas/AAAA-MM-DD.md.

✅ Fim do núcleo

Com as lições 0 a 3 já consegues diagnosticar o caso mais comum: ler um plano, perceber porque é que um índice foi ou não usado, e decidir o que fazer. Parar aqui é um fim honesto.

Antes de continuares, faz duas coisas: entrega as respostas e importa o revisao.ics. No ritmo de uma hora por semana, o que decide se isto fica sabido é o calendário, não a velocidade a que leste.

10. Resumo

O planeador estima quantas linhas saem, a partir de estatísticas amostradas por ANALYZE.
Seletividade ≠ agrupamento. 1% das linhas espalhadas por 100% das páginas não poupa I/O.
🔴 Não há percentagem fixa a partir da qual o índice deixa de ser usado. Medido aqui: 50% com os valores por omissão, 60% com random_page_cost=1.1.
Três causas para um índice não ser usado: o planeador tem razão · estatísticas erradas · a query impede. Distinguem-se comparando rows= com actual rows=.
enable_seqscan=off é diagnóstico, nunca correção. E a correlação é indício, não prova — a prova é o BUFFERS.

11. Fontes

Documentação do PostgreSQL 17 — How the Planner Uses Statistics. planner-stats-details.html — fonte primária: faz as contas de seletividade passo a passo, com most_common_vals e histogramas. Porquê: é o texto que transforma o planeador de magia em aritmética. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — pg_stats. view-pg-stats.html — o significado exato de n_distinct (incluindo os valores negativos) e de correlation. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Planner Cost Constants. runtime-config-query.html — random_page_cost e a nota sobre armazenamento sem partes móveis. 🌐 Verificado a 2026-09-15
Todas as medições desta lição — incluindo o limiar de 50%/52%, o erro de estimativa de 448× e o comportamento com random_page_cost=1.1 — foram corridas em PostgreSQL 17.11 sobre preparar.sql. ✅ Corrido a 2026-09-15