Identificando gargalos em query no Postgresql

[FelipeVerbanek]

Você já teve uma query que demora mais do que o normal, e você não sabe onde começar para conseguir identificar o problema ?
Nesse tópico gostaria de apresentar uma forma que utilizo para analisar uma query e identificar onde está sendo gerado a lentidão.

Verificar plano de execução

Um dos primeiros passos é analisar o plano de execução da query, nesse caso precisamos utilizar o explain analyze, esse comando mostra o plano de execução de forma distribuída, onde é possível analisar os custos computacionais de cada operação do SQL.

Para exemplificar, vamos utilizar um exemplo com duas tabelas, onde a tabela "teste" possui milhões de registros.

Query utilizada:

Resultado:

 

Será retornando varias instruções com todo o plano de execução da query, e com esse resultado podemos analisar as tabelas e filtros que estão mais lentos.

Nesse cenário que testamos é possível identificar a métrica "Rows Removed by Filter" super elevada, ou seja, a query precisou remover 6666670 linhas para conseguir encontrar o registro. Nesse caso talvez um índice resolva nosso problema.

Em um cenário que temos uma query muito complexa, fica muito difícil conseguir analisar o plano de execução dessa forma, e nessas ocasiões indico a plataforma https://tatiyants.com/pev/#/plans.

Vamos ver um exemplo utilizando a plataforma tatiyants

Primeiramente precisamos executar o explain em nossa query com o seguinte comando:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

O resultado ficou da seguinte forma:

 

Será retornado o campo no formato json, e no site https://tatiyants.com/pev/#/plans preenchemos os dados com o json e também com a query que utilizamos.

 

Ao clicar em submit, será retornado um diagrama que é possível visualizar as bifurcações da query.

 

Também é possível clicar no card onde está indicando lentidão, e podemos visualizar as métricas.

 

 

Agora vamos finalizar criando um index chamado "teste_a_idx" para o campo "a" da tabela "teste", e realizar o processo utilizando o explain analyze novamente.

Resultado:

Comando utilizado para criar o index:
CREATE INDEX teste_a_idx ON public.teste (a);

 

Após a criação do index, conseguimos visualizar que o index teste_a_idx já está sendo utilizado na query e também observar que houve um ganho de performance na busca do resultado.
Não será em todos os cenários que a criação de um index será a solução, isso vai depender do problema que você está resolvendo. Use index com moderação!

 

Você trabalhar de uma forma diferente ? deixe nos comentários suas dicas!