SQL Performance na Prática com PostgreSQL: Da lentidão a rapidez

Se você trabalha com API, dashboard ou relatório, uma query lenta vira gargalo muito rápido e você nem percebe.

Neste post, vamos pegar um caso do mundo real e melhorar desempenho de forma objetiva, e medindo resultado com o EXPLAIN ANALYZE.

Problema prático

Nós temos uma tabela de pedidos com volume alto de dados e precisamos buscar o faturamento dos últimos 30 dias para pedidos pagos.

A primeira versão funciona, mas escala e performa muito mal :(

Exemplo completo

O exemplo pronto deste post está no meu Github (me dá uma estrela lá :)

Passo a passo do código

1) Suba o ambiente

Entre na pasta do exemplo:

cd contents/003-sql-performance-postgres

Crie o arquivo .env a partir do exemplo:

cp .env.example .env

No PowerShell, use:

Copy-Item .env.example .env -Force

Depois suba o PostgreSQL:

docker compose up -d

O docker-compose.yml monta dois volumes importantes:

• sql/bootstrap em /docker-entrypoint-initdb.d, para criar e popular o banco na primeira subida

• sql em /sql, para executar os benchmarks dentro do container

2) Entenda um pouco sobre os dados

O exemplo começa nos scripts de bootstrap:

• sql/bootstrap/01-schema.sql

• sql/bootstrap/02-seed.sql

Resumidamente criamos customers e orders, depois geramos uma massa de dados que já vai ser suficiente para o plano da query ficar interessante.

Isso é importante porque performance SQL sem volume costuma esconder gargalos

O schema principal é:

CREATE TABLE orders (

id BIGSERIAL PRIMARY KEY,

customer_id BIGINT NOT NULL REFERENCES customers(id),

status VARCHAR(20) NOT NULL,

total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

O script faz uma carga de dados de 200000 pedidos distribuídos ao longo de 365 dias (famoso, 1 ano):

INSERT INTO orders (customer_id, status, total_amount, created_at)

SELECT

((random() * 4999)::INT + 1) AS customer_id,

(ARRAY['PENDING', 'PAID', 'SHIPPED', 'CANCELLED'])[1 + (random() * 3)::INT] AS status,

round((random() * 500 + 20)::NUMERIC, 2) AS total_amount,

NOW() - (random() * INTERVAL '365 days') AS created_at

FROM generate_series(1, 200000);

No final do seed, o script executa:

ANALYZE customers;

ANALYZE orders;

Isso atualiza as estatísticas que o otimizador usa para escolher o plano.

3) Rode a query não-otimizada com EXPLAIN ANALYZE

Execute o primeiro benchmark:

docker compose exec postgres psql -U app_user -d performance_lab -f /sql/benchmarks/01-non-optimized.sql

O arquivo sql/benchmarks/01-non-optimized.sql roda a query com EXPLAIN ANALYZE:

EXPLAIN ANALYZE

SELECT

date_trunc('day', created_at) AS order_day,

COUNT(*) AS total_orders,

SUM(total_amount) AS total_revenue

FROM orders

WHERE status = 'PAID'

AND created_at::date BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE

GROUP BY order_day

ORDER BY order_day;

O ponto problemático está no filtro de data (como sempre):

WHERE status = 'PAID'

AND created_at::date BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE

O cast em created_at::date transforma a coluna antes da comparação.

Na prática, isso pode atrapalhar o uso eficiente de índice porque o banco precisa calcular a expressão para comparar as linhas.

Mesmo que exista um índice em created_at, esse filtro não combina bem com ele porque a condição está em cima de uma expressão derivada da coluna.

Na versão otimizada, a comparação direta por intervalo permite que o plano use o índice composto com Bitmap Index Scan e depois busque as linhas com Bitmap Heap Scan.

Ao ler a saída do EXPLAIN ANALYZE da query não otimizada, procure principalmente por:

• Seq Scan, quando o banco lê a tabela inteira ou grande parte dela

• Rows Removed by Filter, que mostra linhas lidas e descartadas pelo filtro

• Execution Time, que é o tempo real gasto pela execução

• Planning Time, que é o tempo gasto para montar o plano

Um plano ruim costuma revelar que a query precisou olhar muitas linhas para devolver poucos dias de faturamento.

Na query otimizada, o Rows Removed by Filter pode deixar de ser o ponto principal; nesse caso, observe o uso de Bitmap Index Scan, Bitmap Heap Scan, Recheck Cond e Heap Blocks.

4) Rode a query otimizada com EXPLAIN ANALYZE

Execute o segundo benchmark:

docker compose exec postgres psql -U app_user -d performance_lab -f /sql/benchmarks/02-optimized.sql

Na versão otimizada, o índice é criado para combinar o status e a janela de tempo:

CREATE INDEX IF NOT EXISTS idx_orders_status_created_at ON orders (status, created_at);

Depois o script atualiza as estatísticas:

ANALYZE orders;

E o filtro passa a comparar created_at diretamente:

WHERE status = 'PAID'

AND created_at >= date_trunc('day', NOW()) - INTERVAL '30 days'

AND created_at < date_trunc('day', NOW()) + INTERVAL '1 day'

Assim o otimizador tem uma chance muito melhor de usar o índice composto e reduzir o custo da leitura.

O arquivo completo fica assim:

CREATE INDEX IF NOT EXISTS idx_orders_status_created_at ON orders (status, created_at);

ANALYZE orders;

EXPLAIN ANALYZE

SELECT

date_trunc('day', created_at) AS order_day,

COUNT(*) AS total_orders,

SUM(total_amount) AS total_revenue

FROM orders

WHERE status = 'PAID'

AND created_at >= date_trunc('day', NOW()) - INTERVAL '30 days'

AND created_at < date_trunc('day', NOW()) + INTERVAL '1 day'

GROUP BY order_day

ORDER BY order_day;

Na saída do EXPLAIN ANALYZE, compare com a primeira versão e procure por:

• Uso de Bitmap Index Scan em idx_orders_status_created_at

• Bitmap Heap Scan em orders, usando o bitmap produzido pelo índice

• Index Cond ou Recheck Cond contendo status = 'PAID' e o intervalo de created_at

• Redução do Execution Time

• Custo menor para localizar os pedidos PAID dentro da janela de data

  • Cloud geralmente cobra por processamento então queries bem otimizadas vão doer menos no seu bolso :)

Um plano otimizado comum para esse exemplo fica nessa linha:

Bitmap Heap Scan on orders

Recheck Cond: status = 'PAID' AND created_at >= ... AND created_at < ...

-> Bitmap Index Scan on idx_orders_status_created_at

Index Cond: status = 'PAID' AND created_at >= ... AND created_at < ...

Isso não é pior do que um Index Scan simples.

Quando várias linhas encaixam no filtro, o PostgreSQL pode preferir montar um bitmap com as posições encontradas no índice e depois buscar os blocos da tabela de forma mais eficiente.

5) Compare os resultados

Para uma comparação limpa, rode primeiro a versão não otimizada e depois a otimizada na mesma base recém-criada:

docker compose exec postgres psql -U app_user -d performance_lab -f /sql/benchmarks/01-non-optimized.sql

docker compose exec postgres psql -U app_user -d performance_lab -f /sql/benchmarks/02-optimized.sql

Se você já rodou a versão otimizada antes, o indice idx_orders_status_created_at já existe.

Nesse caso, recrie o ambiente ou remova o índice antes de medir novamente a query não otimizada.

O valor exato pode mudar porque a massa usa random(), mas a leitura deve seguir esta ideia:

O que observar

Não otimizada, filtro com created_at::date, maior chance de leitura ampla e descarte de linhas.

Otimizada, índice (status, created_at), menor trabalho para achar o intervalo.

O objetivo não é decorar um numero de tempo.

O objetivo é provar, pelo plano real, que a query otimizada faz menos trabalho.

Erros comuns

1. Comparar queries sem mesma massa de dados.

1. Criar índice e esquecer ANALYZE.

1. Usar funções/cast na coluna filtrada e perder seletividade.

1. Otimizar sem medir EXPLAIN ANALYZE.

Conclusão e próximos passos

SQL performance não e só sobre criar índices.

É sobre combinar:

• Modelagem mínima correta

• Filtragem bem feita

• Leitura de plano que condiz com a realidade

• E sejam críticos, façam análises constantemente...tempo é dinheiro para alguns casos (Cloud)
  

Próximos passos:

1. Testar particionamento por data.

2. Avaliar índice parcial para status = 'PAID' .

3. Incluir monitoramento de queries lentas no ambiente real.