On Wed, Aug 21, 2013 at 7:45 PM, Danilo Silva <[email protected]>wrote:
> Pessoal, > > Dado o select+explain abaixo, quero ajuda de vocês para saber o que deve > ser levado em consideração no explain para uma possível melhora no select. > > Em relação ao explain, o que pode ser melhorado na consulta para uma > performance melhor? > > CREATE OR REPLACE VIEW vconsulta_datalog AS > SELECT coordenada, latitude, longitude, horarioi -(horarioutc-horario) AS > horacerta, > latitudeq, longitudeq, mediatype, > cmc.categoria_mensagem_cliente,d.id_modulo > FROM datalog d > JOIN mensagens_cliente mc ON mc.id_mensagem = d.id_mensagem > JOIN categoria_mensagem_cliente cmc ON cmc.id_categoria_mensagem_cliente = > mc.id_categoria_mensagem_cliente > WHERE (latitude IS NOT NULL) AND (longitude IS NOT NULL) AND (horarioutc > IS NOT NULL) AND (horario IS NOT NULL) AND (latitudeq IS NOT NULL) AND > (longitudeq IS NOT NULL); > > EXPLAIN ANALYSE > SELECT coordenada, latitude, longitude, horacerta, latitudeq, longitudeq, > mediatype, categoria_mensagem_cliente > FROM vconsulta_datalog > WHERE (id_modulo = '6') > AND (horacerta BETWEEN '2012-06-16 00:00:00' AND '2012-06-16 23:00:00') > > > "Nested Loop (cost=354061.26..1273339.98 rows=124 width=51) (actual > time=64712.690..130988.989 rows=494 loops=1)" > " Join Filter: (mc.id_categoria_mensagem_cliente = > cmc.id_categoria_mensagem_cliente)" > " Rows Removed by Join Filter: 2964" > " -> Hash Join (cost=354061.26..1273325.26 rows=124 width=48) (actual > time=64712.642..130977.716 rows=494 loops=1)" > " Hash Cond: ((mc.id_mensagem)::numeric = d.id_mensagem)" > " -> Seq Scan on mensagens_cliente mc (cost=0.00..785230.84 > rows=26806384 width=8) (actual time=6.001..35041.135 rows=26806383 loops=1)" > " -> Hash (cost=354054.08..354054.08 rows=574 width=50) (actual > time=62118.059..62118.059 rows=494 loops=1)" > " Buckets: 1024 Batches: 1 Memory Usage: 42kB" > " -> Bitmap Heap Scan on datalog d (cost=7440.97..354054.08 > rows=574 width=50) (actual time=3086.930..62116.382 rows=494 loops=1)" > " Recheck Cond: ((id_modulo = 6::numeric) AND (latitude > IS NOT NULL) AND (longitude IS NOT NULL) AND (horarioutc IS NOT NULL) AND > (horario IS NOT NULL) AND (latitudeq IS NOT NULL) AND (longitudeq IS NOT > NULL))" > " Filter: (((horarioi - (horarioutc - horario)) >= > '2012-06-16 00:00:00'::timestamp without time zone) AND ((horarioi - > (horarioutc - horario)) <= '2012-06-16 23:00:00'::timestamp without time > zone))" > " Rows Removed by Filter: 246473" > " -> Bitmap Index Scan on datalog_index_fields_idx > (cost=0.00..7440.82 rows=114736 width=0) (actual time=299.919..299.919 > rows=246967 loops=1)" > " Index Cond: ((id_modulo = 6::numeric) AND > (latitude IS NOT NULL) AND (longitude IS NOT NULL) AND (horarioutc IS NOT > NULL) AND (horario IS NOT NULL) AND (latitudeq IS NOT NULL) AND (longitudeq > IS NOT NULL))" > " -> Materialize (cost=0.00..1.11 rows=7 width=11) (actual > time=0.001..0.008 rows=7 loops=494)" > " -> Seq Scan on categoria_mensagem_cliente cmc (cost=0.00..1.07 > rows=7 width=11) (actual time=0.004..0.012 rows=7 loops=1)" > "Total runtime: 130991.233 ms" > > O resultado do explain é dado em vários nós conectados hierarquicamente, por exemplo, no seu caso os nós (de forma superficial) seriam: (1) Nested Loop (2) -> Hash Join (3) -> Seq Scan on mensagens_cliente mc (4) -> Hash (5) -> Bitmap Heap Scan on datalog d (6) -> Bitmap Index Scan on datalog_index_fields_idx (7) -> Materialize (8) -> Seq Scan on categoria_mensagem_cliente cmc (OBS: Talvez o parágrafo abaixo ficou muito chato de ler, qualquer coisa passe reto nele e veja os demais, sorry...) Resumindo essa árvore, temos que o PostgreSQL vai fazer um hash join (2) para realizar a junção entre as tabelas mensagens_cliente e datalog, usando um seq scan na tabela mensagens_cliente (3) e, para cada tupla encontrada nessa, verificando na tabela hash gerada pela navegação em (5) e (6). Após o join entre essas, o PG decide executar o join desse resultado com a tabela categoria_mensagem_cliente usando o algoritmo nested loop, para isso ele navega no resultado do hash join anteriormente gerado em (2) e, para cada linha, verifica se o valor da mesma existe na materialização (7) dos dados da tabela categoria_mensagem_cliente, sendo esta feita navegando a tabela toda com um seq scan (8). Não sei se consegui explicar essa parte, mas talvez não seja a mais "urgente" para o seu caso (continue lendo, vamos chegar lá)... Bom, outro ponto importante são os dados entre parênteses em cada nó que mostra primeiro como as estatísticas do PostgreSQL "deduziram" que seria o resultado, e segundo, como realmente foi feito (esse só é mostrado se usado o EXPLAIN com ANALYZE). Esse é muito importante, pois mostra quanto tempo o PostgreSQL "perdeu" processando cada nó, e, ainda, a comparação entre os dois lados mostra quão certeiras foram as estatísticas do PostgreSQL. Agora vem o ponto de ouro, se analisarmos sua árvore, vemos que o PostgreSQL perdeu, realmente, muito tempo nos nós (5) e (6). Uma possível solução para esse caso é adicionar a expressão (horarioi -(horarioutc-horario)) ao índice datalog_index_fields_idx (teria que recriar o índice). Existem outras otimizações possíveis, o ideal seria ir fazendo passo-a-passo, se puder testar esse índice e postar novamente os resultados ajudaria-nos (OBS: dependendo do ambiente seria bom fazer isso num ambiente de testes para não onerar a produção). Por fim, vem o "como ler o EXPLAIN de forma fácil"... E a solução é usando a ferramenta explain.depesz.com [1]. Veja, por exemplo, o seu resultado processado pela ferramenta [2] e veja a ajuda [3] para aprender a ler as informações lá presentes, se ainda tiver dúvida, poste aqui. Acho que falei demais, deu pra entender ou acabei complicando mais? [1] http://explain.depesz.com/ [2] http://explain.depesz.com/s/tVvY [3] http://explain.depesz.com/help Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
