Índices B-tree — folha de consulta

Só o que se consulta. As explicações estão nas lições — uma folha que explica deixa de caber numa folha.

Ordem de diagnóstico de uma query lenta

1. EXPLAIN (ANALYZE, BUFFERS) — nunca sem BUFFERS. Num UPDATE/DELETE: BEGIN; … ROLLBACK;
2. rows= vs actual rows= — desencontro de ordens de grandeza? → ANALYZE antes de mais nada.
3. Multiplicar por loops= antes de concluir seja o que for.
4. Onde está o tempo — o nó cujo actual time final é próximo do total e cujos filhos são muito mais rápidos. (Os tempos são acumulados: o próprio é a diferença.)
5. Trabalho desperdiçado — Rows Removed by Filter, Heap Fetches, Sort Method: external merge, Batches > 1, lossy=.
6. Páginas — Buffers. É o único número estável entre execuções.

Sinais do plano → o que fazer

Ordem das colunas de um índice composto

1. Igualdade (=) primeiro.
2. Intervalo ou ORDER BY a seguir — e no fim, porque depois de um intervalo as colunas seguintes deixam de estar ordenadas.
3. Seletividade só para desempatar.

Prefixo mais à esquerda: (a,b,c) serve a · (a,b) · (a,b,c). Não serve nada que não fixe a.
Redundância: (a,b) torna um índice só em (a) desnecessário — apaga-se.

O que um B-tree serve (e não serve)

Comandos

-- ver o plano sem executar um DELETE/UPDATE
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE ...; ROLLBACK;

-- testar um índice sem o deixar (CREATE INDEX é transacional)
BEGIN; CREATE INDEX t ON ...; EXPLAIN (ANALYZE, BUFFERS) ...; ROLLBACK;
-- em produção com escrita: CREATE INDEX CONCURRENTLY (não é transacional)

-- índices nunca usados
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 't' ORDER BY idx_scan;
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- estatísticas de uma coluna
SELECT attname, n_distinct, most_common_vals, correlation
FROM pg_stats WHERE tablename = 't';

-- tamanhos
SELECT pg_size_pretty(pg_table_size('t')), pg_size_pretty(pg_indexes_size('t'));
SELECT relpages, reltuples, relallvisible FROM pg_class WHERE relname = 't';

-- altura da B-tree  (CREATE EXTENSION pageinspect;)
SELECT level FROM bt_metap('idx');    -- folhas = nível 0, logo level=2 são 3 níveis

-- diagnóstico: forçar e comparar. NUNCA deixar ligado.
SET enable_seqscan = off;  SET enable_nestloop = off;  RESET ALL;

-- parcial e de expressão
CREATE INDEX ON t (col) WHERE estado = 'pendente';
CREATE INDEX ON t ((lower(email)));        -- a função tem de ser IMMUTABLE
                                           -- e a query tem de usar a expressão IGUAL

Constantes de custo (omissão)

seq_page_cost 1 · random_page_cost 4 (SSD: ~1.1) · cpu_tuple_cost 0.01 · cpu_operator_cost 0.0025 · cpu_index_tuple_cost 0.005 · página 8 kB

Seq Scan com filtro: relpages×1 + reltuples×0.01 + reltuples×0.0025

Números medidos (Postgres 17.11, tabela de 1 000 000 de linhas)

🔴 São desta máquina e desta tabela. Servem de ordem de grandeza, nunca de regra. Mede os teus.

SQLite — o mesmo, noutro vocabulário