Análise de performance de funções e procedimentos: usando EXPLAIN ANALYZE

EXPLAIN ANALYZE te ajuda a entender como o PostgreSQL "pensa" quando executa sua query:

• Quais passos são feitos pra processar os dados.
• Quanto tempo cada passo leva.
• Por que uma query tá lenta — seja por um scan completo na tabela (Seq Scan) ou por um índice que não foi usado.

O comando EXPLAIN ANALYZE realmente executa a query e mostra como o PostgreSQL otimiza a execução. Imagina que você desmonta um relógio pra entender como o mecanismo funciona. É isso que o EXPLAIN ANALYZE faz, só que com suas queries SQL.

Sintaxe do EXPLAIN ANALYZE

Vamos começar do básico. Olha só como é o comando:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Essa query executa o SELECT e mostra como o PostgreSQL processa os dados.

O resultado do EXPLAIN ANALYZE é uma árvore de execução da query. Cada nível da árvore mostra um passo que o PostgreSQL faz:

• Operation Type — tipo da operação (tipo Seq Scan, Index Scan).
• Cost — o quanto o PostgreSQL acha que custa executar essa operação.
• Rows — quantas linhas ele espera e quantas realmente vieram no resultado.
• Time — quanto tempo levou a operação.

Exemplo de saída:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Repara no Seq Scan on students. Isso quer dizer que o PostgreSQL tá olhando TODAS as linhas da tabela students. Se a tabela for grande, isso pode ser MUITO LENTO.

Exemplos de uso do EXPLAIN ANALYZE

Bora ver uns exemplos práticos pra você aprender a achar e resolver problemas nas queries.

Exemplo 1: scan completo na tabela

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Saída:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

O problema aqui é que o PostgreSQL faz um Seq Scan, ou seja, passa por todas as linhas da tabela. Se tiver milhões de linhas, isso vira um gargalo de performance.

Solução: vamos criar um índice na coluna age.

CREATE INDEX idx_students_age ON students(age);

Agora executa a mesma query:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Saída:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Agora a gente vê Index Scan no lugar do Seq Scan. Aí sim, agora a query voa!

Exemplo 2: query com JOIN

Imagina que temos duas tabelas: students e courses. Queremos saber os nomes dos estudantes e os nomes dos cursos em que eles estão matriculados.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

A saída pode ser mais ou menos assim:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Viu só? O PostgreSQL tá usando índices nas tabelas enrollments e courses, e a execução é rápida. Mas se faltar algum índice, você pode ver um Seq Scan, e aí a coisa fica lenta.

Otimização de performance de funções

Agora imagina que temos uma função que retorna a lista de estudantes com idade maior que um valor:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

A gente pode analisar a performance dessa função usando EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Deixando a função mais rápida

Se a função estiver lenta, provavelmente o problema é o scan completo na tabela. Pra resolver:

1. Confere se a coluna usada nos filtros (age) tem índice.
2. Olha o número de linhas na tabela e pensa em particionar se tiver dados demais.

Gargalos e como resolver

1. Scan completo nas tabelas (Seq Scan). Usa índices pra acelerar a busca das linhas. Mas cuidado, índice demais pode deixar as inserções mais lentas.

2. Muita linha no resultado. Se a query retorna milhões de linhas, pensa em colocar filtros (WHERE, LIMIT) ou paginação (OFFSET).

3. Operações "caras". Algumas operações, tipo ordenação, agregação ou join de tabelas grandes, podem usar muitos recursos. Usa índices ou quebra a query em etapas menores.