Otimização de queries: como identificar e resolver gargalos

1. Introdução à Otimização de Queries

Gargalos em bancos de dados relacionais são pontos de estrangulamento que degradam o desempenho das consultas, tornando-as lentas ou ineficientes. Uma query mal otimizada pode transformar uma operação de milissegundos em minutos, impactando diretamente a experiência do usuário e aumentando os custos de infraestrutura com CPU, memória e I/O.

O ciclo de vida de uma query passa por parsing, otimização, execução e retorno dos dados. Os pontos críticos de desempenho concentram-se na etapa de execução, onde o banco decide como acessar os dados — se por scan completo da tabela, por índices ou por joins complexos. Identificar onde o tempo é gasto é o primeiro passo para resolver gargalos.

2. Ferramentas e Métodos para Identificação de Gargalos

EXPLAIN ANALYZE

A ferramenta mais básica e poderosa para análise de queries é o EXPLAIN ANALYZE. Ela mostra o plano de execução real, incluindo o tempo gasto em cada etapa.

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE data_pedido BETWEEN '2024-01-01' AND '2024-12-31';

A saída revela se houve sequential scan, quantas linhas foram lidas e o tempo real de execução. Um plano com "Seq Scan on pedidos" indica que a tabela foi varrida inteiramente, sinalizando falta de índice adequado.

Slow Query Log

Ativar o log de queries lentas permite capturar automaticamente consultas que excedem um limite de tempo definido:

-- PostgreSQL
SET log_min_duration_statement = 500;  -- registra queries acima de 500ms

Estatísticas de Performance

Ferramentas nativas como pg_stat_statements no PostgreSQL ou sys.dm_exec_query_stats no SQL Server acumulam métricas de execução:

-- PostgreSQL
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

3. Análise de Gargalos Comuns em Queries

Sequential Scan vs. Index Scan

O sequential scan (varredura sequencial) lê todas as linhas da tabela. É aceitável para tabelas pequenas, mas catastrófico em tabelas com milhões de registros. O index scan usa uma estrutura de árvore B+ para localizar rapidamente as linhas desejadas.

-- Gargalo: sequential scan em tabela grande
EXPLAIN SELECT * FROM clientes WHERE email = 'joao@exemplo.com';
-- Resultado: Seq Scan on clientes (cost=0.00..3500.00 rows=1 width=200)

-- Solução: criar índice
CREATE INDEX idx_clientes_email ON clientes(email);

Join Ineficiente

Joins mal planejados geram nested loops excessivos. O otimizador pode escolher nested loop quando um hash join seria mais eficiente para grandes volumes.

-- Gargalo: nested loop join caro
EXPLAIN ANALYZE
SELECT c.nome, p.valor
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
WHERE c.ativo = true;

Ordenação e Agrupamento Pesados

Operações como ORDER BY, GROUP BY e DISTINCT exigem ordenação dos dados. Sem índices adequados, o banco realiza um sort em memória ou em disco, consumindo recursos significativos.

-- Gargalo: Sort pesado
EXPLAIN ANALYZE
SELECT categoria, COUNT(*)
FROM produtos
GROUP BY categoria
ORDER BY COUNT(*) DESC;

4. Estratégias de Otimização com Índices

Índices Compostos

Índices com múltiplas colunas são eficientes para consultas que filtram por várias condições:

CREATE INDEX idx_pedidos_data_cliente ON pedidos(data_pedido, cliente_id);

-- Query beneficiada:
SELECT * FROM pedidos
WHERE data_pedido >= '2024-01-01'
  AND cliente_id = 123;

Covering Indexes (Índices de Cobertura)

Um covering index contém todas as colunas necessárias para a consulta, eliminando a necessidade de acessar a tabela principal:

CREATE INDEX idx_pedidos_cobertura ON pedidos(data_pedido, cliente_id, valor_total);

-- Query totalmente coberta pelo índice:
SELECT data_pedido, cliente_id, valor_total
FROM pedidos
WHERE data_pedido BETWEEN '2024-01-01' AND '2024-01-31';

Índices Parciais e Funcionais

Índices parciais são úteis para filtrar apenas um subconjunto de dados:

CREATE INDEX idx_pedidos_ativos ON pedidos(data_pedido)
WHERE status = 'ativo';

Índices funcionais otimizam expressões:

CREATE INDEX idx_produtos_ano ON produtos(EXTRACT(YEAR FROM data_criacao));

5. Reescrita de Queries e Boas Práticas

Evitar Funções em Colunas Indexadas

Funções em cláusulas WHERE anulam o uso de índices:

-- Ruim: função na coluna indexada
SELECT * FROM pedidos WHERE EXTRACT(YEAR FROM data_pedido) = 2024;

-- Bom: intervalo direto
SELECT * FROM pedidos WHERE data_pedido >= '2024-01-01' AND data_pedido < '2025-01-01';

Uso Correto de Subconsultas e CTEs

CTEs podem ser materializadas ou inline. Em alguns bancos, CTEs são "otimizadores fences", impedindo o pushdown de predicados:

-- CTE que pode ser ineficiente
WITH pedidos_recentes AS (
    SELECT * FROM pedidos WHERE data_pedido > '2024-01-01'
)
SELECT * FROM pedidos_recentes WHERE cliente_id = 10;

-- Alternativa: subconsulta direta
SELECT * FROM pedidos
WHERE data_pedido > '2024-01-01' AND cliente_id = 10;

Limitar Colunas e Linhas

Sempre especifique colunas exatas e use LIMIT quando apropriado:

-- Ruim
SELECT * FROM clientes;

-- Bom
SELECT id, nome, email FROM clientes WHERE ativo = true LIMIT 100;

6. Otimização de Queries com Dados Massivos

Particionamento de Tabelas

Particionamento divide uma tabela grande em partes menores:

-- Particionamento por range no PostgreSQL
CREATE TABLE pedidos (
    id SERIAL,
    data_pedido DATE NOT NULL,
    valor NUMERIC
) PARTITION BY RANGE (data_pedido);

CREATE TABLE pedidos_2024_q1 PARTITION OF pedidos
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Consultas que filtram por data acessam apenas a partição relevante, reduzindo drasticamente o I/O.

Materialized Views

Para consultas agregadas frequentes, materialized views armazenam resultados pré-computados:

CREATE MATERIALIZED VIEW resumo_vendas_mensal AS
SELECT
    EXTRACT(YEAR FROM data_pedido) AS ano,
    EXTRACT(MONTH FROM data_pedido) AS mes,
    COUNT(*) AS total_pedidos,
    SUM(valor) AS valor_total
FROM pedidos
GROUP BY ano, mes;

-- Atualizar periodicamente
REFRESH MATERIALIZED VIEW resumo_vendas_mensal;

Keyset Pagination

A paginação tradicional com OFFSET é ineficiente para grandes conjuntos:

-- Ruim: OFFSET precisa ler linhas anteriores
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 10000;

-- Bom: keyset pagination
SELECT * FROM pedidos
WHERE id > 10000
ORDER BY id
LIMIT 20;

7. Monitoramento Contínuo e Prevenção de Gargalos

Estabelecer SLAs de performance é essencial. Defina limites como "95% das queries devem executar em menos de 100ms". Automatize alertas para queries lentas usando ferramentas como Prometheus + Grafana ou soluções nativas como o Performance Insights da AWS RDS.

Revisões periódicas dos planos de execução e estatísticas do banco (ANALYZE, VACUUM) mantêm o otimizador informado sobre a distribuição dos dados. Um cronograma de manutenção regular evita a degradação gradual do desempenho.

A otimização de queries não é um evento único, mas um processo contínuo. Cada nova funcionalidade ou crescimento no volume de dados pode introduzir novos gargalos. Com as ferramentas e estratégias certas, é possível manter a performance sob controle e garantir uma experiência ágil para os usuários finais.

Referências