Depois de analisados e descartados problemas de bloqueios e as alternativas de diminuição do volume de dados retornados e/ou processados que não são o caso ou não podem ser aplicadas, resta analisar a query mais profundamente. Analisar a consulta será necessário também nas situações em que esta não parece lenta quando executada isoladamente (entre 0,5 ou 1 segundo), mas que por ser executada dezenas de milhares de vezes acaba gerando um gargalo.
Para entender como o banco está processando a consulta, devemos ver o Plano de Execução escolhido pelo SGBD para resolver aquela query. Para fazermos isso, usamos o comando
EXPLAIN, conforme o exemplo a seguir:
Figura 7.7
Ca pí tu lo 7 - D es em pe nh o – T óp ic os s ob re a pl ic aç ão bench=# EXPLAIN SELECT * FROM pgbench_accounts a
INNER JOIN pgbench_branches b ON a.bid=b.bid INNER JOIN pgbench_tellers t ON t.bid=b.bid WHERE a.bid=56;
QUERY PLAN
--- Nested Loop (cost=0.00..296417.62 rows=1013330 width=813)
-> Seq Scan on pgbench_accounts a (cost=0.00..283731.12 rows=101333 width=97) Filter: (bid = 56)
-> Materialize (cost=0.00..19.90 rows=10 width=716) -> Nested Loop (cost=0.00..19.85 rows=10 width=716)
-> Seq Scan on pgbench_branches b (cost=0.00..2.25 rows=1 width=364) Filter: (bid = 56)
-> Seq Scan on pgbench_tellers t (cost=0.00..17.50 rows=10 width=352) Filter: (bid = 56)
O EXPLAIN nos mostra o Plano de Execução da query e os custos estimados. O primeiro nó indica o custo total da query.
Sort (cost=0.00..296417.62 rows=1013330 width=813)
| | | |____ Tamanho estimado de cada registro | | |_____ Número de registros estimado
| |______ Custo total estimado da query
|____ Custo inicial estimado para retornar o primeiro registro
Cada linha no plano com um -> indica uma operação. As demais são informações adicionais. Todas as informações do EXPLAIN sem parâmetros são estimativas. Para obter o tempo real, ele deve executar a query de fato, através do comando EXPLAIN ANALYZE:
Ad m in is tr aç ão d e B an co d e D ad os
bench=# EXPLAIN (ANALYZE) SELECT *
FROM pgbench_accounts a
INNER JOIN pgbench_branches b ON a.bid=b.bid INNER JOIN pgbench_tellers t ON t.bid=b.bid WHERE a.bid=56;
QUERY PLAN
--- Nested Loop (cost=0.00..296417.62 rows=1013330 width=813) (actual time=26750.84
5..44221.807 rows=1000000 loops=1)
-> Seq Scan on pgbench_accounts a (cost=0.00..283731.12 rows=101333 width=97 ) (actual time=26749.187..31234.702 rows=100000 loops=1)
Filter: (bid = 56)
Rows Removed by Filter: 9900000
-> Materialize (cost=0.00..19.90 rows=10 width=716) (actual time=0.004..0.04 3 rows=10 loops=100000)
-> Nested Loop (cost=0.00..19.85 rows=10 width=716) (actual time=1.593..1.88 6 rows=10 loops=1)
-> Seq Scan on pgbench_branches b (cost=0.00..2.25 rows=1 width=364) (actual time=0.156..0.167 rows=1 loops=
Filter: (bid = 56) Rows Removed by Filter: 99
-> Seq Scan on pgbench_tellers t (cost=0.00..17.50 rows=10 width=352) (actual time=1.404..1.617 rows=10 loops=1)
Filter: (bid = 56)
Rows Removed by Filter: 990 Total runtime: 48022.546 ms
Agora podemos ver informações de tempo. No primeiro nó temos o tempo de execução, aproximadamente 44s, e o número de registros real: 1 milhão. O atributo loops indica o número de vezes em que a operação foi executada. Em alguns nós, como alguns joins, será maior que 1 e o número de registros e o tempo são mostrados por iteração, devendo-se multiplicar tais valores pela quantidade de loops para chegar ao valor total.
Importante: o comando EXPLAIN ANALYZE executa de fato a query. Logo, se for feito com um UPDATE ou DELETE, tal ação será realizada de fato, alterando a base da dados. Para somente analisar queries que alteram dados, você pode fazer o seguinte:
BEGIN TRANSACTION;
EXPLAIN ANALYZE UPDATE … ROLLBACK;
Outro parâmetro útil do EXPLAIN é o BUFFERS, que mostra a quantidade de blocos, 8kB por padrão, encontrados no shared buffers ou que foram lidos do disco ou, ainda, de arquivos temporários que foram necessários ser gravados em disco.
Ca pí tu lo 7 - D es em pe nh o – T óp ic os s ob re a pl ic aç ão
bench=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pgbench_accounts a
...
QUERY PLAN
--- Nested Loop (cost=0.00..296417.62 rows=1013330 width=813) (actual time=29946.100.. 48764.583 rows=1000000 loops=1)
Buffers: shared hit=519 read=158218
-> Seq Scan on pgbench_accounts a (cost=0.00..283731.12 rows=101333 width=97) (a ctual time=29945.373..34818.143 rows=100000 loops=1)
Filter: (bid = 56)
Rows Removed by Filter: 9900000 Buffers: shared hit=513 read=158218
-> Materialize (cost=0.00..19.90 rows=10 width=716) (actual time=0.004..0.046 r ows=10 loops=100000)
Buffers: shared hit=6
Agora a saída mostra os dados shared hit e read. No nó superior, que mostra o total, vemos que foram encontrados no cache do PostgreSQL, shared hit, 519 blocos (~4MB) e lidos do disco, read, 158218 blocos (~1,2GB).
Índices
Nos exemplos com o EXPLAIN, vimos nos planos de execução várias operações de SEQ SCAN. Essa operação varre a tabela toda e é executada quando não há um índice que atenda a consulta, ou porque o Otimizador acredita que terá de ler quase toda a tabela de qualquer jeito, sendo um overhead desnecessário tentar usar um índice. Especial atenção deve ser dada a situações em que não há índice útil, mas poderia haver um.
Índices são estruturas de dados paralelas às tabelas que têm a função de tornar o acesso aos dados mais rápido. Em um acesso a dados sem índices, é necessário percorrer todo um conjunto de dados para verificar se uma condição é satisfeita. Índices são estruturados de forma a serem necessárias menos comparações para localizar um dado ou determinar que ele não existe. Existem vários tipos de índices, sendo o mais comum o BTree, baseado em uma estrutura em árvore. No PostgreSQL é o tipo padrão, e se não informado no comando
CREATE INDEX, ele será assumido.
É comum haver confusão entre índices e restrições, ou constraints. Muitos desenvolve- dores assumem que são a mesma coisa, criam suas constraints e não se preocupam mais com o assunto.
Índices e constraints são coisas distintas. Índices, como foi dito, são estruturas de dados, ocupam espaço em disco e têm função de melhoria de desempenho. Constraints são regras, restrições impostas aos dados. A confusão nasce porque as constraints do tipo PRIMARY KEY e UNIQUE de fato criam índices implicitamente para garantir as propriedades das cons- traints. O problema reside com as FOREIGN KEY.
Ad m in is tr aç ão d e B an co d e D ad os
Para uma FK, o PostgreSQL não cria índices automaticamente. A necessidade de um índice nesses casos deve ser analisada e este criado manualmente. Normalmente chaves estran- geiras são colunas boas candidatas a terem índice.
1 2 4 7 9 17 19 26 29 32 3 6 12 16 24 28 10 20 1 2 7 4 19 6 9 12 Tabela 10
Índices simples
No exemplo de plano de execução recém-mostrado, vemos um SEQ SCAN com um filter. Esse é o candidato perfeito para criarmos um índice. Isso significa que o banco teve de varrer a tabela e aplicar uma condição (bid = 56) para remover os registros que não atendem esse critério.
-> Seq Scan on pgbench_accounts a (cost=0.00..283731.12 rows=101333 width=97) (a ctual time=29945.373..34818.143 rows=100000 loops=1)
Filter: (bid = 56)
Rows Removed by Filter: 9900000
Devemos então criar um índice na coluna bid e testar novamente. bench=# CREATE INDEX idx_accounts_bid ON pgbench_accounts(bid);
Índices criam locks nas tabelas que podem bloquear escritas. Se for necessário criá-lo em horário de uso do sistema, pode-se usar CREATE INDEX CONCURRENTLY, que usará um mecanismo menos agressivo de locks, porém demorará mais para executar.
Figura 7.8
Ca pí tu lo 7 - D es em pe nh o – T óp ic os s ob re a pl ic aç ão
Agora, executando a query novamente com EXPLAIN (ANAYZE), vemos o seguinte plano: QUERY PLAN
--- Nested Loop (cost=0.00..16964.96 rows=1013330 width=813) (actual time=44.298.. 13742.038 rows=1000000 loops=1)
-> Index Scan using idx_accounts_bid on pgbench_accounts a (cost=0.00..4278. 46 rows=101333 width=97) (actual time=43.903..1002.919 rows=100000)
Index Cond: (bid = 56)
-> Materialize (cost=0.00..19.90 rows=10 width=716) (actual time=0.004..0.04 2 rows=10 loops=100000)
-> Nested Loop (cost=0.00..19.85 rows=10 width=716) (actual time=0.321..0.57 9 rows=10 loops=1)
-> Seq Scan on pgbench_branches b (cost=0.00..2.25 rows=1 width=364) (actual time=0.117..0.128 rows=1 loops=1)
Filter: (bid = 56) Rows Removed by Filter: 99
-> Seq Scan on pgbench_tellers t (cost=0.00..17.50 rows=10 width=352) (actual time=0.147..0.326 rows=10 loops=1)
Filter: (bid = 56)
Rows Removed by Filter: 990 Total runtime: 17445.034 ms
O custo do nó caiu de 28 mil para cerca de 4 mil. O tempo para o nó que era de quase 35s caiu para 10s e o tempo total da query de 44s para 13s.