Índices e Performance

Avançado • 90 minutos • Otimização de consultas

Aprenda a otimizar suas consultas SQL usando índices e técnicas de performance para tornar seu banco de dados mais rápido e eficiente.

Objetivos de Aprendizagem

Entender o que são índices e como funcionam
Criar e gerenciar diferentes tipos de índices
Analisar planos de execução de consultas
Identificar e resolver problemas de performance
Aplicar boas práticas de otimização

O que são Índices?

Índices são estruturas de dados que melhoram a velocidade de recuperação de dados em uma tabela. Funcionam como um "índice de livro" - apontam diretamente para onde os dados estão armazenados.

🔍 Analogia: Índice de Livro

Sem índice: Para encontrar "SQL" no livro, você precisa ler página por página

Com índice: Você consulta o índice e vai direto para a página 127

No banco: Sem índice = varredura completa da tabela (lento)

No banco: Com índice = acesso direto aos dados (rápido)

Como Funcionam

❌ Sem Índice (Table Scan)

Busca: WHERE nome = 'João'

Linha 1: Maria ❌

Linha 2: Pedro ❌

Linha 3: Ana ❌

Linha 4: João ✅

Linha 5: Carlos ❌

Verificou 5 linhas

✅ Com Índice (Index Seek)

Busca: WHERE nome = 'João'

Índice: Ana → Linha 3

Índice: João → Linha 4 ✅

Índice: Maria → Linha 1

Acesso direto!

Trade-off:

Índices aceleram consultas (SELECT) mas tornam inserções/atualizações mais lentas, pois o índice precisa ser atualizado.

Tipos de Índices

1. Índice Primário (Primary Key)

Criado automaticamente para a chave primária. Garante unicidade e é sempre único.

-- Criado automaticamente
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,  -- Índice automático
    nome VARCHAR(100),
    email VARCHAR(100)
);

Características: Único, não-nulo, uma por tabela, muito rápido

2. Índice Único (Unique)

Garante que não há valores duplicados na coluna.

-- Índice único no email
CREATE UNIQUE INDEX idx_usuarios_email 
ON usuarios(email);

-- Ou na criação da tabela
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(100) UNIQUE  -- Índice único automático
);

Uso: Emails, CPFs, códigos únicos

3. Índice Regular (B-Tree)

O tipo mais comum. Permite duplicatas e é ideal para consultas de igualdade e range.

-- Índice simples
CREATE INDEX idx_usuarios_nome ON usuarios(nome);

-- Índice composto (múltiplas colunas)
CREATE INDEX idx_vendas_data_cliente 
ON vendas(data_venda, cliente_id);

-- Índice com condição
CREATE INDEX idx_produtos_ativos 
ON produtos(categoria_id) 
WHERE ativo = true;

Ideal para: WHERE, ORDER BY, JOIN

4. Índice Parcial

Indexa apenas parte dos dados que atendem a uma condição.

-- Apenas produtos ativos
CREATE INDEX idx_produtos_ativos_nome 
ON produtos(nome) 
WHERE ativo = true;

-- Apenas vendas recentes
CREATE INDEX idx_vendas_recentes 
ON vendas(cliente_id, valor_total) 
WHERE data_venda >= '2024-01-01';

Vantagem: Menor tamanho, mais rápido para consultas específicas

5. Índice de Expressão

Indexa o resultado de uma expressão ou função.

-- Busca case-insensitive
CREATE INDEX idx_usuarios_nome_lower 
ON usuarios(LOWER(nome));

-- Busca por ano
CREATE INDEX idx_vendas_ano 
ON vendas(EXTRACT(YEAR FROM data_venda));

-- Uso
SELECT * FROM usuarios WHERE LOWER(nome) = 'joão';
SELECT * FROM vendas WHERE EXTRACT(YEAR FROM data_venda) = 2024;

Uso: Funções em WHERE, cálculos frequentes

Criando e Gerenciando Índices

Sintaxe Básica

-- Sintaxe geral
CREATE [UNIQUE] INDEX [nome_indice] 
ON tabela(coluna1, coluna2, ...)
[WHERE condição];

-- Exemplos práticos
CREATE INDEX idx_produtos_categoria ON produtos(categoria_id);
CREATE INDEX idx_vendas_data ON vendas(data_venda DESC);
CREATE UNIQUE INDEX idx_usuarios_email ON usuarios(email);

Comandos de Gerenciamento

Listar Índices

-- Ver todos os índices de uma tabela
SELECT 
    indexname,
    indexdef
FROM pg_indexes 
WHERE tablename = 'produtos';

-- Informações detalhadas
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes 
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Remover Índices

-- Remover índice
DROP INDEX idx_produtos_categoria;

-- Remover se existir
DROP INDEX IF EXISTS idx_produtos_categoria;

-- Remover com CASCADE (cuidado!)
DROP INDEX idx_produtos_categoria CASCADE;

Reconstruir Índices

-- Reconstruir um índice específico
REINDEX INDEX idx_produtos_categoria;

-- Reconstruir todos os índices de uma tabela
REINDEX TABLE produtos;

-- Reconstruir todos os índices do banco
REINDEX DATABASE nome_banco;

Quando usar: Após muitas inserções/deleções, corrupção, mudanças de configuração

Analisando Performance de Consultas

O PostgreSQL oferece ferramentas poderosas para analisar como suas consultas são executadas.

EXPLAIN - Plano de Execução

-- Plano básico
EXPLAIN SELECT * FROM produtos WHERE categoria_id = 1;

-- Plano detalhado
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT p.nome, c.nome as categoria
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.preco > 100;

Interpretando o EXPLAIN

❌ Seq Scan (Varredura Sequencial)

Seq Scan on produtos  (cost=0.00..15.00 rows=500 width=32)
  Filter: (categoria_id = 1)

Problema: Lê toda a tabela linha por linha. Lento para tabelas grandes.

✅ Index Scan (Busca por Índice)

Index Scan using idx_produtos_categoria on produtos  
  (cost=0.29..8.31 rows=1 width=32)
  Index Cond: (categoria_id = 1)

Ótimo: Usa índice para acesso direto. Rápido e eficiente.

🔵 Bitmap Scan (Varredura Bitmap)

Bitmap Heap Scan on produtos  (cost=4.30..15.75 rows=5 width=32)
  Recheck Cond: (categoria_id = 1)
  ->  Bitmap Index Scan on idx_produtos_categoria
        (cost=0.00..4.29 rows=5 width=0)

Bom: Híbrido entre seq scan e index scan. Para múltiplas linhas.

Métricas Importantes

Cost (Custo)

Estimativa de "trabalho" para executar a operação

• Menor = melhor

• Unidade arbitrária

• Compare operações similares

Rows (Linhas)

Número estimado de linhas retornadas

• Estimativa vs real (ANALYZE)

• Afeta escolha do plano

• UPDATE STATISTICS se muito diferente

Time (Tempo)

Tempo real de execução (apenas com ANALYZE)

• Startup time: tempo para primeira linha

• Total time: tempo total

• Medido em milissegundos

Buffers

Páginas lidas da memória/disco

• Hit: páginas em cache

• Read: páginas lidas do disco

• Menos reads = melhor

Dicas de Otimização

1. Estratégia de Índices

✅ Faça

• Indexe colunas do WHERE frequentes
• Indexe colunas de JOIN
• Indexe colunas do ORDER BY
• Use índices compostos para consultas específicas
• Monitore uso dos índices

❌ Evite

• Muitos índices em tabelas com muitas escritas
• Índices em colunas com poucos valores únicos
• Índices duplicados ou redundantes
• Índices em colunas pequenas (< 1000 linhas)
• Índices nunca usados

2. Otimização de Consultas

Evite funções em WHERE

❌ Lento

SELECT * FROM vendas 
WHERE EXTRACT(YEAR FROM data_venda) = 2024;

✅ Rápido

SELECT * FROM vendas 
WHERE data_venda >= '2024-01-01' 
AND data_venda < '2025-01-01';

Use LIMIT quando possível

❌ Lento

SELECT * FROM produtos 
ORDER BY data_cadastro DESC;

✅ Rápido

SELECT * FROM produtos 
ORDER BY data_cadastro DESC 
LIMIT 20;

Prefira EXISTS a IN com subconsultas

⚠️ Pode ser lento

SELECT * FROM clientes 
WHERE id IN (
    SELECT cliente_id FROM vendas
);

✅ Mais eficiente

SELECT * FROM clientes c
WHERE EXISTS (
    SELECT 1 FROM vendas v
    WHERE v.cliente_id = c.id
);

3. Monitoramento

-- Índices não utilizados
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- Consultas mais lentas
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Dica: Ative pg_stat_statements para monitorar consultas em produção

Exercício Prático

Vamos praticar otimização!

Analise os cenários e escolha a melhor estratégia:

1. Você tem uma tabela 'produtos' com 100.000 registros. A consulta abaixo é lenta:

SELECT * FROM produtos WHERE categoria_id = 5;

Qual índice criaria?

CREATE INDEX ON produtos(id); CREATE INDEX ON produtos(categoria_id); CREATE INDEX ON produtos(nome);

2. Qual comando mostra o plano de execução de uma consulta?

SHOW PLAN EXPLAIN ANALYZE

3. Você precisa otimizar esta consulta:

SELECT * FROM vendas 
WHERE cliente_id = 123 
AND data_venda >= '2024-01-01' 
ORDER BY data_venda DESC;

Qual índice seria mais eficiente?

CREATE INDEX ON vendas(cliente_id); CREATE INDEX ON vendas(data_venda); CREATE INDEX ON vendas(cliente_id, data_venda DESC);

4. O que indica "Seq Scan" no EXPLAIN?

A consulta está usando um índice A consulta está lendo toda a tabela A consulta está ordenando os dados

Resumo da Aula

Índices aceleram consultas mas tornam escritas mais lentas
Use EXPLAIN para analisar planos de execução
Indexe colunas usadas em WHERE, JOIN e ORDER BY
Monitore e remova índices não utilizados
Evite funções em WHERE e use LIMIT quando possível

Aula Anterior: Subconsultas Próxima Aula: Views e Procedures