Lição 6 · extensão ~1 hora

O custo do outro lado

Cinco lições a ver o que um índice poupa. Esta é sobre o que ele cobra — e sobre as duas ferramentas que reduzem a fatura: índices parciais e de expressão.

1. Objetivo

No fim consegues defender a decisão de não criar um índice com números, identificar os índices que uma base de dados tem a mais, e usar índices parciais e de expressão nos casos em que resolvem mais barato do que um índice completo.

2. Recuperar

Que campo do EXPLAIN denuncia trabalho desperdiçado a filtrar linhas?
Resposta
Rows Removed by Filter.
Índice sobre (a, b): torna redundante que outro índice?
Resposta
Um índice só sobre (a) — a é prefixo do composto.
Porque é que um Index Only Scan pode ir à tabela na mesma?
Resposta
Porque as páginas não estão marcadas como todas-visíveis no visibility map. Vê-se em Heap Fetches.

3. O mecanismo

Um índice é uma estrutura a mais para manter

Cada INSERT tem de inserir uma entrada em cada índice da tabela, no sítio certo da árvore — o que pode obrigar a dividir páginas. Cada UPDATE de uma coluna indexada é, na prática, uma remoção e uma inserção. Cada DELETE deixa entradas mortas até ao VACUUM.

Medido na base de treino (Postgres 17.11), a inserir 200 000 linhas numa tabela vazia:

Tabela	Índices	Tempo do `INSERT`
`t_sem`	0	182 ms
`t_com`	4	1232 ms

6,8 vezes mais lento. E o espaço vai na mesma direção: no fim, a tabela ocupava 13 MB e os índices 16 MB — os índices pesavam mais do que os dados.

⚠️ Este número é o teto, não a média

Um INSERT de 200 000 linhas de uma vez é o caso mais favorável à tabela sem índices. Numa aplicação que insere uma linha de cada vez, o custo fixo por transação dilui a diferença, e o fator é menor. O que se retém não é «6,8×» — é que existe um fator, que cresce com o número de índices, e que se mede na tua carga.

Índices parciais: indexar só o que interessa

Se as queries só procuram um subconjunto das linhas, só esse subconjunto precisa de estar no índice.

CREATE INDEX idx_pendentes ON encomendas (criada_em) WHERE estado = 'pendente';

Índice	Tamanho
`(estado)` — completo	6904 kB
`(criada_em) WHERE estado = 'pendente'`	384 kB

18 vezes mais pequeno, porque só indexa 2% das linhas. E o ganho não é só espaço: as 97% de linhas «entregues» não tocam neste índice quando são inseridas.

O caso canónico é uma fila de trabalho: WHERE processado = false. A tabela cresce para sempre; o índice fica do tamanho da fila.

Índices de expressão: indexar o resultado de uma função

A lição 3 mostrou que WHERE lower(email) = ? não usa um índice em email. A saída é indexar a própria expressão:

CREATE INDEX idx_email_min ON clientes ((lower(email)));

Duas condições, e ambas apanham gente desprevenida:

🔴 A função tem de ser IMMUTABLE

Uma função só pode ser indexada se devolver sempre o mesmo resultado para a mesma entrada. Encontrei isto ao escrever esta lição, a tentar indexar o ano de uma data:

CREATE INDEX idx_ano ON encomendas ((date_part('year', criada_em)));
ERROR:  functions in index expression must be marked IMMUTABLE

A causa: criada_em é timestamptz, e extrair o ano depende do fuso horário da sessão. A mesma linha daria 2025 em Lisboa e 2024 em São Paulo — e um índice não pode depender de quem o consulta. Confirma-se no catálogo: a função está marcada s (stable), não i (immutable).

A correção é fixar o fuso, tornando a expressão determinística:

CREATE INDEX idx_ano ON encomendas ((date_part('year', criada_em AT TIME ZONE 'UTC')));

⚠️ A query tem de usar exatamente a mesma expressão

Medido: com o índice acima, WHERE date_part('year', criada_em AT TIME ZONE 'UTC') = 2025 usa-o (11,8 ms). Mas WHERE date_part('year', criada_em) = 2025 — sem o AT TIME ZONE — volta a Parallel Seq Scan (31,4 ms). São expressões diferentes, e o planeador não as reconcilia.

É um índice fácil de criar e de deixar por usar sem ninguém dar conta.

Como encontrar os índices a mais

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
FROM pg_stat_user_indexes WHERE relname = 'encomendas' ORDER BY idx_scan;

idx_scan = 0 quer dizer que o índice nunca foi usado desde a última reposição das estatísticas. Antes de apagar, três cuidados:

Desde quando? SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); Zero scans desde ontem não diz nada.
Serve uma restrição? Um índice que suporta uma chave primária ou única não se apaga — apaga-se a restrição, se for isso que se quer.
Só uma réplica o usa? As estatísticas são por servidor. Um índice sem uso no primário pode ser o que segura os relatórios na réplica.

4. Exemplo trabalhado

Decidir se vale a pena um índice — com números, não com opinião.

O caso: tabela eventos, 5000 INSERTs por minuto. Um relatório diário corre WHERE tipo = 'erro' AND criada_em > ? e demora 8 s. Alguém propõe CREATE INDEX ON eventos (tipo, criada_em).

Passo 1 — quanto se ganha (medir, não estimar)

Porquê primeiro: se o ganho for pequeno, a discussão acaba aqui.

BEGIN;
CREATE INDEX idx_teste ON eventos (tipo, criada_em);
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
ROLLBACK;   -- o índice desaparece com a transação

⭐ A técnica vale por si: CREATE INDEX é transacional em Postgres. Podes experimentar um índice e desfazê-lo sem deixar rasto. (Numa tabela em produção com escrita, usa-se CREATE INDEX CONCURRENTLY — que não é transacional, precisamente por não bloquear.)

Digamos que o relatório passa de 8 s para 40 ms. Ganho: ~8 s por dia.

Passo 2 — quanto se perde

5000 INSERTs/min = 7,2 milhões por dia. Se cada um custar mais 20 µs por causa do índice, são 144 s por dia de trabalho extra, espalhados por todas as escritas.

Este número mede-se, não se adivinha: insere-se um lote representativo com e sem o índice, como na tabela acima. O que se está a comparar é 8 s poupados uma vez contra 144 s gastos continuamente.

Passo 3 — perguntar se há forma mais barata

Porquê antes de decidir: a escolha raramente é «índice sim ou não». Aqui:

Índice parcial: ON eventos (criada_em) WHERE tipo = 'erro'. Se os erros forem 1% dos eventos, o índice fica ~100× mais pequeno e os outros 99% dos INSERTs não lhe tocam. O custo de escrita quase desaparece.
Não indexar: se o relatório corre de madrugada e ninguém espera por ele, 8 s não são um problema. «Lento» não é o mesmo que «problema».
Materializar: se o relatório é sempre o mesmo, uma tabela-resumo atualizada uma vez por dia resolve sem tocar no caminho de escrita.

Passo 4 — decidir e escrever porquê

✅ Decisão: índice parcial

Porque dá quase todo o ganho de leitura com uma fração do custo de escrita. E porque, sendo pequeno, é barato de manter e de apagar se as queries mudarem.

A parte que se esquece: escrever a razão onde alguém a encontre — numa migração, num comentário. Daqui a um ano, quem vir um índice parcial estranho e não souber porquê, ou o apaga (e parte o relatório) ou não lhe toca por medo (e nunca mais é revisto).

5. Exemplo com lacunas

Completa a auditoria

Dado. Tabela sessoes, escrita intensa, com estes índices:

Índice	`idx_scan`	Tamanho
`sessoes_pkey (id)`	4 100 000	210 MB
`(utilizador_id)`	890 000	180 MB
`(utilizador_id, criada_em)`	1 200 000	260 MB
`(agente_utilizador)`	0	340 MB
`(expira_em)`	52	190 MB

Passo 1 (completa). Qual é redundante por definição, e porquê? ______

Passo 2 (completa). Qual é o candidato mais óbvio a apagar? ______ O que confirmas antes? ______

Passo 3 (completa). O de expira_em tem 52 usos. Apagas? ______ Porquê? ______

Passo 4 (completa). Quanto espaço poupas, e o que ganhas além do espaço? ______

Ver os passos em falta

Passo 1: (utilizador_id) — é prefixo de (utilizador_id, criada_em), portanto tudo o que serve já é servido pelo composto. 180 MB e trabalho em cada escrita, a troco de nada.

Passo 2: (agente_utilizador): zero usos e o maior de todos, 340 MB. Antes de apagar, confirma-se stats_reset (zero desde ontem não é zero), e se alguma réplica o usa.

Passo 3: Não, sem investigar. 52 usos podem ser um trabalho de limpeza que corre uma vez por dia — e que sem o índice passa a varrer a tabela inteira. Um índice pouco usado pode ser o que impede um incidente noturno. A pergunta certa não é «quantas vezes?», é «quem o usa, e o que acontece sem ele?».

Passo 4: 520 MB (340 + 180). Além do espaço: duas árvores a menos para manter em cada INSERT numa tabela de escrita intensa, menos páginas a disputar a cache (o que melhora os outros índices), e menos trabalho para o VACUUM.

6. Erros comuns

A ideia errada	Como se reconhece	O que é mesmo
«Um índice a mais não faz mal»	Dez índices numa tabela de escrita	Medido: 4 índices → `INSERT` 6,8× mais lento, e índices a pesar mais que a tabela
«`idx_scan = 0`, apaga-se»	Apagar sem ver `stats_reset`	Zero desde ontem não é zero. E pode ser o índice de uma limpeza semanal, ou usado só na réplica
«Criei o índice de expressão, está resolvido»	Não verificar o plano depois	A query tem de usar a expressão literalmente igual. Medido: sem o `AT TIME ZONE`, volta a `Seq Scan`
«Qualquer função pode ser indexada»	`ERROR: functions in index expression must be marked IMMUTABLE`	Só funções determinísticas. `now()`, `random()` e conversões dependentes de fuso ou de locale não servem
«Índices parciais são um caso de nicho»	Nunca os usar	Filas de trabalho, soft deletes, estados raros — são comuníssimos, e a poupança é grande (18× aqui)
«A query está lenta, logo precisa de índice»	Índice como primeira resposta	Às vezes a resposta é não indexar: um relatório noturno de 8 s pode não ser um problema. «Lento» ≠ «problema»

7. Praticar

●○○

E1 — parcial ou completo?

Para cada caso, diz se farias índice completo, parcial (com que condição), ou nenhum:

Fila: WHERE processado = false, 0,1% das linhas, tabela cresce para sempre.
Soft delete: quase todas as queries têm WHERE apagado_em IS NULL; 5% apagadas.
WHERE pais = ?, cinco países com ~20% cada.

Solução

Parcial, WHERE processado = false. O caso canónico: o índice fica do tamanho da fila em vez do tamanho da história, e as linhas já processadas deixam de lhe tocar.
Parcial, WHERE apagado_em IS NULL, acrescentando as colunas que as queries usam. Poupa só 5% do tamanho — mas o Postgres passa a poder usar o índice sem revalidar a condição, porque ela faz parte da definição. O ganho é mais em planos do que em bytes.
Provavelmente nenhum. 20% por país é pouco seletivo e, pela lição 3, se as linhas estiverem dispersas o índice não poupa páginas. Mede-se com BUFFERS antes de decidir. Se houvesse um país com 0,5%, um índice parcial só para esse faria sentido.

●●○

E2 — código que corre: mede o custo de escrita da tua máquina

Não aceites o 6,8×. Mede o teu:

CREATE TABLE t_sem (LIKE encomendas);
CREATE TABLE t_com (LIKE encomendas);
CREATE INDEX ON t_com(cliente_id); CREATE INDEX ON t_com(estado);
CREATE INDEX ON t_com(total_cents); CREATE INDEX ON t_com(cliente_id, criada_em);
\timing on
INSERT INTO t_sem SELECT * FROM encomendas LIMIT 200000;
INSERT INTO t_com SELECT * FROM encomendas LIMIT 200000;
SELECT pg_size_pretty(pg_table_size('t_com')), pg_size_pretty(pg_indexes_size('t_com'));
DROP TABLE t_sem, t_com;

Depois repete com um índice em vez de quatro. O custo cresce de forma proporcional?

Solução

Medido em Postgres 17.11: 182 ms sem índices, 1232 ms com quatro — 6,8×. Tabela 13 MB, índices 16 MB.

Com um índice o custo não é ¼ do de quatro: há um custo fixo por linha que não depende dos índices (escrever na tabela, no WAL), e os índices variam bastante entre si — um sobre estado (3 valores, muito deduplicado) é mais barato de manter do que um sobre total_cents (50 000 valores distintos, entradas espalhadas por toda a árvore).

⭐ É por isso que a pergunta útil nunca é «quantos índices tenho?», mas «este índice, nesta coluna, paga-se?».

●●●

E3 — o índice de expressão que não é usado

Reproduz a armadilha: cria o índice sobre date_part('year', criada_em) e vê o erro. Corrige com AT TIME ZONE. Depois mostra que a query sem o AT TIME ZONE não o usa. Por fim: que solução é melhor do que o índice de expressão, para este caso concreto?

Solução

O erro e a correção estão no mecanismo, acima. Medido: com a expressão exata, 11,8 ms (Bitmap Index Scan on idx_ano); sem o AT TIME ZONE, 31,4 ms (Parallel Seq Scan).

A solução melhor: não usar índice de expressão nenhum. Reescrever a query como intervalo sobre a coluna:

WHERE criada_em >= '2025-01-01' AND criada_em < '2026-01-01'

Porque é melhor:

Usa um índice normal sobre criada_em, que provavelmente já existe e serve muitas outras queries — em vez de mais uma árvore só para esta.
Não depende de quem escreve a query acertar na expressão exata.
Não tem o problema do fuso: a comparação é feita sobre o valor, não sobre uma extração dele.

🔴 O índice de expressão é a ferramenta certa quando a expressão é irredutível — lower(email) para comparações sem maiúsculas, por exemplo. Não é a ferramenta certa para contornar uma query que podia ser reescrita como intervalo.

8. Quiz

9. Explica por palavras tuas

Explica a alguém que quer «indexar tudo por precaução» porque é que isso é má ideia. Usa números, não princípios. E não digas só «ocupa espaço» — o espaço é o menor dos custos.

Este curso acabou. Descreve, em cinco passos e por palavras tuas, o que farias perante uma query lenta numa base de dados que não conheces. Esta é a resposta que mostra se o curso funcionou. Escreve-a sem consultar as lições — e se te faltar um passo, deixa lá escrito que te faltou. Isso diz-me onde voltar.

Entregar

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

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

✅ Acabaste o curso

Falta o que decide se fica sabido: o teste de domínio (intercalado, entrega-se para correção) e o revisao.ics no calendário. A folha de consulta imprime-se e fica ao lado do teclado.

10. Resumo

Medido: 4 índices → INSERT 6,8× mais lento; índices a ocupar mais do que a tabela (16 MB contra 13 MB).
Índices parciais (WHERE …) indexam só o subconjunto útil — 18× mais pequenos aqui. Filas e soft deletes são o caso natural.
Índices de expressão exigem função IMMUTABLE e que a query use a expressão literalmente igual. Muitas vezes reescrever a query como intervalo é melhor.
pg_stat_user_indexes.idx_scan encontra os índices a mais — mas confirma stats_reset, restrições e réplicas antes de apagar.
BEGIN; CREATE INDEX; EXPLAIN ANALYZE; ROLLBACK; testa um índice sem o deixar. E 🔴 «lento» não é o mesmo que «problema».

11. Fontes

Documentação do PostgreSQL 17 — Partial Indexes. indexes-partial.html — fonte primária. Porquê: os exemplos são bons e mostram o caso em que o índice parcial também serve para impor unicidade condicional. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Indexes on Expressions. indexes-expressional.html — e o aviso de que são mais caros de manter, porque a expressão é calculada em cada escrita. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Function Volatility Categories. xfunc-volatility.html — IMMUTABLE, STABLE e VOLATILE, e porque só a primeira pode ser indexada. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — The Statistics Collector (pg_stat_user_indexes). monitoring-stats.html — o significado de idx_scan e de stats_reset. 🌐 Verificado a 2026-09-15
Todas as medições (182 ms contra 1232 ms; 13 MB contra 16 MB; 384 kB contra 6904 kB; 11,8 ms contra 31,4 ms) foram corridas em PostgreSQL 17.11 sobre preparar.sql. O erro de IMMUTABLE foi encontrado a escrever esta lição, não previsto. ✅ Corrido a 2026-09-15