Skip site navigation (1) Skip section navigation (2)

Re: Optimizar query

From: Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar>
To: Miguel Julin de la Fuente <jmdelafuente(at)yahoo(dot)com(dot)ar>
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Optimizar query
Date: 2006-07-24 20:49:38
Message-ID: 573a5a5fa244453dc24bdf2bd3b2cf0b@localhost (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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
---------------------------------------------------------



In response to

Responses

pgsql-es-ayuda by date

Next:From: Alvaro HerreraDate: 2006-07-24 21:15:53
Subject: Re: Optimizar query
Previous:From: Alvaro HerreraDate: 2006-07-24 20:28:50
Subject: Re: rescatar exceptions de plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group