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

Índices compostos e a ordem das colunas

As mesmas duas colunas, por ordens diferentes, dão índices com utilidades diferentes. E o plano de execução esconde a diferença melhor do que devia.

1. Objetivo

No fim consegues, dado um conjunto de queries, decidir a ordem das colunas de um índice composto e justificar a escolha. E consegues reconhecer, num plano que menciona o teu índice, se ele está mesmo a ser aproveitado ou apenas a ser varrido do princípio ao fim.

2. Recuperar

Que estrutura têm as folhas de uma B-tree, e o que guardam?
Resposta
Estão ordenadas por chave e ligadas em cadeia; guardam a chave e o ponteiro (TID) para a linha.
Porque é que WHERE date_part('year', criada_em) = 2025 não usa um índice em criada_em?
Resposta
A função destrói a ordenação: os valores de date_part(...) não formam um intervalo contíguo nas folhas, que guardam criada_em.
Qual é a primeira coisa a olhar num EXPLAIN ANALYZE?
Resposta
O desencontro entre rows= e actual rows=.

3. O mecanismo

Um índice composto é uma lista telefónica

Um índice sobre (apelido, nome) ordena primeiro por apelido e, dentro de cada apelido, por nome. É exatamente uma lista telefónica — e a analogia leva-te longe:

Procurar «Silva» → fácil: estão todos juntos.
Procurar «Silva, Ana» → fácil: dentro dos Silva, os nomes estão ordenados.
Procurar toda a gente chamada «Ana», de qualquer apelido → tens de ler a lista inteira. As Anas estão espalhadas por todos os apelidos.

⭐ A regra do prefixo mais à esquerda

Um índice sobre (a, b, c) serve procuras por a, por (a, b) e por (a, b, c). Não serve procuras que não fixem a.

O porquê é a ordenação, não uma regra arbitrária: sem fixar a, os valores de b que te interessam não formam um intervalo contíguo nas folhas — e percorrer um intervalo contíguo é a única coisa que uma B-tree sabe fazer depressa.

A ordem das colunas decide qual das procuras fica contígua — e só a contígua é barata.

Como escolher a ordem

Três critérios, por ordem de importância:

A coluna usada com igualdade (=) vem primeiro. Fixar um valor exato deixa o resto do índice ordenado e utilizável. Uma coluna usada com intervalo (>, BETWEEN) «gasta» a ordenação: depois dela, as colunas seguintes já não estão ordenadas dentro do que sobrou.
Depois, a coluna do intervalo ou do ORDER BY. É aí que a cadeia de folhas compensa.
A seletividade decide empates, e só empates. É o critério que mais gente usa primeiro, e é o menos importante dos três.

Daí o padrão que resolve a maioria dos casos reais — (igualdade, intervalo): (cliente_id, criada_em) para «as encomendas deste cliente, das mais recentes para as mais antigas».

⚠️ Um índice composto não substitui dois índices — nem o contrário

(a, b) serve as procuras por a, portanto torna um índice separado em a redundante (e esse deve ser removido — ocupa espaço e custa em cada escrita). Mas não serve as procuras só por b: para essas, ou crias um índice em b, ou aceitas que não há.

4. Exemplo trabalhado

Vamos criar o índice com a ordem errada e ver o que o plano diz — porque o que ele diz é enganador.

Passo 1 — criar a ordem errada, de propósito

A query que nos interessa é WHERE cliente_id = 42. Criamos o índice ao contrário:

CREATE INDEX idx_ec ON encomendas (estado, cliente_id);
ANALYZE encomendas;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM encomendas WHERE cliente_id = 42;

Index Scan using idx_ec on encomendas  (actual time=0.040..1.887 rows=20 loops=1)
  Index Cond: (cliente_id = 42)
  Buffers: shared hit=1 read=1002
Execution Time: 1.901 ms

🔴 Lê com atenção: isto parece bom e não é

O plano diz Index Scan. Diz o nome do índice. Até diz Index Cond: (cliente_id = 42). Quem olhe de relance dá a query por resolvida.

Mas olha para os Buffers: 1003 páginas. O índice inteiro tem ~1005. Ele varreu o índice do princípio ao fim, a testar cliente_id = 42 em cada entrada — porque sem fixar estado (a primeira coluna) não há bloco contíguo onde descer.

Fez isso porque continua a compensar face à alternativa: varrer 1005 páginas de índice é melhor do que varrer 8348 páginas de tabela. É um plano racional e péssimo ao mesmo tempo.

Passo 2 — a ordem certa, a mesma query

DROP INDEX idx_ec;
CREATE INDEX idx_ce ON encomendas (cliente_id, estado);
ANALYZE encomendas;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM encomendas WHERE cliente_id = 42;

Bitmap Heap Scan on encomendas  (actual time=0.015..0.015 rows=20 loops=1)
  Buffers: shared hit=1 read=3
  ->  Bitmap Index Scan on idx_ce  (actual time=0.010..0.010 rows=20 loops=1)
        Index Cond: (cliente_id = 42)
        Buffers: shared read=3

✅ 4 páginas contra 1003. 0,015 ms contra 1,9 ms.

250 vezes menos I/O, com as mesmas duas colunas, o mesmo tamanho de índice (8048 kB contra 8056 kB) e a mesma query. Só mudou a ordem na declaração do índice.

Passo 3 — a lição que fica

O nome do nó e o nome do índice no plano não dizem se o índice está a ser aproveitado. O sinal está nos Buffers: se o número de páginas lidas do índice se aproxima do tamanho total do índice, ele está a ser varrido, não procurado.

-- quantas páginas tem o índice, para comparar:
SELECT pg_relation_size('idx_ce') / 8192 AS paginas_do_indice;

É o mesmo padrão da lição 2, noutra roupagem: aí era Index Only Scan com Heap Fetches alto; aqui é Index Scan com Buffers alto. O nome descreve a intenção; os números descrevem o que aconteceu.

5. Exemplo com lacunas

Completa

Dado. Estas três queries são as quentes de uma aplicação:

WHERE cliente_id = ? ORDER BY criada_em DESC LIMIT 20
WHERE cliente_id = ? AND estado = 'pendente'
WHERE criada_em >= ? AND criada_em < ?

Passo 1 (completa). Que coluna aparece com = em mais queries? ______

Passo 2 (completa). Índice para a query 1: (______, ______). Porquê esta ordem? ______

Passo 3 (dado). Esse índice também serve a query 2, embora só parcialmente.

Passo 4 (completa). Porque é que serve «parcialmente»? O que é que o Postgres tem de fazer a mais?

Passo 5 (completa). A query 3 é servida por esse índice? Se não, o que crias?

Ver os passos em falta

Passo 1: cliente_id — com = nas queries 1 e 2.

Passo 2: (cliente_id, criada_em). A igualdade primeiro fixa um bloco contíguo; dentro dele, criada_em está ordenado, portanto o ORDER BY … DESC LIMIT 20 lê a cadeia de folhas ao contrário e para à vigésima, sem ordenar nada.

Passo 4: na query 2, o índice localiza o bloco do cliente (talvez 20 entradas) mas estado não está no índice, logo tem de ir buscar cada linha à tabela e filtrar estado = 'pendente' lá. Com ~20 linhas por cliente, é irrelevante. Se fossem 50 000 por cliente, valeria a pena (cliente_id, estado, criada_em) — que continua a servir a query 1, porque (cliente_id) continua a ser prefixo.

Passo 5: não serve — a query 3 não fixa cliente_id, e sem o prefixo não há bloco contíguo. Cria-se (criada_em). É o exemplo de duas necessidades genuinamente diferentes que precisam mesmo de dois índices: não há ordem única que sirva as três.

6. Erros comuns

A ideia errada	Como se reconhece	O que é mesmo
«O plano diz o nome do índice, logo está bem»	Dar a query por resolvida sem olhar para os `Buffers`	Um índice pode ser varrido inteiro. Compara as páginas lidas com `pg_relation_size(indice)/8192`: se forem próximas, não houve procura
«A coluna mais seletiva vai primeiro»	Ordenar por `n_distinct` sem olhar para as queries	Primeiro vem a coluna usada com `=`. A seletividade só decide empates — um índice perfeitamente ordenado por seletividade pode não servir query nenhuma
«Um índice por coluna do `WHERE`»	Cinco índices de coluna única numa tabela com escrita	Um composto com a ordem certa serve mais casos e custa menos nas escritas. E `(a,b)` torna o índice em `(a)` redundante — esse deve ser apagado
«A coluna do intervalo pode ir no meio»	`(cliente_id, criada_em, estado)` com intervalo em `criada_em` e igualdade em `estado`	Depois de um intervalo, as colunas seguintes deixam de estar ordenadas dentro do resultado. `estado` passa a ser filtro, não procura. A ordem útil seria `(cliente_id, estado, criada_em)`
«Inverter a ordem só afeta o desempenho um bocadinho»	Não testar a ordem alternativa	Medido aqui: 250× na mesma query, com índices do mesmo tamanho

7. Praticar

●○○

E1 — quais é que este índice serve?

Índice sobre (pais, estado, criada_em). Para cada condição, diz se é servida por procura (descida à árvore), por varrimento do índice, ou não é servida de todo:

WHERE pais = 'PT'
WHERE pais = 'PT' AND estado = 'pendente'
WHERE estado = 'pendente'
WHERE pais = 'PT' AND criada_em > '2025-01-01'
WHERE pais = 'PT' AND estado = 'pendente' ORDER BY criada_em

Solução

Procura. Prefixo exato.
Procura. Prefixo de duas colunas.
Não é servida por procura. Falta o prefixo pais. Na prática o planeador pode varrer o índice inteiro se isso for melhor que varrer a tabela — e aí o plano menciona o índice sem o estar a aproveitar. É o caso do exemplo trabalhado.
Procura parcial. Desce por pais; dentro desse bloco, estado é a coluna seguinte e não está fixa, portanto criada_em não está ordenado no que sobrou. A data passa a ser filtro aplicado às entradas, não intervalo percorrido.
Procura, e o melhor caso dos cinco. Fixa as duas primeiras por igualdade; dentro desse bloco criada_em está ordenado, logo o ORDER BY sai de graça.

●●○

E2 — código que corre: reproduzir os 250×

Na base de treino, cria os dois índices (um de cada vez — com ambos presentes o planeador escolhe o bom e o efeito desaparece) e mede a mesma query com BUFFERS. Depois compara as páginas lidas com o tamanho total do índice.

Solução

DROP INDEX IF EXISTS idx_ce, idx_ec;
CREATE INDEX idx_ec ON encomendas (estado, cliente_id);
ANALYZE encomendas;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM encomendas WHERE cliente_id = 42;
SELECT pg_relation_size('idx_ec')/8192 AS paginas_do_indice;

Medido em Postgres 17.11: Buffers: shared hit=1 read=1002 contra um índice de ~1005 páginas — leu-o praticamente todo. Com (cliente_id, estado): 4 páginas.

⚠️ A armadilha da experiência (e a razão do aviso no enunciado): se deixares os dois índices criados, o planeador escolhe o bom e não vês diferença nenhuma. Foi o que me aconteceu à primeira vez que corri isto — a medição deu 4 páginas para os dois casos e quase escrevi que não havia diferença.

●●●

E3 — desenhar para três queries com um orçamento de dois índices

Uma tabela mensagens (id, conversa_id, autor_id, criada_em, lida, corpo), com escrita intensa. Três queries quentes:

WHERE conversa_id = ? ORDER BY criada_em DESC LIMIT 50 (abrir uma conversa)
WHERE autor_id = ? AND lida = false (por ler, de um autor)
SELECT count(*) WHERE conversa_id = ? AND lida = false (badge de não lidas)

Propõe dois índices e justifica cada coluna e cada posição. Diz também o que sacrificaste.

Solução

Uma proposta defensável:

(conversa_id, criada_em DESC) — serve a query 1 por inteiro: igualdade primeiro, ordenação a seguir, e o LIMIT 50 para logo à quinquagésima entrada. Serve também a query 3 parcialmente: localiza o bloco da conversa, mas tem de ir à tabela verificar lida em cada linha.
(autor_id, lida) — serve a query 2. As duas são igualdades, e autor_id vem primeiro por ser muito mais seletiva (o critério 3, que aqui decide o empate entre duas igualdades).

O que se sacrificou: a query 3 não fica ótima. Um índice (conversa_id, lida) resolveria-a — mas em vez de o acrescentar, pergunta-se primeiro:

Um índice parcial (conversa_id) WHERE lida = false seria muito mais pequeno (só as não lidas) e serviria a query 3 melhor do que um índice completo — lição 6.
Ou cobrir: (conversa_id, criada_em DESC) INCLUDE (lida) torna a query 3 index-only sem criar índice novo — ao preço de engordar o primeiro.

⭐ O enunciado dizia «escrita intensa», e é isso que justifica o orçamento apertado. Cada índice é trabalho em cada INSERT: na base de treino, quatro índices tornaram um INSERT de 100 000 linhas 16 vezes mais lento (lição 6). Numa tabela de mensagens, esse custo paga-se a cada mensagem enviada.

8. Quiz

9. Explica por palavras tuas

Explica a regra do prefixo mais à esquerda usando uma analogia que não seja a lista telefónica, e diz onde a tua analogia parte. Escolher uma analogia nova obriga a perceber o mecanismo; repetir a do texto não obriga a nada.

Porque é que ver o nome do teu índice no plano não significa que ele esteja a ser aproveitado? Que número confirmas, e contra o quê o comparas? Esta ideia já apareceu na lição 2 com outro nome. Se conseguires dizer qual era, melhor.

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

Prefixo mais à esquerda: (a,b,c) serve a, (a,b), (a,b,c) — e não serve nada que não fixe a.
Ordem: igualdade primeiro, intervalo ou ORDER BY a seguir, seletividade só para desempatar.
Depois de um intervalo, as colunas seguintes deixam de estar ordenadas — por isso o intervalo vai no fim.
🔴 O nome do índice no plano não prova nada. Compara os Buffers com pg_relation_size(indice)/8192: próximos = varrimento, não procura.
(a,b) torna o índice em (a) redundante — apaga-se, e as escritas agradecem.

11. Fontes

Documentação do PostgreSQL 17 — Multicolumn Indexes. indexes-multicolumn.html — fonte primária para o comportamento do prefixo e para o limite de 32 colunas por índice. 🌐 Verificado a 2026-09-15
Documentação do PostgreSQL 17 — Indexes and ORDER BY. indexes-ordering.html — quando é que DESC e NULLS FIRST na declaração do índice valem a pena. 🌐 Verificado a 2026-09-15
Leitura recomendada — Markus Winand, «Concatenated Keys». Porquê: é o tratamento mais completo da ordem das colunas que conheço, com o caso do intervalo bem explicado. O que saltar: as secções de Oracle. Fonte secundária. 🌐 Verificado a 2026-09-15
Todas as medições (1003 páginas contra 4; 8056 kB contra 8048 kB) foram corridas em PostgreSQL 17.11 sobre preparar.sql, com um índice de cada vez. ✅ Corrido a 2026-09-15