O desempenho dos comandos pode ser afetado por vários motivos. Alguns destes motivos podem ser tratados pelo usuário, enquanto outros são inerentes ao projeto do sistema subjacente. Este capítulo fornece algumas dicas para compreender e ajustar o desempenho do PostgreSQL.
O PostgreSQL concebe um plano de comando para cada comando recebido. A escolha do plano correto, correspondendo à estrutura do comando e às propriedades dos dados, é absolutamente crítico para o bom desempenho. Pode ser utilizado o comando EXPLAIN para ver o plano criado pelo sistema para qualquer comando. A leitura do plano é uma arte que merece um tutorial extenso, o que este não é; porém, aqui 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 de poder começar a varrer a saída como, por exemplo, o tempo para fazer a classificação em um nó de classificação).
O custo total estimado (Se todas as linhas fossem buscadas, o que pode não acontecer: uma consulta contendo a cláusula LIMIT pára antes de gastar o custo total, por exemplo).
Número de linhas de saída estimado para este nó do plano (Novamente, somente se for executado até o fim).
Largura média estimada (em bytes) das linhas de saída deste nó do plano.
Os custos são medidos em termos de unidades de páginas de disco buscadas (O esforço de CPU estimado é convertido em unidades de páginas de disco, utilizando fatores estipulados altamente arbitrários. Se for desejado realizar experiências com estes fatores, consulte a lista de parâmetros de configuração em tempo de execução na Seção 16.4.5.2.)
É importante notar que o custo de um nó de nível mais alto inclui o custo de todos os seus nós descendentes. Também é importante perceber que o custo reflete apenas as coisas com as quais o planejador/otimizador se preocupa. Em particular, o custo não considera o tempo gasto transmitindo as linhas do resultado para o cliente, que pode ser o fator predominante no computo do tempo total gasto, mas que o planejador ignora porque não pode mudá-lo alterando o plano (Todo plano correto produz o mesmo conjunto de linhas, assim se acredita).
Linhas de saída é um pouco enganador, porque não é o número de linhas processadas/varridas pelo comando, geralmente é menos, refletindo a seletividade estimada de todas as condições da cláusula WHERE aplicadas a este nó. Idealmente, a estimativa de linhas do nível superior estará próxima do número de linhas realmente retornadas, atualizadas ou excluídas pelo comando.
Abaixo seguem alguns exemplos (utilizando o banco de dados de teste de regressão após a execução do comando VACUUM ANALYZE, e os códigos fonte de desenvolvimento da versão 7.3):
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)
Isto é tão direto quanto parece. Se for executado
SELECT * FROM pg_class WHERE relname = 'tenk1';
será visto que tenk1 ocupa 233 páginas de disco e possui 10.000 linhas. Portanto, o custo é estimado em 233 páginas lidas, definidas como custando 1.0 cada uma, mais 10.000 * cpu_tuple_cost que é atualmente 0.01 (execute SHOW cpu_tuple_cost para ver) (233 + 10.000 * 0,01 = 233 + 100 = 333 - N. do T.).
Agora a consulta será modificada para incluir uma condição WHERE:
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 de saída diminuiu por causa da cláusula WHERE. Entretanto, a varredura ainda precisa percorrer todas as 10.000 linhas e, 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 verdadeiro de linhas que esta consulta deveria selecionar é 1.000, mas a estimativa é somente aproximada. Se for tentado repetir esta experiência, 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-se a consulta para restringir mais ainda a condição
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 quando fazemos a condição WHERE seletiva o bastante, o planejador decide, finalmente, que a varredura do índice tem custo menor que a varredura seqüencial. Este plano necessita buscar apenas 50 linhas por causa do índice e, portanto, vence 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 condição à cláusula WHERE:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND 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 condição adicionada stringu1 = 'xxx' reduz a estimativa de linhas de saída mas não o custo, porque deverá ser acessado o mesmo conjunto de linhas. Deve ser observado que a cláusula stringu1 não pode ser aplicada como uma condição do índice (porque o índice contém apenas a coluna unique1). Em vez disto, é aplicada como um filtro nas linhas trazidas 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 as colunas sendo discutidas:
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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 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 da varredura externa corrente é vinculado à varredura interna do índice para produzir uma condição de índice como t2.unique2 = constante. Portanto, é obtido o mesmo plano e custo para a varredura interna que seria obtido por, digamos, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. Os custos do nó do laço são então definidos tomando por base o custo da varredura externa, mais uma 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 de saída da junção é igual ao produto da quantidade de linhas das duas varreduras, mas isto usualmente não é verdade porque, em geral, podem existir cláusulas WHERE fazendo menção às duas tabelas e, portanto, só podem ser aplicadas no ponto de junção, e não às duas varreduras de entrada. Por exemplo, se fosse adicionado 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 de ver outros planos é forçar o planejador a não considerar a estratégia que sairia vencedora, ativando e desativando sinalizadores de cada tipo de plano (Esta é uma ferramenta deselegante, mas útil. Consulte também a Seção 13.3).
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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, armazená-las para uso posterior em uma tabela de dispersão (hash table) em memória e, então, fazer uma varredura seqüencial em tenk2 procurando possíveis correspondências na tabela de dispersão para t1.unique2 = t2.unique2 para cada linha de tenk2. O custo para ler tenk1 e montar a tabela de dispersão é inteiramente custo de partida para a junção hash, porque não haverá nenhuma linha de saída 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 de dispersão 10.000 vezes. Entretanto, deve ser observado que não está sendo cobrado 10.000 * 179,33; a montagem da tabela de dispersão é feita somente uma vez neste tipo de plano.
É possível verificar a precisão dos custos estimados pelo planejador utilizando o comando EXPLAIN ANALYZE. Na verdade este comando executa a consulta, e depois mostra o tempo real acumulado dentro de cada nó do plano junto com os custos estimados que o comando EXPLAIN simples mostraria. Por exemplo, poderia ser obtido um resultado como este:
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 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.181..29.822 rows=50 loops=1)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
(actual time=0.630..8.917 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.295..0.324 rows=1 loops=50)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 31.604 ms
Deve ser observado que os valores de "actual time" são em milissegundos de tempo real, enquanto as estimativas de "custo" (cost) 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 comando é possível que um nó de subplano seja executado mais de uma vez. Por exemplo, a varredura de índice interna é executada uma vez para cada linha externa no plano de laço aninhado acima. Nestes casos, o valor de "loops" (laços) expressa o número total de execuções do nó, e os valores de "actual time" (tempo real) e "rows" (linhas) mostrados são valores médios por execução. Isto é feito para tornar os números comparáveis com o modo como as estimativas de custo são mostradas. Deve ser multiplicado pelo valor de "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 as linhas do resultado. Não inclui os tempos de análise, reescrita e planejamento. Para um comando SELECT, o tempo total de execução normalmente será apenas um pouco maior que o tempo total informado para o nó do plano de nível mais alto. 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 do resultado. 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 as alterações. O tempo gasto disparando os gatilhos, se houver algum, está fora do nó superior do plano, sendo mostrado separadamente para cada gatilho.
Vale a pena notar que os resultados do comando 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 a uma tabela grande. As estimativas de custo do planejador não são lineares e, portanto, podem ser escolhidos planos diferentes para tabelas maiores ou menores. Um exemplo extremo é o de uma tabela que ocupa uma única página em disco, onde quase sempre vence o plano de varredura seqüencial, havendo í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.
