Re: Optimizar query

Muestro el EXPLAIN ANALYZE, desde ya muchas gracias.

EXPLAIN ANALYZE
SELECT the_geom, nombre FROM
shortest_path('SELECT id, source, target, cost FROM calles_tmp_edges where partido=45', 6001, 9960, false, false) a
inner join calles_tmp b
inner join calles_nombre n
on b.idcalle=n.idcalle
on a.edge_id=b.edge_id
where partido=45

"Merge Join (cost=2061.75..2721.72 rows=43665 width=77) (actual time=1950.551..1991.743 rows=49 loops=1)"
" Merge Cond: ("outer".edge_id = "inner".edge_id)"
" -> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=473.271..473.360 rows=50 loops=1)"
" Sort Key: a.edge_id"
" -> Function Scan on shortest_path a (cost=0.00..12.50 rows=1000 width=4) (actual time=472.492..472.652 rows=50 loops=1)"
" -> Sort (cost=1999.42..2021.25 rows=8733 width=81) (actual time=1420.252..1480.678 rows=14692 loops=1)"
" Sort Key: b.edge_id"
" -> Hash Join (cost=63.63..1427.74 rows=8733 width=81) (actual time=145.128..733.827 rows=23287 loops=1)"
" Hash Cond: ("outer".idcalle = "inner".idcalle)"
" -> Seq Scan on calles_tmp b (cost=0.00..1223.19 rows=10719 width=72) (actual time=37.283..417.470 rows=29223 loops=1)"
" -> Hash (cost=61.30..61.30 rows=932 width=17) (actual time=17.250..17.250 rows=0 loops=1)"
" -> Seq Scan on calles_nombre n (cost=0.00..61.30 rows=932 width=17) (actual time=5.235..13.832 rows=932 loops=1)"
" Filter: (partido = 45)"
"Total runtime: 1998.597 ms"

EXPLAIN ANALYZE
SELECT the_geom, nombre FROM
shortest_path('SELECT id, source, target, cost FROM calles_tmp_edges where partido=45', 6001, 9960, false, false) a
inner join calles_tmp b
inner join calles_nombre n
on b.idcalle=n.idcalle
on a.edge_id=b.edge_id
where partido=45

"Nested Loop (cost=13.50..236376.67 rows=43665 width=77) (actual time=1944.100..7761.394 rows=49 loops=1)"
" Join Filter: ("inner".edge_id = "outer".edge_id)"
" -> Nested Loop (cost=0.00..39870.67 rows=8733 width=81) (actual time=405.498..2457.878 rows=23287 loops=1)"
" -> Index Scan using calles_tmp_id_calle_idq on calles_tmp b (cost=0.00..7039.83 rows=10719 width=72) (actual time=0.495..632.118 rows=29223 loops=1)"
" -> Index Scan using ix_idcalle on calles_nombre n (cost=0.00..3.05 rows=1 width=17) (actual time=0.037..0.044 rows=1 loops=29223)"
" Index Cond: ("outer".idcalle = n.idcalle)"
" Filter: (partido = 45)"
" -> Materialize (cost=13.50..23.50 rows=1000 width=4) (actual time=0.021..0.092 rows=50 loops=23287)"
" -> Function Scan on shortest_path a (cost=0.00..12.50 rows=1000 width=4) (actual time=441.421..441.586 rows=50 loops=1)"
"Total runtime: 7761.931 ms"

Miguel Juliÿffffe1n de la Fuente <jmdelafuente(at)yahoo(dot)com(dot)ar> escribió:
Hola lista, tengo instalado "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4" sobre debian, estoy tratando de ejecutar un consulta que sobre el resultado de una funcion (shortest_path devuelve aprox. 50 filas) debe unir otra tabla (calles_tmp de 30000 filas) a traves de un campo indizado en esta tabla y a su vez con otro campo de esta segunda tabla unir una tercera (calles_nombre de 1100 filas) que tambien dispone de indice por el campo buscado:

si yo ejecuto lo siguiente:

SELECT the_geom, nombre
FROM
shortest_path('SELECT id, source, target, cost FROM calles_tmp_edges where partido=45', 6001, 9960, false, false) a
inner join calles_tmp b
inner join calles_nombre n
on b.idcalle=n.idcalle
on a.edge_id=b.edge_id
where partido=45

el plan de ejecucion resulta

Merge Join (cost=2061.75..2721.72 rows=43665 width=77)
Merge Cond: ("outer".edge_id = "inner".edge_id)
-> Sort (cost=62.33..64.83 rows=1000 width=4)
Sort Key: a.edge_id
-> Function Scan on shortest_path a (cost=0.00..12.50 rows=1000 width=4)
-> Sort (cost=1999.42..2021.25 rows=8733 width=81)
Sort Key: b.edge_id
-> Hash Join (cost=63.63..1427.74 rows=8733 width=81)
Hash Cond: ("outer".idcalle = "inner".idcalle)
-> Seq Scan on calles_tmp b (cost=0.00..1223.19 rows=10719 width=72)
-> Hash (cost=61.30..61.30 rows=932 width=17)
-> Seq Scan on calles_nombre n (cost=0.00..61.30 rows=932 width=17)
Filter: (partido = 45)

que demora mas de 2 segundos y no parece ser el camino optimo.

Por esta razon tratamos de optimizarlos forzando a un nested loop de la siguiente manera:

set enable_seqscan=off; set enable_mergejoin=off; set enable_hashjoin=off; set enable_nestloop=on;

esperando que buscara a partir del resultado de la funcion las coincidencias en calles_tmp y luego a partir de este nuevo resultado las coincidencias en calles_nombre.

Pero, no. Utiliza los nested loops, pero no en el orden correcto. Primero une calles_tmp con calles_nombres y luego une el resultado de la funcion, tardando 8 segundos.
Copio el plan de ejecucion:

Nested Loop (cost=13.50..236376.67 rows=43665 width=77)
Join Filter: ("inner".edge_id = "outer".edge_id)
-> Nested Loop (cost=0.00..39870.67 rows=8733 width=81)
-> Index Scan using calles_tmp_id_calle_idq on calles_tmp b (cost=0.00..7039.83 rows=10719 width=72)
-> Index Scan using ix_idcalle on calles_nombre n (cost=0.00..3.05 rows=1 width=17)
Index Cond: ("outer".idcalle = n.idcalle)
Filter: (partido = 45)
-> Materialize (cost=13.50..23.50 rows=1000 width=4)
-> Function Scan on shortest_path a (cost=0.00..12.50 rows=1000 width=4)

Finalmente, para forzar el orden de ejecucion de las consultas, no se nos ocurrio mas que dividir las consultas guardando el resultado del primer join en una tabla temporal y luego ejecutando el segundo join.

select the_geom, idcalle
into temporary pp
from shortest_path('SELECT id, source, target, cost FROM calles_tmp_edges where partido=45', 6001, 9960, false, false) a
inner join calles_tmp b on a.edge_id=b.edge_id;

SELECT the_geom, nombre FROM pp b
inner join calles_nombre n
on b.idcalle=n.idcalle
where partido=45;

Asi, el tiempo se redujo a 640 ms.

Mis dudas son:
-- no hay alguna forma mas "elegante" de resolver el problema??
-- No le puedo indicar el orden en que debe realizar los join de alguna otra manera??
-- Y, aunque no sea mi caso, no le puedo indicar la forma de union individualmente (lo que en otro DBMS seria "inner loop join") ??

Espero sus respuestas. Muchas gracias.

---------------------------------
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
Probalo ya!

---------------------------------
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
Probalo ya!