Usando EXPLAIN ANALYZE pra medir o tempo real de execução das queries

Se o comando EXPLAIN deixa você dar aquela espiada na bola de cristal pra ver como o PostgreSQL “planeja” rodar a query, o EXPLAIN ANALYZE te transforma num verdadeiro detetive, descobrindo o que realmente rolou.

Principais diferenças entre EXPLAIN e EXPLAIN ANALYZE:

EXPLAIN – é a teoria, mostrando como o PostgreSQL planeja executar a query. Você vê valores estimados, tipo quantidade de linhas (rows) e custo de execução (cost).

EXPLAIN ANALYZE – é a prática. O PostgreSQL realmente executa a query e mostra:

• Quantidade real de linhas processadas em cada etapa.
• Tempo real de execução de cada operação.
• Comparação com as suposições do plano (rows e cost).

Exemplo: se sua query acha que vai processar 100 linhas, mas na real processa 10.000 linhas, o EXPLAIN ANALYZE já entrega esse fato na lata!

Sintaxe básica e como usar

Assim como o EXPLAIN, o EXPLAIN ANALYZE é fácil de usar. Só colocar a palavra ANALYZE junto do seu comando EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

O que o PostgreSQL faz:

• Ele executa a query.
• Registra cada operação no plano de execução, incluindo os dados reais.
• Retorna a descrição completa do processo de execução da query.

Que dados o EXPLAIN ANALYZE mostra?

Tempo real de execução das operações:

• Actual Start Time: quando a operação começou.
• Actual End Time: quando a operação terminou.

Total de linhas processadas:

Isso ajuda a ver se as suposições do plano (valores de rows) batem com a realidade.

Info sobre buffers:

Como os buffers de disco e memória foram usados.

Exemplo de uso do EXPLAIN ANALYZE

Bora ver um exemplo prático. Temos uma tabela students com dados dos estudantes:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Vamos rodar uma query pra pegar os estudantes com mais de 20 anos:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Exemplo de resultado:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Bora entender o resultado:

• Seq Scan – mostra que o PostgreSQL fez um scan sequencial na tabela.
• cost=0.00..14.00 – é o custo estimado da operação.
• rows=2 – o PostgreSQL achou que a query ia retornar 2 linhas (e acertou!).
• actual time=0.025..0.026 – tempo real da operação (em milissegundos).
• Rows Removed by Filter: 2 – duas linhas foram filtradas porque não batiam com o WHERE.

Comparando teoria e prática

Aí que tá a mágica do EXPLAIN ANALYZE: ele mostra como a query foi realmente executada e ainda deixa comparar com o plano teórico.

Bora ver um exemplo mais complexo.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Exemplo de resultado:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

O que vemos:

1. O PostgreSQL executou a query em 0.057 milissegundos.
2. Só uma linha (rows=1) bateu com o WHERE.
3. Três linhas foram filtradas (Rows Removed by Filter: 3).

Resumo

Usar o EXPLAIN ANALYZE te ajuda a achar gargalos e entender como otimizar as queries. Por exemplo:

• Se o Seq Scan tá muito "pesado", talvez seja hora de criar um índice.
• Se as suposições do PostgreSQL são bem diferentes dos dados reais, vale conferir as estatísticas da tabela (ANALYZE) ou a estrutura dos índices.