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" []s Danilo
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
