Um índice é uma estrutura de dados auxiliar que o SGBD mantém paralelamente à tabela — permite localizar linhas sem percorrer toda a tabela (sequential scan). A analogia clássica é o índice remissivo de um livro: em vez de ler o livro do início ao fim para encontrar uma palavra, consultas o índice e vais directamente à página. O custo é real: cada índice ocupa espaço em disco e atrasa INSERT, UPDATE e DELETE porque precisa de ser mantido actualizado. A arte está em indexar o suficiente — nem mais, nem menos.
-- O B-tree (Balanced Tree) é o tipo de índice default no PostgreSQL
-- Estrutura: árvore equilibrada onde cada nó contém ponteiros para nós filhos
-- e valores ordenados. A altura da árvore é O(log n) — encontrar uma linha
-- em 1 bilião de registos requer ~30 comparações.
-- Representação simplificada de um índice B-tree em preco_base:
--
-- [50]
-- / \
-- [20, 35] [75, 90]
-- / | \ / | \
-- [10] [25] [40][60][80][95]
-- ↓ ↓ ↓ ↓ ↓ ↓
-- heap heap heap heap heap heap ← ponteiros para linhas reais da tabela (TID)
--
-- Para WHERE preco_base = 25:
-- 1. Raiz: 25 < 50 → ir para sub-árvore esquerda
-- 2. Nó [20, 35]: 20 < 25 < 35 → ir para segundo filho
-- 3. Folha [25]: encontrado — seguir o ponteiro para a linha na heap
-- Total: 3 leituras de página vs N leituras para sequential scan
-- O índice armazena os valores ORDENADOS
-- Isso significa que também é útil para:
-- ORDER BY preco_base → sem sort adicional
-- WHERE preco_base BETWEEN a AND b → range scan
-- WHERE preco_base > 50 → range scan a partir de 50
-- Criar índice B-tree (default):
CREATE INDEX idx_produto_preco ON produto (preco_base);
-- O PostgreSQL cria automaticamente índices B-tree para:
-- PRIMARY KEY
-- UNIQUE constraint
-- Não precisas de criá-los manualmente para esses campos.-- ── B-tree (default) — comparações de igualdade e range ─────────────────────
CREATE INDEX idx_encomenda_data ON encomenda (criada_em);
-- Útil para: =, <, <=, >, >=, BETWEEN, ORDER BY, IS NULL/NOT NULL
-- ── Hash — apenas igualdade exacta ───────────────────────────────────────────
CREATE INDEX idx_utilizador_email_hash ON utilizador USING HASH (email);
-- Útil APENAS para: =
-- Não suporta: ranges, ORDER BY, LIKE
-- Mais rápido que B-tree para lookups de igualdade pura (ex: lookup por UUID/email)
-- Mas: raramente necessário — B-tree é quase sempre preferível pela flexibilidade
-- ── GIN (Generalized Inverted Index) — arrays, JSONB, full-text search ────────
-- Índice invertido: para cada elemento, guarda a lista de linhas que o contêm
-- Para arrays:
CREATE INDEX idx_produto_tags ON produto USING GIN (tags);
SELECT * FROM produto WHERE tags @> ARRAY['electrónica']; -- usa o índice GIN
-- Para JSONB:
CREATE INDEX idx_produto_atributos ON produto USING GIN (atributos);
SELECT * FROM produto WHERE atributos @> '{"cor": "azul"}'; -- usa o índice GIN
-- Para full-text search:
ALTER TABLE artigo ADD COLUMN search_vector TSVECTOR;
UPDATE artigo SET search_vector = to_tsvector('portuguese', titulo || ' ' || conteudo);
CREATE INDEX idx_artigo_fts ON artigo USING GIN (search_vector);
SELECT titulo FROM artigo WHERE search_vector @@ to_tsquery('portuguese', 'base & dados');
-- ── GiST (Generalized Search Tree) — geometria, ranges, full-text ────────────
-- Para tipos geométricos (PostGIS):
CREATE INDEX idx_loja_localizacao ON loja USING GiST (localizacao);
SELECT nome FROM loja
WHERE ST_DWithin(localizacao, ST_MakePoint(-9.14, 38.72)::geography, 5000);
-- Encontrar lojas num raio de 5km de Lisboa
-- Para ranges (daterange, tstzrange):
CREATE INDEX idx_reserva_periodo ON reserva USING GiST (periodo);
-- periodo é do tipo tstzrange: '[2024-01-15 10:00, 2024-01-15 12:00)'
SELECT * FROM reserva WHERE periodo && '[2024-01-15 09:00, 2024-01-15 11:00)';
-- && = sobreposição — reservas que se sobrepõem com o intervalo dado
-- ── BRIN (Block Range Index) — colunas com correlação física ─────────────────
-- Armazena min/max de cada bloco de disco — índice muito pequeno
-- Útil para tabelas enormes onde os dados são inseridos em ordem crescente
-- (timestamps, IDs sequenciais) — correlação natural com a ordem física
CREATE INDEX idx_log_timestamp ON log_evento USING BRIN (timestamp);
-- BRIN de 1M de linhas pode ter apenas alguns KB vs MB de um B-tree
-- Tradeoff: menos preciso — pode incluir falsos positivos (verificados depois)-- ── Índice Composto — múltiplas colunas ─────────────────────────────────────
CREATE INDEX idx_encomenda_cliente_data ON encomenda (cliente_id, criada_em DESC);
-- Útil para queries que filtram por cliente_id E ordenam por criada_em:
SELECT * FROM encomenda WHERE cliente_id = 42 ORDER BY criada_em DESC LIMIT 10;
-- REGRA DE ORDEM: o índice composto serve para queries que usam:
-- (cliente_id) ✓ — usa o prefixo do índice
-- (cliente_id, criada_em) ✓ — usa o índice completo
-- (criada_em) ✗ — não usa (criada_em não é prefixo)
-- O primeiro campo do índice composto é o mais selectivo normalmente
-- ── Índice Parcial — indexar apenas um subconjunto das linhas ─────────────────
-- Mais pequeno, mais rápido — apenas índica as linhas que interessam
-- Só encomendas pendentes (em vez de todas):
CREATE INDEX idx_encomenda_pendente ON encomenda (criada_em)
WHERE estado = 'pendente';
-- Uma tabela com 10M encomendas pode ter apenas 50K pendentes
-- O índice parcial tem 1/200 do tamanho do índice completo
-- Só utilizadores activos:
CREATE INDEX idx_utilizador_activo_email ON utilizador (email)
WHERE activo = TRUE AND apagado_em IS NULL;
-- Para a query beneficiar do índice parcial, a WHERE da query deve ser compatível:
SELECT * FROM encomenda WHERE estado = 'pendente' AND criada_em < NOW() - INTERVAL '1 day';
-- ✓ Usa idx_encomenda_pendente — a condição WHERE estado = 'pendente' está coberta
SELECT * FROM encomenda WHERE criada_em < NOW() - INTERVAL '1 day';
-- ✗ Não usa o índice parcial — não filtra por estado = 'pendente'
-- ── Índice de Cobertura — incluir colunas extra para evitar heap access ────────
-- Um índice de cobertura contém todas as colunas necessárias para satisfazer a query
-- O PostgreSQL pode responder sem ir à tabela (Index Only Scan)
CREATE INDEX idx_encomenda_cobertura ON encomenda (cliente_id, criada_em DESC)
INCLUDE (total, estado);
-- INCLUDE: colunas incluídas mas não ordenadas — apenas para "cobertura"
-- Query que beneficia de Index Only Scan:
SELECT criada_em, total, estado
FROM encomenda
WHERE cliente_id = 42
ORDER BY criada_em DESC
LIMIT 20;
-- O índice contém cliente_id (filtro), criada_em (ordem), total e estado (projecção)
-- O PostgreSQL não precisa de aceder às páginas da tabela — só ao índice
-- ── Índice em Expressão ───────────────────────────────────────────────────────
-- Indexar o resultado de uma função — útil para queries case-insensitive
CREATE INDEX idx_utilizador_email_lower ON utilizador (LOWER(email));
-- A query deve usar a mesma expressão:
SELECT * FROM utilizador WHERE LOWER(email) = LOWER('Joao@Example.COM');
-- ✓ Usa o índice de expressão
-- Indexar part of a timestamp (apenas a data):
CREATE INDEX idx_encomenda_data_only ON encomenda (DATE(criada_em));
SELECT * FROM encomenda WHERE DATE(criada_em) = '2024-01-15';
-- Agrupa todas as encomendas do dia num índice compacto-- EXPLAIN mostra o plano de execução sem executar a query
-- EXPLAIN ANALYZE executa a query e mostra tempos reais
-- EXPLAIN (ANALYZE, BUFFERS) mostra também acesso a cache/disco
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT e.id, u.email, e.total
FROM encomenda e
JOIN utilizador u ON u.id = e.cliente_id
WHERE e.estado = 'pendente'
AND e.criada_em > NOW() - INTERVAL '7 days'
ORDER BY e.criada_em DESC
LIMIT 20;
-- Exemplo de output anotado:
--
-- Limit (cost=0.56..45.23 rows=20 width=52)
-- (actual time=0.123..0.456 rows=20 loops=1)
-- -> Nested Loop (cost=0.56..892.34 rows=397 width=52)
-- (actual time=0.120..0.445 rows=20 loops=1)
-- -> Index Scan Backward using idx_encomenda_pendente on encomenda e
-- (cost=0.29..78.12 rows=397 width=28)
-- (actual time=0.089..0.234 rows=20 loops=1)
-- Index Cond: (criada_em > (now() - '7 days'::interval))
-- Filter: (estado = 'pendente')
-- Rows Removed by Filter: 3 ← quantas linhas foram filtradas após o índice
-- -> Index Scan using utilizador_pkey on utilizador u
-- (cost=0.28..2.05 rows=1 width=24)
-- (actual time=0.010..0.010 rows=1 loops=20)
-- Index Cond: (id = e.cliente_id)
-- Planning Time: 0.234 ms
-- Execution Time: 0.512 ms
-- ── O que procurar no plano ───────────────────────────────────────────────────
-- Seq Scan em tabelas grandes → provavelmente falta índice
-- Seq Scan on encomenda (cost=0.00..45234.00 rows=1000000 width=52)
-- Rows Removed by Filter muito alto → o índice não é selectivo o suficiente
-- (o índice encontrou muitas linhas e depois filtrou a maioria)
-- Nested Loop com muitos loops → considerar Hash Join ou índice composto
-- Nested Loop (loops=50000) → 50000 lookups individuais
-- cost=X..Y: X = custo de startup, Y = custo total (em unidades arbitrárias)
-- actual time=X..Y: X = tempo até primeira linha, Y = tempo total (ms)
-- rows=N: linhas estimadas (pelo planner) vs actual rows (reais)
-- Se estimativa e real divergem muito → estatísticas desactualizadas → ANALYZE
-- Actualizar estatísticas manualmente:
ANALYZE encomenda;
ANALYZE; -- toda a base de dados
-- O autovacuum faz isto automaticamente, mas após imports massivos pode ser necessário
-- ── Tipos de scan ────────────────────────────────────────────────────────────
-- Seq Scan — lê toda a tabela página a página (bom para tabelas pequenas ou full scans)
-- Index Scan — navega o índice B-tree e acede à heap para cada linha
-- Index Only Scan — responde apenas pelo índice (tabela não é lida) ← melhor caso
-- Bitmap Index Scan + Bitmap Heap Scan — para múltiplos índices combinados com AND/OR
-- Parallel Seq Scan — leitura paralela em múltiplos workers (PostgreSQL 9.6+)-- ── O optimizador pode ignorar o índice ──────────────────────────────────────
-- 1. Baixa selectividade — coluna booleana ou com poucos valores distintos:
CREATE INDEX idx_activo ON utilizador (activo);
-- Se 95% dos utilizadores têm activo=TRUE, o índice não ajuda:
SELECT * FROM utilizador WHERE activo = TRUE;
-- O optimizador escolhe Seq Scan — mais eficiente que ler o índice + 95% da tabela
-- Solução: índice parcial (ver acima) ou compound index com campo mais selectivo
-- 2. Funções aplicadas à coluna indexada:
SELECT * FROM utilizador WHERE LOWER(email) = 'joao@example.com';
-- ✗ Não usa o índice em email — a função LOWER() "oculta" o valor indexado
-- ✓ Solução: índice de expressão CREATE INDEX ... ON utilizador (LOWER(email))
-- 3. Tipo de dados incompatível — implicit cast impede uso do índice:
-- email é VARCHAR, mas a query compara com INTEGER:
SELECT * FROM utilizador WHERE email = 42; -- cast implícito de INTEGER para VARCHAR
-- O PostgreSQL pode não usar o índice devido à conversão de tipos
-- 4. LIKE com wildcard no início:
SELECT * FROM produto WHERE nome LIKE '%teclado%'; -- ✗ não usa B-tree
-- ✓ Para full-text search: usar GIN com tsvector (ver acima)
-- ✓ Para prefixo: LIKE 'teclado%' usa B-tree (wildcard apenas no fim)
-- 5. NOT IN / NOT EXISTS em colunas nullable:
-- O optimizador tem dificuldade com NOT IN quando a subquery pode retornar NULL
-- ── Overhead de manutenção ───────────────────────────────────────────────────
-- Cada índice é actualizado em cada INSERT, UPDATE, DELETE que afecte as colunas indexadas
-- Uma tabela com 10 índices: cada INSERT actualiza 11 estruturas (tabela + 10 índices)
-- Identificar índices não utilizados (PostgreSQL):
SELECT
schemaname,
tablename,
indexname,
idx_scan, -- número de vezes que o índice foi usado
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- índices nunca utilizados
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Identificar tabelas com Seq Scans frequentes (falta índice):
SELECT
relname AS tabela,
seq_scan,
seq_tup_read,
idx_scan,
n_live_tup AS linhas_estimadas
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;
-- Remover índice desnecessário:
DROP INDEX IF EXISTS idx_activo;
DROP INDEX CONCURRENTLY IF EXISTS idx_activo;
-- CONCURRENTLY: remove o índice sem bloquear leituras e escritas na tabela-- CREATE INDEX bloqueia escritas na tabela durante a construção
-- Em tabelas grandes em produção, usar CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_encomenda_estado ON encomenda (estado);
-- CONCURRENTLY: constrói o índice em background, sem bloquear INSERT/UPDATE/DELETE
-- Demora mais tempo (2 passagens pela tabela em vez de 1)
-- Não pode ser executado dentro de um bloco BEGIN/COMMIT
-- Se falhar a meio: fica em estado "invalid" — apagar e recriar:
-- DROP INDEX CONCURRENTLY idx_encomenda_estado;
-- Ver índices em estado inválido:
SELECT indexname, tablename
FROM pg_indexes
JOIN pg_index ON pg_index.indexrelid = (schemaname || '.' || indexname)::regclass
WHERE NOT pg_index.indisvalid;
-- Monitorizar progresso de criação de índice (PostgreSQL 12+):
SELECT phase, blocks_done, blocks_total,
ROUND(blocks_done::NUMERIC / NULLIF(blocks_total, 0) * 100, 1) AS pct
FROM pg_stat_progress_create_index;@>, @@). GiST para geometria e ranges com sobreposição (&&).=), depois as de range ou ORDER BY.WHERE estado = 'pendente') para subconjuntos frequentemente consultados — muito mais pequenos e rápidos.INCLUDE) para permitir Index Only Scan — o PostgreSQL responde sem aceder à tabela.LOWER(email)) impedem o uso do índice — criar índice de expressão com a mesma função.EXPLAIN (ANALYZE, BUFFERS) para ver o plano real — comparar estimativas com valores actuais; divergências grandes indicam estatísticas desactualizadas (ANALYZE).pg_stat_user_indexes: índices com idx_scan = 0 são candidatos a remoção. pg_stat_user_tables: tabelas com seq_scan alto são candidatas a novos índices.CREATE INDEX CONCURRENTLY — evita bloquear a tabela durante a construção.