pronto 7 lições Postgres 17 · SQLite

Índices B-tree em SQL

Porque é que um índice acelera umas queries e outras não — e como decidir, com números, em vez de por hábito. Todos os planos, tamanhos e tempos deste curso foram medidos numa base de treino que tu podes reproduzir em dois comandos.

O que vais conseguir fazer no fim

• Calcular à mão o custo que o Postgres atribui a uma varredura sequencial, e conferir que bate certo com o EXPLAIN.
• Prever quantas leituras de página custa localizar um valor num índice, a partir do número de linhas e do fanout.
• Distinguir Index Scan, Bitmap Heap Scan e Index Only Scan num plano, e dizer porque é que o planeador escolheu aquele.
• Diagnosticar um índice que não está a ser usado: decidir entre as três causas possíveis e confirmar qual é, com um comando de cada vez.
• Decidir a ordem das colunas de um índice composto e justificá-la.
• Ler um plano de vinte linhas e dizer, em menos de um minuto, onde está o tempo.
• Defender a decisão de não criar um índice, com números.

O que este curso não é

• Não ensina SQL. Parte do princípio que escreves a query; o problema é ela ser lenta.
• Não cobre índices que não sejam B-tree — GIN, GiST, BRIN, hash, SP-GiST. Pesquisa textual, JSON e geometria precisam deles e ficam de fora. Se vierem a ser precisos, serão um tópico próprio (indices-nao-btree-sql), não uma lição acrescentada a este.
• Não cobre particionamento, réplicas nem sharding. A fronteira é o ponto em que a resposta deixa de ser «que índice?» e passa a ser «que arquitetura?» — algures acima das dezenas de milhões de linhas.
• Não cobre afinação do servidor além dos parâmetros que mudam decisões de índice (random_page_cost, work_mem). shared_buffers, WAL e checkpoints ficam de fora.
• Não cobre o MySQL/InnoDB a sério. O índice agrupado do InnoDB muda o mecanismo o suficiente para merecer tratamento próprio, e fingir que é igual seria pior do que não falar dele.

O que assumi

Do diagnóstico. Cada pressuposto traz o que muda se estiver errado — se algum não bater contigo, diz, porque há lições que mudam por causa disso.

O mapa

A lição 0 existe porque o diagnóstico disse «índices quase zero»: sem a noção de página e de custo, tudo o resto vira regras decoradas. Não é uma nota de rodapé — é por onde se começa.

O plano no tempo

Distribuído por ~1 hora por semana, que foi o ritmo declarado. Sete semanas para tudo; quatro para um ponto de chegada honesto.

Preparar o ambiente

Os exercícios correm mesmo. Precisas da base de treino — 1 000 000 de linhas, ~87 MB, cria-se em segundos:

createdb treino_indices
psql -d treino_indices -f preparar.sql

O ficheiro é preparar.sql. Precisas também de:

psql -d treino_indices -c "CREATE EXTENSION IF NOT EXISTS pageinspect;"

No fim do tópico

• Folha de consulta — imprimível em A4, só o que se consulta.
• Teste de domínio — intercalado, sem soluções à vista. A entrega vai para correção.
• Flashcards (CSV para o Anki, frente;verso;etiqueta).
• Calendário de revisão (.ics) — +1, +3, +7, +21 e +60 dias. 🔴 Importa-o antes de começares, não depois. Num ritmo de uma hora por semana, é o que decide se isto fica sabido.
• Projeto: pega numa base de dados tua, corre a auditoria de índices da lição 6, e escreve uma decisão fundamentada por cada índice que propões criar ou apagar. É o exercício que junta tudo.

Nota das fases

O que cada fase do processo encontrou. Está aqui porque é isto que distingue um curso que passou pelo processo de um que parece ter passado.

Fase 0 — Verificar

Procurei em docs/ e topicos/ por: «b-tree», «índice», «index», «query», «lenta», «desempenho», «seletividade»; no INDICE.md; e com git log -S "b-tree".

Encontrei: nada. Este é o primeiro tópico do repositório, que tinha zero commits. Não há pré-requisitos por cobrir noutro tópico nem vizinhos com que declarar fronteira — a fronteira declarada acima é, por agora, contra tópicos que não existem e que ficam nomeados para quando existirem (indices-nao-btree-sql, e um sobre o InnoDB).

Fontes primárias localizadas: a documentação do PostgreSQL 17 (tipos de índice, multicolumn, index-only scans, visibility map, parciais, de expressão, volatilidade de funções, uso do EXPLAIN, constantes de custo, estatísticas do planeador). Uma fonte secundária identificada como tal: Use The Index, Luke!.

Fase 1 — Criar

Sete lições, folha, teste, flashcards e calendário. Decisões tomadas durante a escrita:

• Núcleo e extensão (D-011), por causa da tensão entre «as três coisas» e 1h/semana.
• Uma base de treino única para o curso inteiro, em vez de exemplos avulsos: permite que os números de uma lição sejam comparáveis com os da seguinte.
• Postgres 17.11 instalado de propósito para poder correr tudo. Sem isso, metade das afirmações deste curso seria de memória.

Fase 2 — Aprofundar

O que a Fase 1 errou:

• 🔴 A lição 3 afirmava que «o Postgres deixa de usar o índice acima de ~20% das linhas». Número redondo, escrito de memória, sem fonte. Medido: o índice foi usado até 50%, e até 60% com random_page_cost=1.1. A correção ficou à vista na lição, e a lição passou a ensinar a medir o limiar em vez de o decorar.
• A lição 1 tinha «Recuperar» antes de «Objetivo», contra a ordem fixada no PROCESSO.md. Trocado.
• Faltava o caso em que seletividade e agrupamento divergem. A lição 1 explicava a estrutura e saltava direto para «o índice é rápido». Acrescentado o exercício E3, que mostra 1% das linhas a ler 8359 páginas — e que prepara a lição 3 em vez de a repetir.
• A lição 2 não explicava porque é que Heap Fetches era 40 para 20 linhas. Ficava o número sem o mecanismo (duas versões por linha, à espera de VACUUM). Acrescentado.

Fase 3 — Validar

Resolvi todos os exercícios e o teste do zero, corri todo o código, e conferi cada afirmação factual contra a documentação aberta na altura. O que isso apanhou:

• 🔴 O preparar.sql não corria. i * 7919 estoura os 32 bits por volta da linha 271 000: ERROR: integer out of range. Corrigido com i::bigint. Um erro invisível a ler e imediato a correr.
• 🔴 O índice de expressão da lição 6 não podia ser criado. date_part('year', criada_em) sobre timestamptz dá ERROR: functions in index expression must be marked IMMUTABLE, porque depende do fuso da sessão. Eu tinha-o escrito como exemplo que funcionava. Passou a ser o exemplo central da lição, com o erro, a causa e a correção — ficou melhor do que a versão que eu pensava estar certa.
• Três números inventados, substituídos por medições: a ordenação em disco da lição 5 («~35000kB» → 15792kB), a memória do quicksort («~100MB» → 79342kB), e as páginas do E3 da lição 3 («~90» → 118).
• O custo de escrita da lição 4 estava sobrestimado. Dizia «16× mais lento», medido numa tabela criada com INCLUDING ALL — que trazia a chave primária e contaminava a comparação. Medição controlada: 6,8×. Corrigido nas duas lições onde aparecia.
• Uma medição minha estava mal feita e quase entrou no curso: comparei as duas ordens de um índice composto sem apagar o outro índice, o planeador escolheu o bom nos dois casos, e o resultado deu «não há diferença». A diferença real é de 250×. O aviso ficou escrito no enunciado do exercício, para não acontecer a quem o repetir.
• Descoberta que contrariou o que eu ia escrever: o planeador não fica perdido quando a tabela cresce sem ANALYZE — corrige a escala pelo tamanho real do ficheiro. O que o cega é a distribuição mudar. A lição 3 foi reescrita para dizer isto, com a demonstração do erro de 448×.
• ⚠️ O que ficou por validar: a verificação de acessibilidade automática (axe-core) não correu nesta máquina — o Chrome do puppeteer vem sem assinatura e o macOS mata-o. O validar.sh avisa e diz como resolver. As páginas foram verificadas manualmente nos dois temas e a 400 px.