Banco de Dados13 min de leitura

Bancos de Dados: O Poder (e o Perigo) dos Índices

Como B-Trees funcionam internamente, EXPLAIN ANALYZE para diagnóstico, índices compostos e sua ordem de colunas, índices parciais, covering indexes e o custo real de cada índice em writes.

12 de agosto de 2026

Uma API que funcionava perfeitamente com 1.000 usuários começa a ficar lenta com 100.000. Não é o servidor, não é o Node.js, não é o React — é o banco de dados fazendo Full Table Scan: lendo cada linha da tabela para responder uma query. Em uma tabela de 500.000 pedidos, isso significa ler 500.000 linhas para encontrar os pedidos de um único usuário.

Índices resolvem isso, mas a maioria dos desenvolvedores entende apenas a superfície: 'adiciona um INDEX e fica rápido'. A realidade é mais rica — a ordem das colunas em um índice composto importa, índices têm custo real em writes, e o PostgreSQL tem EXPLAIN ANALYZE para te dizer exatamente o que está acontecendo.

Como B-Trees Funcionam: A Estrutura Real

O tipo de índice padrão no PostgreSQL (e na maioria dos bancos relacionais) é a B-Tree (Balanced Tree). Entender sua estrutura explica por que ela é eficiente e quais operações ela acelera:

Uma B-Tree organiza os valores indexados em nós ordenados. Cada nó interno aponta para filhos que contêm valores em um intervalo específico. Para encontrar o email 'maria@email.com' em uma tabela de 1 milhão de registros:

O banco começa no nó raiz e compara: 'maria...' > 'mmmm...'?
Desce para o nó filho do intervalo correto
Repete por ~20 níveis (log₂ de 1.000.000 ≈ 20)
Encontra o ponteiro para a posição real na tabela (heap tuple)
Lê apenas essa linha

Resultado: 20 operações de I/O ao invés de 1.000.000. Isso explica a diferença de 4 segundos para 50ms. Uma B-Tree suporta operações de igualdade (=), range (>, <, BETWEEN) e ordenação (ORDER BY) no mesmo índice — por isso é o tipo padrão.

EXPLAIN ANALYZE: Veja o Que o Banco Está Fazendo

Antes de criar qualquer índice, use EXPLAIN ANALYZE para entender o plano de execução atual. É a ferramenta mais importante para diagnóstico de performance:

sql

1-- EXPLAIN: mostra o plano sem executar a query (estimativas)
-- EXPLAIN: mostra o plano sem executar a query (estimativas)
2-- ANALYZE: executa a query e mostra números reais
-- ANALYZE: executa a query e mostra números reais
3EXPLAIN ANALYZE
EXPLAIN ANALYZE
4SELECT id, status, total
SELECT id, status, total
5FROM orders
FROM orders
6WHERE customer_id = 'uuid-123'
WHERE customer_id = 'uuid-123'
7  AND status = 'PENDING'
  AND status = 'PENDING'
8ORDER BY created_at DESC;
ORDER BY created_at DESC;
9

10-- Saída típica SEM índice:
-- Saída típica SEM índice:
11-- Seq Scan on orders  (cost=0.00..8432.00 rows=1 width=48)
-- Seq Scan on orders  (cost=0.00..8432.00 rows=1 width=48)
12--                     (actual time=0.024..124.832 rows=3 loops=1)
--                     (actual time=0.024..124.832 rows=3 loops=1)
13--   Filter: ((customer_id = 'uuid-123') AND (status = 'PENDING'))
--   Filter: ((customer_id = 'uuid-123') AND (status = 'PENDING'))
14--   Rows Removed by Filter: 499997
--   Rows Removed by Filter: 499997
15-- Planning Time: 0.3 ms
-- Planning Time: 0.3 ms
16-- Execution Time: 124.9 ms  ← Full Table Scan!
-- Execution Time: 124.9 ms  ← Full Table Scan!
17

18-- Saída típica COM índice:
-- Saída típica COM índice:
19-- Index Scan using idx_orders_customer_status on orders
-- Index Scan using idx_orders_customer_status on orders
20--   (cost=0.43..12.47 rows=3 width=48)
--   (cost=0.43..12.47 rows=3 width=48)
21--   (actual time=0.012..0.025 rows=3 loops=1)
--   (actual time=0.012..0.025 rows=3 loops=1)
22--   Index Cond: ((customer_id = 'uuid-123') AND (status = 'PENDING'))
--   Index Cond: ((customer_id = 'uuid-123') AND (status = 'PENDING'))
23-- Planning Time: 0.4 ms
-- Planning Time: 0.4 ms
24-- Execution Time: 0.08 ms  ← Index Scan!
-- Execution Time: 0.08 ms  ← Index Scan!

Os termos mais importantes no EXPLAIN ANALYZE:

`Seq Scan` — Full Table Scan. Sinal de alerta se a tabela for grande.
`Index Scan` — usa o índice + lê as linhas no heap. Bom para baixa seletividade.
`Index Only Scan` — responde apenas com dados do índice, sem tocar o heap. O mais eficiente.
`cost=0.00..8432.00` — estimativa de custo do planejador (unidades arbitrárias). O segundo número é o total.
`rows=X` — linhas retornadas. Quando muito diferente da estimativa, as estatísticas estão desatualizadas (ANALYZE).
`Rows Removed by Filter: 499997` — quantas linhas foram lidas mas descartadas. Alto valor = candidato a índice.

Índices Simples: Quando e Como Criar

sql

1-- Índice simples: busca por email
-- Índice simples: busca por email
2CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_email ON users (email);
3

4-- Índice único: garante unicidade E acelera buscas
-- Índice único: garante unicidade E acelera buscas
5CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
6

7-- Índice em chave estrangeira: SEMPRE indexe FKs
-- Índice em chave estrangeira: SEMPRE indexe FKs
8-- JOINs sem índice na FK são Full Scans
-- JOINs sem índice na FK são Full Scans
9CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
10CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
11

12-- Índice em colunas de status usadas em WHERE frequentemente
-- Índice em colunas de status usadas em WHERE frequentemente
13CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_status ON orders (status);

Índices Compostos: A Ordem das Colunas Importa

Índices compostos (múltiplas colunas) são poderosos, mas a ordem das colunas define para quais queries o índice funciona. A regra é o prefixo: um índice em (a, b, c) acelera queries que filtram por (a), (a, b), ou (a, b, c) — mas não acelera queries que filtram apenas por (b) ou (c).

sql

1-- Índice composto para a query: WHERE customer_id = ? AND status = ?
-- Índice composto para a query: WHERE customer_id = ? AND status = ?
2CREATE INDEX idx_orders_customer_status
CREATE INDEX idx_orders_customer_status
3  ON orders (customer_id, status);
  ON orders (customer_id, status);
4

5-- Este índice acelera:
-- Este índice acelera:
6-- WHERE customer_id = 'uuid'                         ✅ (prefixo: customer_id)
-- WHERE customer_id = 'uuid'                         ✅ (prefixo: customer_id)
7-- WHERE customer_id = 'uuid' AND status = 'PENDING'  ✅ (prefixo completo)
-- WHERE customer_id = 'uuid' AND status = 'PENDING'  ✅ (prefixo completo)
8

9-- Este índice NÃO acelera:
-- Este índice NÃO acelera:
10-- WHERE status = 'PENDING'                           ❌ (não começa pelo prefixo)
-- WHERE status = 'PENDING'                           ❌ (não começa pelo prefixo)
11

12-- Regra para definir a ordem das colunas no índice composto:
-- Regra para definir a ordem das colunas no índice composto:
13-- 1. Colunas de igualdade (=) primeiro
-- 1. Colunas de igualdade (=) primeiro
14-- 2. Colunas de range (>, <, BETWEEN) por último
-- 2. Colunas de range (>, <, BETWEEN) por último
15-- Exemplo: WHERE customer_id = ? AND created_at > ?
-- Exemplo: WHERE customer_id = ? AND created_at > ?
16CREATE INDEX idx_orders_customer_created
CREATE INDEX idx_orders_customer_created
17  ON orders (customer_id, created_at DESC);
  ON orders (customer_id, created_at DESC);
18-- A direção DESC no índice é importante para ORDER BY DESC!
-- A direção DESC no índice é importante para ORDER BY DESC!

Covering Indexes: Index Only Scan

Um Covering Index inclui todas as colunas que uma query precisa, eliminando a necessidade de acessar o heap (as linhas reais da tabela). O resultado é um Index Only Scan — o tipo de leitura mais eficiente possível:

sql

1-- Query que lista pedidos de um cliente:
-- Query que lista pedidos de um cliente:
2SELECT id, status, total, created_at
SELECT id, status, total, created_at
3FROM orders
FROM orders
4WHERE customer_id = 'uuid-123'
WHERE customer_id = 'uuid-123'
5ORDER BY created_at DESC
ORDER BY created_at DESC
6LIMIT 10;
LIMIT 10;
7

8-- Índice normal: Index Scan + Heap Fetch (2 I/Os por linha)
-- Índice normal: Index Scan + Heap Fetch (2 I/Os por linha)
9CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_customer ON orders (customer_id);
10

11-- Covering Index: inclui todas as colunas do SELECT
-- Covering Index: inclui todas as colunas do SELECT
12-- Resultado: Index Only Scan (1 I/O, sem tocar o heap)
-- Resultado: Index Only Scan (1 I/O, sem tocar o heap)
13CREATE INDEX idx_orders_customer_covering
CREATE INDEX idx_orders_customer_covering
14  ON orders (customer_id, created_at DESC)
  ON orders (customer_id, created_at DESC)
15  INCLUDE (id, status, total);
  INCLUDE (id, status, total);
16-- A cláusula INCLUDE adiciona colunas ao índice sem incluí-las na ordenação
-- A cláusula INCLUDE adiciona colunas ao índice sem incluí-las na ordenação
17-- (disponível no PostgreSQL 11+ e SQL Server)
-- (disponível no PostgreSQL 11+ e SQL Server)

Índices Parciais: Indexando Apenas o Que Importa

Um Partial Index (índice parcial) indexa apenas um subconjunto das linhas baseado em uma condição WHERE. Isso reduz drasticamente o tamanho do índice e o custo de manutenção em writes:

sql

1-- Cenário: tabela com 1 milhão de pedidos
-- Cenário: tabela com 1 milhão de pedidos
2-- 95% tem status 'DELIVERED', 5% tem status 'PENDING'
-- 95% tem status 'DELIVERED', 5% tem status 'PENDING'
3-- Você só busca por pedidos PENDENTES com frequência
-- Você só busca por pedidos PENDENTES com frequência
4

5-- Índice normal: indexa 1.000.000 de linhas
-- Índice normal: indexa 1.000.000 de linhas
6CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_status ON orders (status);
7

8-- Índice parcial: indexa apenas as 50.000 linhas PENDING
-- Índice parcial: indexa apenas as 50.000 linhas PENDING
9CREATE INDEX idx_orders_pending
CREATE INDEX idx_orders_pending
10  ON orders (customer_id, created_at)
  ON orders (customer_id, created_at)
11  WHERE status = 'PENDING';
  WHERE status = 'PENDING';
12

13-- Resultado: índice 95% menor, writes 95% mais rápidos para ele
-- Resultado: índice 95% menor, writes 95% mais rápidos para ele
14-- A query abaixo usará este índice automaticamente:
-- A query abaixo usará este índice automaticamente:
15SELECT * FROM orders
SELECT * FROM orders
16WHERE customer_id = 'uuid-123' AND status = 'PENDING';
WHERE customer_id = 'uuid-123' AND status = 'PENDING';
17

18-- Outro uso clássico: soft delete
-- Outro uso clássico: soft delete
19-- Indexar apenas registros não deletados
-- Indexar apenas registros não deletados
20CREATE INDEX idx_users_active
CREATE INDEX idx_users_active
21  ON users (email)
  ON users (email)
22  WHERE deleted_at IS NULL;
  WHERE deleted_at IS NULL;

O Custo Real dos Índices: Writes

Todo índice tem um custo em operações de escrita. Quando você faz um INSERT, UPDATE ou DELETE, o banco precisa atualizar cada índice da tabela além dos dados em si. Uma tabela com 10 índices faz 11 operações de I/O para cada write:

sql

1-- Verificando quantos índices uma tabela tem:
-- Verificando quantos índices uma tabela tem:
2SELECT
SELECT
3  indexname,
  indexname,
4  indexdef,
  indexdef,
5  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
6FROM pg_indexes
FROM pg_indexes
7JOIN pg_class ON pg_class.relname = pg_indexes.indexname
JOIN pg_class ON pg_class.relname = pg_indexes.indexname
8WHERE tablename = 'orders'
WHERE tablename = 'orders'
9ORDER BY pg_relation_size(indexrelid) DESC;
ORDER BY pg_relation_size(indexrelid) DESC;
10

11-- Identificando índices não utilizados (candidatos para remoção):
-- Identificando índices não utilizados (candidatos para remoção):
12SELECT
SELECT
13  schemaname,
  schemaname,
14  tablename,
  tablename,
15  indexname,
  indexname,
16  idx_scan AS vezes_utilizado,
  idx_scan AS vezes_utilizado,
17  pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
  pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
18FROM pg_stat_user_indexes
FROM pg_stat_user_indexes
19JOIN pg_indexes USING (schemaname, tablename, indexname)
JOIN pg_indexes USING (schemaname, tablename, indexname)
20WHERE idx_scan = 0  -- Nunca foi usado!
WHERE idx_scan = 0  -- Nunca foi usado!
21  AND indexname NOT LIKE '%pkey%'  -- Exclui PKs
  AND indexname NOT LIKE '%pkey%'  -- Exclui PKs
22ORDER BY pg_relation_size(indexrelid) DESC;
ORDER BY pg_relation_size(indexrelid) DESC;

Nunca indexe colunas de baixa cardinalidade como booleanos (is_active com valores true/false) com índice simples — o banco prefere Full Scan porque o índice não filtra o suficiente. Use Partial Index nesses casos. Também evite indexar colunas que mudam constantemente (como updated_at) pois o custo de manutenção do índice supera os benefícios.

Criando Índices sem Bloquear a Tabela em Produção

Por padrão, CREATE INDEX bloqueia writes na tabela enquanto o índice é construído — inaceitável em produção. A solução é o CONCURRENTLY:

sql

1-- Cria o índice sem bloquear a tabela
-- Cria o índice sem bloquear a tabela
2-- Demora mais, mas não impacta a produção
-- Demora mais, mas não impacta a produção
3CREATE INDEX CONCURRENTLY idx_orders_customer_id
CREATE INDEX CONCURRENTLY idx_orders_customer_id
4  ON orders (customer_id);
  ON orders (customer_id);
5

6-- Desvantagem: não pode ser feito dentro de uma transaction
-- Desvantagem: não pode ser feito dentro de uma transaction
7-- Monitore o progresso:
-- Monitore o progresso:
8SELECT phase, blocks_done, blocks_total,
SELECT phase, blocks_done, blocks_total,
9  ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS percent
  ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS percent
10FROM pg_stat_progress_create_index
FROM pg_stat_progress_create_index
11WHERE relid = 'orders'::regclass;
WHERE relid = 'orders'::regclass;

Conclusão

O workflow correto para otimização de queries é: medir primeiro (com EXPLAIN ANALYZE), identificar Seq Scans em tabelas grandes, criar o índice mais específico possível (partial > covering > composto > simples), e verificar com pg_stat_user_indexes se os índices existentes estão sendo usados. Índices desnecessários são passivo, não ativo — aumentam o tempo de writes e consomem disco sem trazer benefício.