Otimização de queries baseada na análise do plano: EXPLAIN ANALYZE

Chegou a hora da verdade: queries SQL não são só linhas de código, é tipo um papo reto com o banco de dados. Se você manda um "SELECT *" de boa, o banco provavelmente entende e executa sem reclamar. Mas se você joga um romance SQL todo bagunçado, o banco pode ficar pensando... e aí começa a travar.

Otimizar queries é saber falar com o banco de um jeito claro e direto. Quando a query tá bem escrita e eficiente, ela roda rapidinho, não pesa no sistema e não atrapalha os outros processos. Agora, se a query tá mal feita, pode deixar tudo lento: o banco começa a gastar mais CPU e memória, o disco fica lendo e escrevendo à toa, e até os apps que usam o banco começam a engasgar.

EXPLAIN ANALYZE ajuda a achar esses pontos problemáticos e entender onde a query tá "sofrendo". É tipo um diagnóstico — sem ele, fica difícil tratar a performance.

Problemas comuns nas queries e como achar eles

Agora bora conhecer os suspeitos de sempre quando a performance cai. Pra isso, vamos usar o comando EXPLAIN ANALYZE.

Problema 1: Sequential Scan (Seq Scan)

Seq Scan (varredura sequencial) é quando o PostgreSQL procura os dados passando por cada linha da tabela. Até vai se a tabela for pequena, mas em tabelas grandes isso é sofrido.

Como saber se tá rolando Seq Scan? Só rodar a análise com EXPLAIN ANALYZE. Exemplo:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

O resultado pode ser assim (olha o Seq Scan):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Como resolver?

Crie um índice em student_id, se ainda não tiver:

CREATE INDEX idx_student_id ON students(student_id);

Depois disso, roda de novo o EXPLAIN ANALYZE. Você deve ver Index Scan no lugar do Seq Scan.

Problema 2: baixa seletividade das condições

Seletividade é quantas linhas precisam ser processadas pra achar o que você quer. Se seu filtro pega quase a tabela toda, índice não vai ajudar.

Exemplo de query com baixa seletividade:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Se 90% dos estudantes da tabela fazem Computer Science, a query pode usar Seq Scan mesmo tendo índice em program.

Como melhorar a query?

1. Reveja a lógica da query: talvez você precise refinar o filtro, colocando mais condições.
2. Confere se as estatísticas da tabela estão atualizadas (isso ajuda o PostgreSQL a calcular a seletividade direito):

ANALYZE students;

3. Se a query tá usando índice sem motivo, tenta forçar o PostgreSQL a usar ele:

SET enable_seqscan = OFF;

Problema 3: operações de ordenação desnecessárias

Ordenar (Sort) pode ser caro, principalmente se os dados não cabem na RAM. Um caso clássico é o ORDER BY.

Exemplo de problema:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Você pode ver algo assim:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Como deixar a ordenação mais rápida? Se você sempre ordena por uma coluna, pode criar um índice:

CREATE INDEX idx_last_name ON students(last_name);

Agora o PostgreSQL pode usar o índice pra trazer os dados já ordenados, sem precisar ordenar depois.

Problema 4: Falta de limites (LIMIT)

Quando você faz um SELECT sem limitar o número de linhas, a query pode varrer a tabela toda, mesmo que só precise da primeira linha.

Como isso aparece:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Se o banco tem um milhão de linhas e o filtro gpa > 3.5 pega 80% da tabela, você vai esperar sentado.

Se só precisa dos 10 melhores estudantes, usa LIMIT:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Além disso, junto com LIMIT dá pra usar OFFSET pra fazer paginação.

Gerenciando parâmetros de execução: SET

O comando SET no PostgreSQL serve pra mudar parâmetros da sessão ou da execução da query. É tipo um ajuste temporário, que só vale pra conexão atual.

Resumindo, SET é um jeito de mudar o "humor" do PostgreSQL na hora, sem mexer nas configs globais.

Onde usar isso?

• Mudar idioma ou formato de data antes de rodar um relatório.
• Aumentar a memória pra uma query pesada.
• Desligar logs durante um carregamento em massa.
• Mudar temporariamente o search_path.
• Gerenciar segurança (tipo baixar o nível de privilégio do usuário por um tempo).

Sintaxe geral

SET parâmetro = valor;

Pra ver o valor atual do parâmetro, usa:

SHOW parâmetro;

Pra voltar pro valor padrão:

RESET parâmetro;

Exemplo de otimização completa

Imagina que você precisa achar os 10 últimos estudantes com o maior GPA, que fazem Computer Science. Olha a query original:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Análise da query: Primeiro, roda o EXPLAIN ANALYZE:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Se aparecer varredura sequencial e ordenação, é hora de otimizar.

2. Índice no filtro e na ordenação:

   Crie um índice composto com as duas colunas:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Verificando melhorias:

   Roda de novo o EXPLAIN ANALYZE. Agora a query deve usar esse índice, sem precisar ordenar ou fazer varredura sequencial.

Metodologia de otimização de queries

1. Comece analisando o plano de execução atual. Use EXPLAIN ANALYZE pra achar operações problemáticas.

2. Ache os gargalos. Procure os nós do plano que mais gastam tempo ou recursos.

3. Crie índices. Veja quais colunas são usadas em filtros e ordenações, e crie os índices necessários.

4. Reduza o volume de dados. Use LIMIT, OFFSET e filtros precisos.

5. Atualize as estatísticas. Rode ANALYZE pra garantir que o PostgreSQL tem info fresca sobre os dados.

6. Teste as mudanças. Depois de otimizar, rode de novo o EXPLAIN ANALYZE pra ver se melhorou a performance.

E agora?

Você acabou de passar por um intensivão de otimização de queries. Parabéns! Quanto mais você brincar com o EXPLAIN ANALYZE, mais vai sacar como o PostgreSQL funciona por dentro. E lembra: nenhum índice mágico salva uma query se ela for muito complicada ou mal escrita. SQL, igual qualquer linguagem, gosta de clareza.