10.1. Utilização do comando EXPLAIN

O PostgreSQL concebe um plano de consulta para cada consulta solicitada. A escolha do plano correto, correspondendo à estrutura da consulta e às propriedades dos dados, é absolutamente crítico para o bom desempenho. Para toda consulta pode ser utilizado o comando EXPLAIN, para ver o plano criado pelo sistema. A leitura de um plano é uma arte e merece um tutorial extenso, que este não é; neste capítulo são fornecidas algumas informações básicas.

Os números apresentados atualmente pelo EXPLAIN são:

• O custo de partida estimado (O tempo gasto antes da varredura da saída poder começar como, por exemplo, o tempo para fazer a ordenação em um nó de ordenação).

• O custo total estimado (Se todas as linhas forem buscadas, que pode não acontecer --- uma consulta contendo a cláusula LIMIT não gastará o custo total, por exemplo).

• Número de linhas de saída estimado para este nó do plano (Novamente, apenas quando executado até o fim).

• Largura média estimada (em bytes) das linhas de saída para este nó do plano.

Os custos são medidos em termos de unidades de páginas buscadas no disco (O esforço de CPU estimado é convertido em unidades de página de disco utilizando fatores estipulados altamente arbitrários. Se for desejado realizar experiências com estes fatores, deve ser consultada a lista de parâmetros de configuração em tempo de execução no Guia do Administrador do PostgreSQL.)

É importante perceber que o custo do nível mais alto inclui todos os custos de seus descendentes. Também é importante perceber que o custo reflete apenas as partes com as quais o planejador/otimizador se preocupa. Em particular, o custo não considera o tempo gasto transmitindo o resultado para o cliente --- que pode ser um fator importante no computo do tempo total gasto, mas que o planejador ignora porque não é alterado pela mudança de plano (Todo plano correto produz o mesmo conjunto de linhas, acredita-se).

Linhas produzidas é um assunto delicado porque não é o número de linhas processadas/varridas pela comando --- geralmente é menos, refletindo a seletividade estimada das restrições de certas cláusulas WHERE aplicadas. Idealmente, as linhas de nível superior estimam de forma aproximada o número de linhas realmente retornadas, atualizadas ou excluídas pelo comando.

A seguir são apresentados alguns exemplo (utilizando o banco de dados de teste de regressão após a execução do comando VACUUM ANALYZE, e os fontes de desenvolvimento da 7.3):

regression=# EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

Este resultado é tão simples quanto parece. Se for executado

SELECT * FROM pg_class WHERE relname = 'tenk1';

será visto que tenk1 possui 10.000 linhas e ocupa 233 páginas de disco. Portanto, o custo é estimado em 233 páginas lidas, definidas como custando 1.0 cada uma, vezes 10.000 * cpu_tuple_cost que vale atualmente 0.01 (tente executar SHOW cpu_tuple_cost).

Agora a consulta será modificada para incluir uma condição WHERE:

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

A estimativa de linhas produzidas diminuiu devido à cláusula WHERE. Entretanto, a varredura ainda precisa percorrer todas as 10.000 linhas, portanto o custo não diminuiu; na verdade aumentou um pouco, para refletir o tempo a mais de CPU gasto verificando a condição WHERE.

O número correto de linhas que esta consulta deveria selecionar é 1.000, mas a estimativa é somente aproximada. Se a repetição desta experiência for tentada, provavelmente será obtida uma estimativa ligeiramente diferente; além disso, mudanças ocorrem após cada comando ANALYZE, porque as estatísticas produzidas pelo ANALYZE são obtidas a partir de amostras aleatórias na tabela.

Modificando a consulta para restringir mais ainda a condição

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

será visto que a condição WHERE ficou bastante seletiva, e o planejador no final decidirá que a varredura do índice é mais barata que a varredura seqüencial. Este plano necessita ler apenas 50 linhas devido ao índice, saindo vencedor apesar do fato de cada busca individual ser mais cara que a leitura de toda a página do disco seqüencialmente.

Adição de outra cláusula à condição WHERE:

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# stringu1 = 'xxx';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

A cláusula adicionada stringu1 = 'xxx' reduz a estimativa de linhas produzidas mas não o custo, porque deverá ser lido o mesmo conjunto de linhas. Observe que a cláusula stringu1 não pode ser aplicada como uma condição do índice (porque o índice abrange apenas a coluna unique1). Em vez disto, esta cláusula é aplicada como um filtro nas linhas obtidas pelo índice. Portanto, o custo na verdade sobe um pouco para refletir esta verificação adicional.

A seguir é feita a junção de duas tabelas, utilizando os campos sendo discutidos:

regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

Nesta junção de laço aninhado, a varredura externa é a mesma varredura de índice vista no penúltimo exemplo e, portanto, seu custo e quantidade de linhas são os mesmos porque está sendo aplicada a cláusula unique1 < 50 na condição WHERE neste nó. A cláusula t1.unique2 = t2.unique2 ainda não é relevante e, portanto, não afeta a quantidade de linhas da varredura externa. Para a varredura interna, o valor de unique2 da linha corrente da varredura externa é conectado na varredura interna do índice para produzir uma condição de índice do tipo t2.unique2 = constante. Portanto, seria obtido o mesmo plano e custo para a varredura interna que seria obtido por, digamos, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. O custo do nó do laço é então estabelecido tomando por base o custo da varredura externa, mais a repetição da varredura interna para cada linha externa (49 * 3.01, neste caso), mais um pouco de tempo de CPU para o processo de junção.

Neste exemplo, a quantidade de linhas do laço é igual ao produto da quantidade de linhas das duas varreduras, mas isto não é verdade usualmente porque, em geral, podem existir cláusulas WHERE fazendo menção às duas relações e, portanto, só podem ser aplicadas no ponto de junção, e não nas duas varreduras de entrada. Por exemplo, se fosse incluído WHERE ... AND t1.hundred < t2.hundred, faria diminuir a quantidade de linhas de saída do nó da junção, mas não mudaria nenhuma das varreduras da entrada.

Uma forma para ver planos alternativos é forçar o planejador não levar em consideração a estratégia que seria a vencedora, ativando e desativando os sinalizadores de cada tipo de plano (Esta é uma ferramenta deselegante, mas útil. Veja também a Seção 10.3).

regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

Este plano propõe extrair as 50 linhas que interessam de tenk1, usando a mesma varredura de índice anterior, armazenar estas linhas em uma tabela hash na memória e, então, executar uma varredura seqüencial em tenk2, comparando cada linha de tenk2 com a tabela hash para verificar possíveis correspondências de t1.unique2 = t2.unique2. O custo para ler tenk1 e preparar a tabela hash é inteiramente custo de partida para a junção hash, porque não sairá nenhuma linha até começar a leitura de tenk2. O tempo total estimado para a junção também inclui uma pesada carga de tempo de CPU para verificar a tabela hash 10.000 vezes. Entretanto deve ser observado que não está sendo computado 10.000 vezes 179.33; a montagem da tabela hash é feita somente uma vez neste tipo de plano.

É possível verificar a precisão dos custos estimados pelo planejador utilizando o comando EXPLAIN ANALYZE. Este comando na verdade executa a consulta, e depois mostra o tempo real acumulado dentro de cada nó do plano junto com os mesmos custos estimados que o comando EXPLAIN simples mostraria. Por exemplo, poderia ser obtido um resultado como este:

regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.18..29.82 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.63..8.91 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.29..0.32 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.60 msec

Deve ser observado que os valores de "actual time" (tempo real) são em milissegundos de tempo real, enquanto as estimativas de "cost" (custo) são expressas em unidades arbitrárias de busca em disco; portanto, não é provável haver correspondência. É nas relações que se deve prestar atenção.

Em alguns planos de consulta é possível que o nó de um subplano seja executado mais de uma vez. Por exemplo, a varredura do índice interno é executada uma vez para cada linha externa no plano de laço aninhado acima. Nestes casos, o valor "loops" indica o número total de execuções do nó, enquanto os valores tempo real (actual time) e linhas (rows) mostram médias por execução. Isto é feito para tornar os números comparáveis com o modo como os custos estimados são mostrados. Deve ser multiplicado pelo valor "loops" para obter o tempo total realmente gasto no nó.

O Total runtime (tempo total de execução) mostrado pelo EXPLAIN ANALYZE inclui os tempos de inicialização e de finalização do executor, assim como o tempo gasto processando os resultados das linhas. Não inclui os tempos de análise, re-escrita e planejamento. Para um comando SELECT, o tempo total de execução normalmente será apenas um pouco maior que o tempo total relatado pelo nó de nível mais alto do plano. Para os comandos INSERT, UPDATE e DELETE , o tempo total de execução pode ser consideravelmente maior, porque inclui o tempo gasto processando as linhas resultantes. Nestes comandos, o tempo para o nó superior do plano é, essencialmente, o tempo gasto computando as novas linhas e/ou localizando as linhas antigas, mas não inclui o tempo gasto realizando estas alterações.

Vale a pena observar que os resultados do EXPLAIN não devem ser extrapolados para outras situações além da que está sendo testada; por exemplo, não é possível supor que os resultados para uma tabela pequena possam ser aplicados em uma tabela grande. Os custos do planejador não são lineares e, portanto, podem ser escolhidos planos diferentes para tabelas grandes e tabelas menores. Um exemplo extremo é o caso de uma tabela que só ocupa uma página do disco, onde quase sempre vence o plano de varredura seqüencial, estejam os índices disponíveis ou não. O planejador percebe que fará a leitura de uma página do disco para processar a tabela em qualquer caso e, portanto, não faz sentido fazer leituras de páginas adicionais para procurar em um índice.