I'm writing a function that returns routes using PgRouting (v.2.6) The main problem I'm having is that I cant get the time less than 1 second per query.
The main query I need is get time, length, and the line, so I don't need all the returning rows but a sum() of them.
I have this query:
SELECT sum(rutanodos.cost*60) as sum_cost_minutes, sum(routing.calles_2po_4pgr.km) as sum_km, round((st_length(st_union(st_transform(geom_way, 4258)))*100)::numeric, 3) as distancia_linea_km, st_asEWKT(st_union(geom_way)) as geom_json FROM pgr_dijkstra( 'SELECT id as id, source source, target target, cost, reverse_cost FROM routing.es_2po_4pgr WHERE geom_way && ST_Expand( (SELECT ST_Collect(the_geom) FROM routing.es_2po_4pgr_vertices_pgr WHERE id IN (38785, 744540)),0.01)', 38785, 744540, false, false) as rutanodos join routing.es_2po_4pgr on routing.es_2po_4pgr.id = id2
- I obtain the route with pg_dijkstra() from a node to a node.
- I join the nodes with the line.
- Finally I group the list to get: SUM of costs, SUM of length, union of the geometry in EWKT.
And returns (in 1.1 second):
- sum of cost minutes:345.276354
- sum of Km: 643.367627099999
- line length: 729.228 (to see the difference with returned km)
- EWKT line geom: "SRID=4326;MULTILINESTRING((-1.7734135 42.2439494,-1.7732242 42.2408917),(-1.773393 42.2445657,-..."
- "Aggregate (cost=8224.76..8226.67 rows=1 width=80)" - " -> Nested Loop (cost=0.68..8209.75 rows=1000 width=130)" - " -> Function Scan on pgr_dijkstra rutanodos (cost=0.25..10.25 rows=1000 width=12)" - " -> Index Scan using calles_2po_4pgr_pk on calles_2po_4pgr (cost=0.43..8.20 rows=1 width=126)" - " Index Cond: (id = rutanodos.id2)"
This works properly and with I get all the results that I need, but, as I said, it takes more than a second.
I've read some advices to optimize, but still can't get better time. For example.
- I've created indexes in source, target, geom and a primary key.
- Vacuum and cluster on index.
- Created bounding box for passing only a part of the network instead of all the nodes.
- My data comes from osm2po, with topology created.
I've read a lot of posts about, and I've seen that people can get times of 50ms, but the fastest time I can get is about a second.
Is there anything that I'm missing?
Anyone knows any other tip or recommendation to get better times?