Descrição

Este comando mostra o plano de execução que o planejador do PostgreSQL gera para a instrução especificada. O plano de execução mostra como as tabelas referenciadas pela instrução serão verificadas — por varredura sequencial simples, varredura de índice, etc. — e se forem especificadas várias tabelas, quais algoritmos de junção serão usados para juntar as linhas das diferentes tabelas.

A parte mais importante do que é mostrado é o custo estimado de execução da instrução, representando a estimativa do planejador sobre quanto tempo levará para executar a instrução (medido em unidades de custo que são arbitrárias, mas convencionalmente significam acessos de página no disco). Na verdade, são mostrados dois números: o custo inicial antes que a primeira linha possa ser retornada, e o custo total para retornar todas as linhas. Para a maioria das consultas, o custo total é o que importa, mas em contextos como uma subconsulta com EXISTS, o planejador escolherá o menor custo inicial em vez do menor custo total (já que o executor irá parar após obter uma linha, de qualquer maneira). Além disso, se for limitado o número de linhas retornadas usando a cláusula LIMIT, o planejador fará uma interpolação apropriada entre os dois limites de custo para estimar qual plano é realmente o menos dispendioso.

A opção ANALYZE faz com que a instrução seja realmente executada, não apenas planejada. Em seguida, as estatísticas de tempo de execução real são adicionadas ao que é mostrado, incluindo o tempo decorrido total gasto em cada nó do plano (em milissegundos), e o número total de linhas que realmente retornou. Permite ver se as estimativas do planejador estão próximas da realidade.

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Parâmetros

Saídas

O resultado do comando é uma descrição textual do plano selecionado para a instrução, opcionalmente anotado com estatísticas de execução. Em Uso do comando EXPLAIN estão descritas as informações fornecidas.

Notas

Para permitir que o planejador de consultas do PostgreSQL tome decisões bem informadas ao otimizar as consultas, os dados da tabela pg_statistic devem estar atualizados para todas as tabelas usadas na consulta. Normalmente, o Autovacuum cuida disso automaticamente. Mas se a tabela teve recentemente alterações substanciais em seu conteúdo, talvez seja necessário executar o comando ANALYZE manualmente, em vez de esperar que a limpeza automática atualize as alterações.

Para medir o custo do tempo de execução de cada nó no plano de execução, a implementação corrente do comando EXPLAIN ANALYZE adiciona uma sobrecarga de criação de perfil à execução da consulta. Como resultado, a execução do comando EXPLAIN ANALYZE em uma consulta às vezes pode levar muito mais tempo do que executar a consulta normalmente. A quantidade de sobrecarga depende da natureza da consulta, bem como da plataforma que está sendo usada. O pior caso ocorre para os nós do plano que, por si só, requerem muito pouco tempo de execução, e em máquinas que possuem chamadas de sistema operacional relativamente lentas para obter a hora do dia.

Exemplos

Para mostrar o plano de uma consulta simples em uma tabela com uma única coluna do tipo inteiro com 10.000 linhas:

EXPLAIN SELECT * FROM foo;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 linha)

A seguir está a mesma consulta, com a saída formatada em JSON:

EXPLAIN (FORMAT JSON) SELECT * FROM foo;

           QUERY PLAN
--------------------------------
 [                             +
   {                           +
     "Plan": {                 +
       "Node Type": "Seq Scan",+
       "Relation Name": "foo", +
       "Alias": "foo",         +
       "Startup Cost": 0.00,   +
       "Total Cost": 155.00,   +
       "Plan Rows": 10000,     +
       "Plan Width": 4         +
     }                         +
   }                           +
 ]
(1 linha)

Se houver um índice e for usada uma consulta com uma condição WHERE indexável, o comando EXPLAIN poderá mostrar um plano diferente:

EXPLAIN SELECT * FROM foo WHERE i = 4;

                         QUERY PLAN
--------------------------------------------------------------
 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
   Index Cond: (i = 4)
(2 linhas)

A seguir está a mesma consulta, mas no formato YAML:

EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';

          QUERY PLAN
-------------------------------
 - Plan:                      +
     Node Type: "Index Scan"  +
     Scan Direction: "Forward"+
     Index Name: "fi"         +
     Relation Name: "foo"     +
     Alias: "foo"             +
     Startup Cost: 0.00       +
     Total Cost: 5.98         +
     Plan Rows: 1             +
     Plan Width: 4            +
     Index Cond: "(i = 4)"
(1 linha)

O formato XML fica como exercício para o leitor.

A seguir está o mesmo plano, com as estimativas de custos suprimidas:

EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;

        QUERY PLAN
----------------------------
 Index Scan using fi on foo
   Index Cond: (i = 4)
(2 linhas)

A seguir está exemplo de um plano de consulta para uma consulta que utiliza uma função de agregação:

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
-------------------------------------------------------------------​--
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 linhas)

A seguir está um exemplo de como usar o comando EXPLAIN EXECUTE para mostrar o plano de execução de uma consulta preparada:

PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

                           QUERY PLAN
--------------------------------------------------------------
 HashAggregate  (cost=10.77..10.87 rows=10 width=12) ↵
                (actual time=0.043..0.044 rows=10.00 loops=1)
   Group Key: foo
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=4
   ->  Index Scan using test_pkey on test  ↵
         (cost=0.29..10.27 rows=99 width=8) ↵
         (actual time=0.009..0.025 rows=99.00 loops=1)
         Index Cond: ((id > 100) AND (id < 200))
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.244 ms
 Execution Time: 0.073 ms
(10 linhas)

Obviamente, os números específicos mostrados aqui dependem do conteúdo real das tabelas envolvidas. Note também que os números, e até mesmo a estratégia de consulta selecionada, podem variar entre as versões do PostgreSQL devido a melhorias no planejador. Além disso, o comando ANALYZE usa amostragem aleatória para estimar as estatísticas dos dados; portanto, é possível que as estimativas de custo sejam alteradas após uma nova execução do comando ANALYZE, mesmo que a distribuição real dos dados na tabela não tenha sido alterada.

Note que o exemplo anterior mostrou um plano “personalizado” para valores de parâmetro específicos fornecidos em EXECUTE. Também se pode querer ver o plano genérico para uma consulta parametrizada, o que pode ser feito com GENERIC_PLAN:

EXPLAIN (GENERIC_PLAN)
  SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

                        QUERY PLAN
----------------------------------------------------------
 HashAggregate  (cost=26.79..26.89 rows=10 width=12)
   Group Key: foo
   ->  Index Scan using test_pkey on test  ↵
         (cost=0.29..24.29 rows=500 width=8)
         Index Cond: ((id > $1) AND (id < $2))
(4 linhas)

Neste caso, o analisador inferiu corretamente que $1 e $2 deveriam ter o mesmo tipo de dados que id, portanto, a falta de informações sobre o tipo de dados do parâmetro do comando PREPARE não era um problema. Em outros casos, pode ser necessário especificar explicitamente os tipos de dados para os símbolos dos parâmetros, o que pode ser feito por meio de conversão de tipo de dados, por exemplo:

EXPLAIN (GENERIC_PLAN)
  SELECT sum(bar) FROM test
    WHERE id > $1::integer AND id < $2::integer
    GROUP BY foo;

Conformidade

Não existe o comando EXPLAIN definido no padrão SQL.

A seguinte sintaxe era usada antes da versão 9.0 do PostgreSQL e ainda tem suporte:

EXPLAIN [ ANALYZE ] [ VERBOSE ] instrução

Note que, nesta sintaxe, as opções devem ser especificadas exatamente na ordem mostrada.

Veja também