EXPLAIN ANALYZE

EXPLAIN ANALYZE é uma ferramenta poderosa no SQL que permite aos desenvolvedores e administradores de bancos de dados analisar como exatamente uma consulta é executada. Ele exibe um plano de execução detalhado, incluindo os índices utilizados, o número de linhas processadas e o tempo gasto.

Como funciona o EXPLAIN ANALYZE?

O comando EXPLAIN ANALYZE executa a consulta e fornece um relatório detalhado sobre sua execução. Diferente do EXPLAIN comum, que apenas gera um plano estimado, o EXPLAIN ANALYZE realmente executa a consulta e mede os valores reais.

Exemplo de consulta:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Exemplo de saída:

Seq Scan on users (cost=0.00..35.50 rows=1 width=64) (actual time=0.032..0.033 rows=1 loops=1)

Filter: (email = 'test@example.com'::text)

Rows Removed by Filter: 999

Planning Time: 0.082 ms

Execution Time: 0.045 ms

Principais elementos da saída

Tipo de execução (Scan type)
- Seq Scan — varredura completa da tabela (lenta para grandes volumes de dados).
- Index Scan — busca através de um índice (mais eficiente).
- Index Only Scan — busca por índice sem acessar a tabela.
- Bitmap Heap Scan — busca combinada usando um bitmap.
Custo estimado (cost=...)
- Representa o custo esperado da operação em unidades virtuais.
- Formato: (custo_inicial..custo_final), onde o primeiro valor é o custo inicial e o segundo é o custo total estimado.
Tempo real (actual time=...)
- Tempo real de execução da operação (em milissegundos).
Número de linhas processadas (rows=...)
- Indica quantas linhas foram retornadas nesta etapa da consulta.
Número de repetições (loops=...)
- Quantas vezes essa operação foi executada.

Otimização com EXPLAIN ANALYZE

Adição de índices
Se a consulta estiver realizando um Seq Scan, pode ser que um índice esteja ausente. Criar um índice pode acelerar a busca:
CREATE INDEX idx_users_email ON users(email);

Após isso, o EXPLAIN ANALYZE< deve mostrar um Index Scan em vez de Seq Scan.
Uso do VACUUM ANALYZE
Se as estatísticas do banco estiverem desatualizadas, as consultas podem ser ineficientes. O comando ANALYZE atualiza as estatísticas:
VACUUM ANALYZE;
Reescrita da consulta
Algumas consultas podem ser reformuladas para que o otimizador do banco de dados utilize um plano mais eficiente. Exemplos:
- - Usar JOIN em vez de subconsultas.
  - Substituir IN por EXISTS.
Particionamento da tabela (Partitioning)
Se a tabela for muito grande, dividi-la em partições (PARTITION) pode acelerar a busca.

Conclusão

O comando EXPLAIN ANALYZE é uma ferramenta essencial para análise e otimização de consultas SQL. Ele ajuda a identificar gargalos de desempenho, determinar quais índices são necessários e aprimorar a estrutura do banco de dados para um funcionamento mais eficiente.

Como funciona o EXPLAIN ANALYZE?

Principais elementos da saída

Otimização com EXPLAIN ANALYZE