Entendendo o explain do Postgres
Frequentemente escrevemos uma consulta ao banco que demora mais do que esperávamos e que precisa ser otimizada. Porém, para otimizar corretamente, precisamos entender: por que a consulta está lenta? Qual ou quais gargalos ela tem? É aí que entra o explain.
O explain é uma ferramenta disponível em diversos bancos de dados que nos permite entender qual caminho o banco vai seguir para executar uma consulta que passamos a ele. No Postgres ele tem essa cara:
testes=# explain select * from clientes where cpf = '547.838.333-38'; QUERY PLAN ----------------------------------------------------------- Seq Scan on clientes (cost=0.00..208.23 rows=1 width=54) Filter: ((cpf)::text = '547.838.333-38'::text) (2 rows)
Na saída acima, podemos ver que, para executar a consulta select * from clientes where cpf = '547.838.333-38', o Postgres passará por todos os registros da tabela, mostrando apenas os que baterem com a condição que passamos.
Numa tabela com poucos registros, passar por todos eles vai ser rápido; a consulta acima devolve instantaneamente o resultado para uma tabela com 9000 registros. Porém, numa tabela com milhões de registros, um select simples como o acima pode demorar vários segundos para executar.
Uma estratégia bastante comum para melhorar o desempenho de consultas mais frequentes é indexar valores de uma ou mais colunas usadas nessas consultas. No exemplo acima, poderíamos ter um índice na coluna cpf. Quando indexamos os valores de uma coluna, o banco mantém uma estrutura com os valores dessa coluna ordenados, de forma que fica muito mais rápido buscar valores específicos no banco. O explain nos mostra isso também:
testes=# create index idx_cliente_cpf on clientes (cpf); CREATE INDEX testes=# explain select * from clientes where cpf = '547.838.333-38'; QUERY PLAN -------------------------------------------------------------------------------------- Index Scan using idx_cliente_cpf on clientes (cost=0.29..8.30 rows=1 width=54) Index Cond: ((cpf)::text = '547.838.333-38'::text) (2 rows)
Repare como, agora, em vez de mostrar Seq Scan, ele mostra Index Scan, ou seja, ele está usando o índice que acabamos de criar. E repare, também, que os valores do cost (entre parênteses) mudaram: o primeiro número aumentou ligeiramente e o segundo caiu bastante! Esses valores são uma informação bem importante do explain: eles mostram uma estimativa do tempo que será gasto na execução dessa consulta. O primeiro valor é o tempo até o primeiro resultado e, o segundo, o tempo até completar a consulta. Ambos são valores importantes: no processamento de uma lista de valores, quanto mais rápido recebermos o primeiro valor, mais cedo podemos começar a processar. Além disso, quanto mais rápido a consulta terminar, mais cedo o banco de dados ficará livre para processar outra consulta.
Vale ressaltar que os valores que aparecem são estimativas. Isso porque, para determiná-los, o Postgres não executa a consulta que passamos para ele, apenas usa de algumas estatísticas que ele guarda sobre nossas informações. Se quisermos saber quanto tempo de verdade ele vai levar para executar uma consulta, podemos usar o explain analyze:
testes=# explain analyze select * from clientes where cpf = '547.838.333-38'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_cliente_cpf on clientes (cost=0.29..8.30 rows=1 width=54) (actual time=0.068..0.069 rows=1 loops=1) Index Cond: ((cpf)::text = '547.838.333-38'::text) Total runtime: 0.091 ms (3 rows)
Veja como, agora, temos algumas informações a mais: actual runtime e total runtime. Esses são os tempos reais que o Postgres gastou na nossa consulta.
Uma consulta mais complexa
Até agora, vimos o explain de uma consulta simples. Porém, consultas que precisam ser otimizadas são, geralmente, muito mais complexas que um simples where, envolvendo joins, subconsultas e condições mais complexas. Vamos ver um caso mais complexo:
testes=# explain select count(c.*) from clientes c where exists (select 1 from pedidos p where p.id_cliente = c.id); QUERY PLAN ----------------------------------------------------------------------------- Aggregate (cost=227.43..227.44 rows=1 width=78) -> Hash Semi Join (cost=12.14..226.43 rows=400 width=78) Hash Cond: (c.id = p.id_cliente) -> Seq Scan on clientes c (cost=0.00..185.78 rows=8978 width=86) -> Hash (cost=7.06..7.06 rows=406 width=8) -> Seq Scan on pedidos p (cost=0.00..7.06 rows=406 width=8) (6 rows)
Aqui estamos fazendo uma consulta para trazer a quantidade de clientes que têm pedido registrado no sistema. Vamos entender o que o Postgres vai fazer nesse caso. Para isso, o primeiro ponto que precisamos entender é que, no explain, as linhas menos indentadas dependem das linhas mais indentadas para executar. Então, no exemplo, para o Hash Semi Join executar, ele precisou executar primeiro o Seq Scan em clientes e o Hash; este último, por sua vez, foi feito com base num Seq Scan em pedidos. Com o Hash Semi Join pronto, ele enfim conseguiu executar o Aggregate, que é o count(*) da consulta.
Será que podemos melhorar o desempenho dessa consulta criando um índice? Afinal, o Seq Scan significa que Postgres está percorrendo as duas tabelas inteiras para fazer o exists! Vamos tentar:
testes=# create index idx_cliente_pedido on pedidos (id_cliente); CREATE INDEX testes=# explain select count(c.*) from clientes c where exists (select 1 from pedidos p where p.id_cliente = c.id); QUERY PLAN ----------------------------------------------------------------------------- Aggregate (cost=227.43..227.44 rows=1 width=78) -> Hash Semi Join (cost=12.14..226.43 rows=400 width=78) Hash Cond: (c.id = p.id_cliente) -> Seq Scan on clientes c (cost=0.00..185.78 rows=8978 width=86) -> Hash (cost=7.06..7.06 rows=406 width=8) -> Seq Scan on pedidos p (cost=0.00..7.06 rows=406 width=8) (6 rows)
Mesma coisa. Ou seja, o índice, nesse caso, mais atrapalha do que ajuda! Isso porque, além de não ser usado na consulta, o índice não sai de graça para o banco: ele tem um custo (em processamento e memória) de criação e manutenção.
Mas por que o índice não foi utilizado? Porque, com base em suas estatísticas, o Postgres calculou que seria mais caro carregá-lo e usá-lo do que simplesmente montar um hash (uma tabela de espalhamento) a partir de toda a tabela de pedidos.
Com base no que vimos até agora, podemos notar como o explain é uma ferramenta útil para entender o funcionamento do banco de dados, otimizar consultas e evitar otimizações desnecessárias. Num próximo post, veremos como o Postgres gera essas estatísticas e quais são algumas delas.













