Como administradores de banco de dados, precisamos ficar obcecados com as ferramentas e métodos para aprimorar o desempenho do banco de dados.
No PostgreSQL, temos acesso ao comando EXPLAIN ANALYZE que nos permite analisar o plano de execução e o desempenho de uma determinada consulta ao banco de dados. O comando retorna informações detalhadas sobre como o mecanismo de banco de dados processa a consulta. Isso inclui a sequência de operações executadas, custos estimados de consulta, tempo de execução e muito mais.
Podemos usar essas informações para identificar as consultas de banco de dados, bem como identificar e corrigir os possíveis gargalos de desempenho.
Este tutorial discute como usar o comando EXPLAIN ANALYZE no PostgreSQL para visualizar e otimizar o desempenho da consulta.
PostgreSQL EXPLICAR ANALISAR
O comando é bem direto. Primeiro, precisamos preceder o comando EXPLAIN ANALYZE no início da consulta que desejamos analisar.
A sintaxe do comando é a seguinte:
EXPLICAR ANALISARDepois de executar o comando, o PostgreSQL retorna uma saída detalhada sobre a consulta fornecida.
Compreendendo a saída da consulta EXPLAIN ANALYZE
Como mencionado, uma vez executado o comando EXPLAIN ANALYZE, o PostgreSQL gera um relatório detalhado do plano de consulta e as estatísticas de execução.
A saída é composta por um conjunto de colunas que contêm informações úteis. As colunas resultantes são mostradas com seus respectivos significados:
PLANO DE CONSULTA – Esta coluna exibe o plano de execução da consulta especificada. O plano de execução refere-se a uma sequência de operações que o mecanismo de banco de dados executa para concluir a consulta com êxito.
PLANO – A segunda coluna é a coluna PLAN. Contém uma representação textual de cada operação ou etapa no plano de execução. Novamente, cada operação é recuada para indicar a hierarquia das operações.
CUSTO TOTAL – A coluna de custo total representa o custo total estimado da consulta. O custo refere-se a uma medida relativa que o planejador de consulta de banco de dados usa para determinar o plano de execução ideal.
LINHAS REAIS – Esta coluna mostra o número exato de linhas que são processadas em cada etapa da execução da consulta.
TEMPO REAL – Esta coluna mostra o tempo real gasto por cada operação que inclui tanto o tempo de execução da operação quanto o tempo gasto em recursos.
TEMPO DE PLANEJAMENTO – Esta coluna mostra o tempo que o planejador de consulta leva para gerar um plano de execução. Isso inclui o tempo total de otimização da consulta e geração do plano.
TEMPO DE EXECUÇÃO – Esta coluna mostra o tempo total para executar a consulta. Isso também inclui o tempo gasto no planejamento e o tempo de execução da consulta.
PostgreSQL EXPLICAR ANALISAR Exemplo
Vejamos alguns exemplos básicos de uso da instrução EXPLAIN ANALYZE.
Exemplo 1: selecione a declaração
Vamos usar a instrução EXPLAIN ANALYZE para mostrar a execução de uma instrução select simples no PostgreSQL.
Depois de executar a instrução anterior, devemos obter uma saída da seguinte forma:
PLANO DE CONSULTA-------------------------------------------------- -----------------
Seq Scan em wp_users (custo=0.00..10.38 linhas=10 largura=2256) (tempo real=0.009..0.010 linhas=7 loops=1)
Filtro: (id > 3)
Linhas removidas pelo filtro: 3
Tempo de planejamento: 0,995 ms
Tempo de execução: 0,021 ms
(5 linhas)
Nesse caso, podemos ver que a seção Plano de consulta indica que a consulta executa uma varredura sequencial na tabela wp_users. A linha de filtro denota a condição usada para filtrar as linhas resultantes.
Em seguida, vemos o “Rows Removed by Filter” que mostra o número de linhas que são eliminadas pela condição do filtro.
Por fim, o tempo de execução mostra o tempo total de execução da consulta. Nesse caso, a consulta leva 0,021 ms.
Exemplo 2: analisando uma junção
Vamos fazer uma consulta mais complexa que envolve uma junção SQL. Para isso, utilizamos o banco de dados de exemplo Pagila. Você pode baixar e instalar o banco de dados de amostra em sua máquina para fins de demonstração.
Podemos executar uma junção simples, conforme mostrado a seguir:
explicar analisar SELECT f.title, c.nameDO filme f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN categoria c ON fc.category_id = c.category_id;
Depois de executar a consulta fornecida, devemos ver a saída da seguinte forma:
Vamos explorar o seguinte plano de consulta:
- Loop aninhado – indica que a junção usa uma estratégia de junção de loop aninhado.
- Hash Join – Esta operação une as tabelas film_category e film usando um algoritmo Hash join. Esta operação tem um custo de 77,50 e estimativa de 1000 linhas. No entanto, o tempo real gasto para esta operação é de 0,254 a 0,439 milissegundos e recupera 1.000 linhas.
- Hash Cond – Isso indica que a condição de junção usa uma junção Hash para corresponder às colunas film_id e às colunas film_category nas tabelas de filmes.
- Seq Scan on film_category – Esta operação executa uma varredura sequencial na tabela film_category com um custo de 16,00 e estimativa de 1.000 linhas. O tempo real gasto para esta operação é de 0,008 a 0,056 milissegundos e recupera 1.000 linhas.
- Seq Scan on film – A consulta realiza uma varredura sequencial na mesa de filmes com os custos estimados e reais resultantes e linhas nesta operação.
- Memoize – Esta operação armazena em cache os resultados da junção entre film_category e tabelas de filmes para uso posterior.
- Chave de cache – Isso indica que a chave de cache usada para memoização é baseada na coluna category_id de film_category.
- Cache Mode – Isso indica que a consulta usa o modo de cache lógico.
- Hits, Misses, Evictions, Overflows – As três linhas fornecem estatísticas sobre o cache, número de hits, misses, despejos e overflows durante a execução. Este bloco também inclui o uso de memória durante a execução da consulta.
- Varredura de índice usando category_pkey – Isso mostra a operação que executa uma varredura de índice na tabela de categoria usando o índice de chave primária.
- Index Cond – Isso mostra que a varredura de índice é baseada na condição que corresponde à coluna category_id na tabela de categoria.
- Planning Time – Esta linha mostra o tempo gasto para o planejamento da consulta, que é de 3,005 milissegundos.
- Tempo de Execução – Por fim, esta linha mostra o tempo total de execução da consulta que é de 0,745 milissegundos.
Aí está! Uma informação detalhada sobre a execução de uma junção simples no PostgreSQL.
Conclusão
Você descobriu o poder e o uso da instrução EXPLAIN ANALYZE no PostgreSQL. A instrução EXPLAIN ANALYZE é uma ferramenta poderosa para análise e otimização de consultas. Use essa ferramenta para criar consultas eficientes e com menos recursos.