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   

In which:

  • 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,-..."

After explain command:

- "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?

  • can you add an explain for the query to the question – Ian Turton May 24 '18 at 9:12
  • Query explained as @IanTurton suggested. – Pablo Pardo May 24 '18 at 10:13
  • I meant what is the result of running explain select sum(...... – Ian Turton May 24 '18 at 10:14
  • Ok sorry, edited again :) – Pablo Pardo May 24 '18 at 10:21
  • no, it is a special SQL command that will show us how the database engine processes the query, add explain to the start of your query and run it again. You should get result like "Seq Scan on roadnode (cost=0.00..4609.81 rows=119681 width=222)" but longer – Ian Turton May 24 '18 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.