Re: Optimizar query

On Mon, 24 Jul 2006 13:39:56 -0300 (ART), Miguel Juliÿffffe1n de la Fuente <jmdelafuente(at)yahoo(dot)com(dot)ar> wrote:
> 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"

Haz un VACUUM ANALYZE sobre la tabla calles_tmp y prueba de nuevo. Ahi el planner cree que hay la mitad de las filas que realmente existen.

--
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------