Otimizando consultas no PostgreSQL - Comando EXPLAIN

Por André Milani

Data de Publicação: 27 de Abril de 2008

Existem várias ações para se otimizar uma consulta, independende do banco de dados utilizado. No caso do PostgreSQL existe um comando interessante, e simples de ser usado, que fornece dados para uma análise de custo de tempo que uma consulta irá necessitar para ser realizada. Este mesmo comando pode ser utilizado para comparar diferentes formas de escrever a mesma consulta, visando encontrar o modo que utilize menos recursos possíveis do servidor.

O PostgreSQL disponibiliza um comando que possibilita analisar previamente consultas SQL (nas operações de SELECT, INSERT, UPDATE, DELETE, EXECUTE, e DECLARE) tendo em vista saber como será o comportamento das mesmas, e permitindo também comparar diferentes expressões SQL para descobrir qual a melhor para se atingir um resultado mais rápido.

O comando em questão é o EXPLAIN. Para utilizá-lo, basta acrescentar o termo EXPLAIN antes de sua consulta SQL. Por meio dele, diversas informações sobre o comportamento de uma consulta são exibidos, como mostra o exemplo a seguir.

EXPLAIN SELECT * FROM APLICATIVOS WHERE NOME = 'Softblue' AND GENERO = 'Gerenciamento';
                             QUERY PLAN
—-------------------------------------------------------------------
 Seq Scan on aplicativos  (cost=0.00..17.60 rows=1 width=63)
   Filter: (((nome)::text = 'Softblue'::text) AND ((genero)::text = 'Gerenciamento'::text))

O comando EXPLAIN executa uma análise para cada expressão ou união encontrada na consulta, retornando uma linha (registro) correspondente a sua análise. Neste caso, algumas informações sobre os filtros da cláusula WHERE foram apresentados, e havendo índices, os mesmos também são mencionados sobre suas utilizações no comando.

Um dos indicadores mais importantes deste comando é o retorno da variável cost, a qual informa algo como o custo de esforço que o PostgreSQL utilizou para atingir o objetivo do comando SQL em questão. Quanto menor o seu valor, mais eficiente a sintaxe foi classificada. Se preferir visualizar em milisegundos o tempo de execução que o comando levaria, utilize o comando EXPLAIN com a opção ANALYSE. A última linha de retorno deste comando trará a informação do tempo total que o comando levaria para ser utilizado, bem como o tamanho para realizar cada parte dos JOINS.

Observe o exemplo a seguir, que realiza uma operação INNER JOIN:

EXPLAIN SELECT * FROM CDS INNER JOIN APLICATIVOS ON CDS.ID = APLICATIVOS.CDID;
                             QUERY PLAN
—-------------------------------------------------------------------
 Hash Join  (cost=9.89..43.27 rows=640 width=332)
   Hash Cond: ("outer".cdid = "inner".id)
   ->  Seq Scan on aplicativos  (cost=0.00..14.40 rows=640 width=63)
   ->  Hash  (cost=8.91..8.91 rows=391 width=269)
         ->  Seq Scan on cds  (cost=0.00..8.91 rows=391 width=269)

Note que no resultado do comando EXPLAIN é informado até mesmo o tipo de JOIN que é utilizado no comando em questão (hash no caso), e quais suas condições de uso (qual tabela é a inner e a outer na operação).

André Milani é autor do livro PostgreSQL - Guia do Programador, da editora Novatec, um livro que indica ao leitor todos os passos necessários para conhecer e utilizar esta ferramenta da melhor maneira possível, partindo do básico, para quem não teve ainda nenhum contato com o PostgreSQL, até o nível avançado, servindo como um guia de referência para administradores, por meio de explicações claras e objetivas complementadas com exemplos práticos para cada situação de uso. Destinado a administradores de bancos de dados, programadores, estudantes e a todos que se interessam pelo assunto.

Para saber mais sobre o livro, acesse: http://www.novatec.com.br/livros/postgre