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

Do índice à linha

O índice diz-te onde está a linha. Ir buscá-la é a outra metade do trabalho — e é quase sempre a metade cara. Há três formas de a fazer, e o EXPLAIN diz qual foi.

1. Objetivo

No fim consegues distinguir Index Scan, Bitmap Heap Scan e Index Only Scan num plano, dizer porque é que o planeador escolheu aquele, e — dada uma query lenta servida por índice — decidir se vale a pena transformá-la num index-only scan e medir se resultou.

2. Recuperar

⭐ De memória, antes de ler

Falhar aqui é informação útil, não fracasso.

O que está guardado numa folha da B-tree?
Resposta
A chave e um ponteiro para a linha na tabela (o TID: página X, posição Y). As restantes colunas não estão lá.
Um índice com level = 2 custa quantas leituras para localizar uma chave?
Resposta
Três — níveis 2, 1 e 0. Medimos exatamente Buffers: shared read=3 no Bitmap Index Scan.
Na moeda do planeador, ler 3000 páginas ao calhas custa mais ou menos do que ler 8348 em sequência?
Resposta
Mais: 3000 × 4 = 12 000 contra 8348 × 1 = 8348. É esta conta que decide se um índice é usado — e é o coração da lição 3.

3. O mecanismo

O problema: o índice está ordenado, a tabela não

As folhas dão-te os TIDs por ordem de chave. Mas as linhas estão na tabela por ordem de inserção. Logo, percorrer as folhas por ordem e ir buscando cada linha significa saltar pela tabela ao calhas — e a lição 0 já disse quanto isso custa: 4× por página.

É daqui que saem as três estratégias. Todas resolvem o mesmo problema; diferem em como.

1 · `Index Scan` — uma linha de cada vez

Percorre as folhas por ordem e, para cada TID, vai imediatamente à tabela buscar a linha.

Bom quando há poucas linhas, ou quando a ordem do índice é útil por si (ORDER BY, LIMIT) — devolve já ordenado, sem custo de arranque.
Mau quando há muitas linhas dispersas: cada uma é um salto, e a mesma página da tabela pode ser visitada várias vezes.

2 · `Bitmap Heap Scan` — recolher primeiro, ler depois

Duas fases, e a segunda é onde está a esperteza:

Bitmap Index Scan: percorre o índice e, em vez de ir à tabela, constrói um mapa de bits das páginas que vai precisar.
Bitmap Heap Scan: lê essas páginas por ordem física crescente, cada uma uma só vez.

Trocou-se acesso aleatório por acesso quase sequencial. Foi por isto que o plano da lição 1 apareceu como Bitmap Heap Scan e não Index Scan: 20 linhas em 20 páginas diferentes valem mais lidas por ordem.

⭐ Recheck Cond não é desconfiança

No plano aparece quase sempre Recheck Cond. O mapa de bits pode degradar-se: se as páginas forem tantas que não caiba um bit por linha (work_mem esgotado), o Postgres passa a marcar páginas inteiras. Aí lê a página e tem de voltar a aplicar a condição linha a linha. Quando o mapa é exato, o EXPLAIN diz Heap Blocks: exact=N; quando degradou, diz lossy=N — e ver lossy é um sinal concreto de que aumentar work_mem pode ajudar.

3 · `Index Only Scan` — não ir à tabela de todo

Se todas as colunas que a query precisa estão no índice, não é preciso ir buscar a linha: a resposta está na folha. Deixa de haver segunda metade.

Só que há uma complicação, e é ela que torna esta a parte da lição que mais gente percebe mal:

⚠️ O índice não sabe se a linha é visível para ti

O Postgres é multiversão: uma linha apagada ou atualizada continua fisicamente na tabela até o VACUUM passar, e a informação sobre quem a pode ver está na tabela, não no índice. Então um index-only scan teria de ir à tabela na mesma — o que anularia a ideia toda.

A saída é o visibility map: um bitmap minúsculo, com dois bits por página da tabela, que marca as páginas onde todas as linhas são visíveis para toda a gente. Se a página estiver marcada, o índice chega. Se não estiver, é preciso ir lá — e isso aparece no plano como Heap Fetches.

Daqui sai a consequência prática mais importante desta lição: um index-only scan numa tabela com escrita recente e sem VACUUM deixa de ser «only». O plano continua a dizer Index Only Scan — é o Heap Fetches que denuncia. Vais medir isso já a seguir.

A diferença entre as três está toda na seta do meio: quantas vezes se toca na tabela, e por que ordem.

4. Exemplo trabalhado

Vamos fazer um index-only scan, parti-lo de propósito, e repará-lo — medindo em cada passo.

Passo 1 — pedir só o que está no índice

Porquê isto primeiro: é a condição necessária. Se a query pedir uma coluna que não está no índice, não há conversa possível.

EXPLAIN (ANALYZE, BUFFERS) SELECT cliente_id FROM encomendas WHERE cliente_id = 42;

Index Only Scan using idx_cliente on encomendas (actual time=0.029..0.029 rows=20)
  Heap Fetches: 0
  Buffers: shared hit=4
Execution Time: 0.044 ms

4 páginas e Heap Fetches: 0. Três são os níveis da árvore; a quarta é a meta-página do índice. A tabela não foi tocada.

Passo 2 — pedir uma coluna a mais e ver o plano mudar

Porquê agora: para mostrar que não é a query nem o índice que mudam de natureza — muda uma coluna na lista do SELECT.

EXPLAIN (ANALYZE, BUFFERS) SELECT cliente_id, total_cents FROM encomendas WHERE cliente_id = 42;

Bitmap Heap Scan on encomendas (actual time=0.012..0.053 rows=20)
  Buffers: shared hit=23
  ->  Bitmap Index Scan on idx_cliente (actual time=0.006..0.006 rows=20)
        Buffers: shared hit=3

De 4 páginas para 23. total_cents não está no índice, logo foi preciso ir buscar as 20 linhas a 20 páginas da tabela. Uma coluna no SELECT multiplicou o I/O por seis.

Passo 3 — partir o index-only scan de propósito

Porquê: porque em produção isto acontece sozinho, e quem nunca o viu não o reconhece.

UPDATE encomendas SET total_cents = total_cents WHERE cliente_id = 42;
EXPLAIN (ANALYZE, BUFFERS) SELECT cliente_id FROM encomendas WHERE cliente_id = 42;

Index Only Scan using idx_cliente on encomendas (actual time=0.104..0.107 rows=20)
  Heap Fetches: 40
  Buffers: shared hit=25
Execution Time: 0.122 ms

🔴 Continua a dizer «Index Only Scan» e já não é

4 páginas → 25. Heap Fetches: 0 → 40. O UPDATE criou versões novas das linhas, as páginas afetadas deixaram de estar marcadas como todas-visíveis, e o index-only scan teve de ir à tabela — 40 vezes para 20 linhas, porque ficaram lá as duas versões de cada uma.

Repara que um UPDATE que não mudou nenhum valor (total_cents = total_cents) foi suficiente. O nome do nó no plano manteve-se. É o Heap Fetches que diz a verdade, não o título do nó.

Passo 4 — reparar

VACUUM (ANALYZE) encomendas;
EXPLAIN (ANALYZE, BUFFERS) SELECT cliente_id FROM encomendas WHERE cliente_id = 42;

Index Only Scan using idx_cliente on encomendas (actual time=0.022..0.023 rows=20)
  Heap Fetches: 0
  Buffers: shared hit=4
Execution Time: 0.035 ms

O VACUUM voltou a marcar as páginas no visibility map. Quanto da tabela está marcado vê-se assim:

SELECT relpages, relallvisible, round(100.0*relallvisible/relpages,1) AS pct
FROM pg_class WHERE relname = 'encomendas';

 relpages | relallvisible |  pct
----------+---------------+-------
     8348 |          8328 |  99.8

Quando esta percentagem cai, os index-only scans dessa tabela degradam-se em silêncio. É um número que vale a pena olhar antes de culpar a query.

5. Exemplo com lacunas

Completa

Dado. Índice sobre encomendas (pais). A query é SELECT pais, count(*) FROM encomendas GROUP BY pais.

Passo 1 (completa). Que colunas precisa a query? ______ . Estão todas no índice? ______ .

Passo 2 (dado). O plano é Index Only Scan com Heap Fetches: 0.

Passo 3 (completa). Agora a query passa a SELECT pais, sum(total_cents) FROM encomendas GROUP BY pais. O plano ainda pode ser index-only? ______ Porquê? ______

Passo 4 (completa). Que índice tornaria a segunda query index-only? E que preço é que isso tem?

Ver os passos em falta

Passo 1: só pais — o count(*) não precisa de ler colunas, só de contar entradas. Está no índice. Por isso index-only.

Passo 3: não. sum(total_cents) precisa dos valores de total_cents, que não estão num índice sobre (pais). Tem de ir à tabela buscar cada linha.

Passo 4: um índice que cubra as duas colunas, e há duas formas:

CREATE INDEX ON encomendas (pais, total_cents); — as duas fazem parte da chave.
CREATE INDEX ON encomendas (pais) INCLUDE (total_cents); — total_cents viaja só nas folhas, sem entrar na ordenação. É mais pequeno e não sugere procuras que não serve.

O preço: o índice fica maior (mais bytes por entrada → mais páginas → mais I/O para o percorrer) e cada escrita em total_cents passa a ter de o atualizar. Um índice de cobertura troca custo de leitura por custo de escrita e espaço — a conta faz-se na lição 6.

6. Erros comuns

A ideia errada	Como se reconhece	O que é mesmo
«Diz `Index Only Scan`, logo não vai à tabela»	Dar a query por resolvida ao ler o nome do nó, sem olhar para mais nada	O nome do nó é a estratégia; `Heap Fetches` é o resultado. Com `Heap Fetches: 40` foi à tabela 40 vezes. Sempre que leres «Index Only», procura logo essa linha
«`Bitmap Heap Scan` é pior que `Index Scan`»	Tentar forçar `Index Scan` por parecer «mais direto»	O bitmap existe precisamente para ser melhor quando há muitas linhas dispersas: lê cada página uma vez e por ordem física. O planeador escolhe-o quando a conta lhe dá vantagem
«`Recheck Cond` significa que algo correu mal»	Alarme ao ver a palavra no plano	Aparece em quase todos os bitmap scans e é normal. O que merece atenção é `Heap Blocks: lossy=N`, que indica mapa degradado por falta de `work_mem`
«Cobrir mais colunas é sempre bom»	`INCLUDE` com meia tabela lá dentro	Cada coluna coberta engorda o índice e acrescenta trabalho a cada escrita. Cobre-se o que uma query quente precisa, não tudo o que possa vir a dar jeito
«O `VACUUM` é só para libertar espaço»	Índices-only lentos sem explicação aparente	É também o que mantém o visibility map atualizado. Sem ele, os index-only scans degradam-se sem que o plano mude de nome

7. Praticar

●○○

E1 — ler três planos

Para cada excerto, diz qual das três estratégias foi usada e quantas páginas da tabela foram lidas:

Index Only Scan … Heap Fetches: 0 … Buffers: shared hit=4
Bitmap Heap Scan … Heap Blocks: exact=20 … Buffers: shared hit=23
Index Only Scan … Heap Fetches: 40 … Buffers: shared hit=25

Solução

Index-only a sério. Zero páginas da tabela; as 4 são todas do índice.
Bitmap. 20 páginas da tabela (exact=20), mais 3 do índice = 23.
Index-only degradado. Foi à tabela 40 vezes; das 25 páginas, 3 ou 4 são do índice e o resto da tabela. Diagnóstico: falta VACUUM, ou a tabela tem escrita constante e o visibility map nunca fica em dia.

●●○

E2 — código que corre: construir um índice de cobertura

Faz com que SELECT cliente_id, criada_em FROM encomendas WHERE cliente_id = 42 seja index-only. Mede antes e depois com BUFFERS. Depois responde: o índice que criaste serve também SELECT cliente_id, total_cents FROM encomendas WHERE cliente_id = 42? Porquê?

Solução

CREATE INDEX idx_cob ON encomendas (cliente_id, criada_em);
EXPLAIN (ANALYZE, BUFFERS) SELECT cliente_id, criada_em FROM encomendas WHERE cliente_id = 42;

Corrido em Postgres 17.11, o plano passa a Index Only Scan using idx_cob com Heap Fetches: 0 — contra as 23 páginas do bitmap scan anterior.

Não serve a segunda query. total_cents não está no índice, logo volta a ser preciso ir à tabela. É a limitação central da cobertura: ela é por conjunto de colunas, não por tabela. Cobrir tudo resolveria tudo — e transformaria o índice numa segunda cópia da tabela, com todo o custo de escrita que isso traz (lição 6).

●●●

E3 — reproduzir a degradação e explicá-la

Reproduz o passo 3 do exemplo trabalhado: parte um index-only scan com um UPDATE que não muda valor nenhum. Depois explica porque é que Heap Fetches foi 40 e não 20, se as linhas são 20.

Solução

Porque o Postgres não altera linhas no sítio: um UPDATE escreve uma versão nova e deixa a antiga lá até ao VACUUM. Ficaram duas versões de cada uma das 20 linhas, e o índice ganhou entradas a apontar para ambas.

Ao percorrer o índice, o scan encontra 40 ponteiros. Como as páginas envolvidas deixaram de estar marcadas no visibility map, tem de ir à tabela verificar cada um — e é só lá que descobre que metade aponta para versões que já não deve devolver. Daí 40 Heap Fetches para 20 linhas.

⭐ A lição maior: em Postgres, uma tabela com muitos UPDATEs tem índices com entradas a mais e um visibility map desatualizado. Os dois efeitos empurram na mesma direção, e nenhum aparece no nome do nó do plano.

8. Quiz

9. Explica por palavras tuas

Explica a diferença entre «o índice encontrou a linha» e «a base de dados leu a linha», e porque é que a segunda parte é quase sempre a cara. Sem usar «TID», «heap», «bitmap» nem «visibility map». Uma analogia com um catálogo e um armazém costuma funcionar — mas diz também onde a analogia parte.

Um colega diz que a query dele está resolvida porque o plano já diz Index Only Scan. O que lhe perguntas a seguir, e porquê? Uma pergunta só. Escolhe a que mais depressa distingue «resolvido» de «parece resolvido».

Entregar

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

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

10. Resumo

A folha dá o ponteiro; ir buscar a linha é a outra metade, e normalmente a cara.
Index Scan: linha a linha, mantém a ordem — bom com LIMIT. Bitmap Heap Scan: recolhe as páginas e lê-as por ordem física, uma vez cada.
Index Only Scan: não vai à tabela — se todas as colunas estiverem no índice e as páginas estiverem marcadas no visibility map.
🔴 Heap Fetches é que diz a verdade, não o nome do nó. Sobe com escrita recente e desce com VACUUM.
Cobrir colunas (INCLUDE) compra leituras com escritas e espaço.

11. Fontes

Documentação do PostgreSQL 17 — Index-Only Scans and Covering Indexes. indexes-index-only-scans.html — fonte primária para o visibility map, o papel do VACUUM e a cláusula INCLUDE. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Visibility Map. storage-vm.html — o que saltar: é curtíssimo; lê-se inteiro em dois minutos. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Using EXPLAIN. using-explain.html — a secção sobre bitmap scans explica exact e lossy. 🌐 Verificado a 2026-09-15
Todos os planos desta lição foram corridos em PostgreSQL 17.11 sobre a tabela de preparar.sql, incluindo a degradação por UPDATE e a recuperação por VACUUM. ✅ Corrido a 2026-09-15

Do índice à linha

1. Objetivo

2. Recuperar

3. O mecanismo

O problema: o índice está ordenado, a tabela não

1 · Index Scan — uma linha de cada vez

2 · Bitmap Heap Scan — recolher primeiro, ler depois

3 · Index Only Scan — não ir à tabela de todo

4. Exemplo trabalhado

Passo 1 — pedir só o que está no índice

Passo 2 — pedir uma coluna a mais e ver o plano mudar

Passo 3 — partir o index-only scan de propósito

Passo 4 — reparar

5. Exemplo com lacunas

Completa

6. Erros comuns

7. Praticar

E1 — ler três planos

E2 — código que corre: construir um índice de cobertura

E3 — reproduzir a degradação e explicá-la

8. Quiz

9. Explica por palavras tuas

Entregar

10. Resumo

11. Fontes

1 · `Index Scan` — uma linha de cada vez

2 · `Bitmap Heap Scan` — recolher primeiro, ler depois

3 · `Index Only Scan` — não ir à tabela de todo