pgRouting – Troubleshoot Slow pgr_* Routing Functions with OSM Data

osm2popgroutingpostgis-2.0

I loaded the German OSM dataset into the pgrouting DB by using osm2po 4.7.7. Everything works fine i have osm2po set up via it's config and it's working like a charm through it's Java part.

I had the *_2po_4pgr table imported without any problems. Even the *2po_v table gets imported, though I don't completely understand the relation of this table.

I executed the pgr_createTopology function which ran for quite a while (12000secs) while calculating all 6m edges. I thought this would do the deal, but still it's unbearably slow.

I would like to know if i forgot something. I was thinking of using pgRouting instead of the java library but at the moment its performance-wise just out of comparison.

Best Answer

Problem with pgRouting performance seems to be that new pgr_astar and pgr_dijkstra use whole graph (which guarantees solution if there is one). Simple solution to get better performance is limit used graph to smaller area. It has it own problems like sometimes it may create graphs that cannot be solved

 (SELECT ST_Expand(ST_Extent(geom_way),0.1) as box  FROM hh_2po_4pgr as l1 WHERE l1.source =7 OR l1.target = 12)

Creates BBOX over source and target collection and expands it 0.1 degrees, then same query is used to limit graph size in pgr_ query

Dijkstra from 1.2s to ~65ms 

SELECT  seq, id1 AS node, id2 AS edge, g.geom_way as the_geom
    FROM pgr_dijkstra(
            'SELECT id, source, target, cost FROM hh_2po_4pgr as r, 
            (SELECT ST_Expand(ST_Extent(geom_way),0.1) as box  FROM hh_2po_4pgr as l1    WHERE l1.source =7 OR l1.target = 12) as box
            WHERE r.geom_way && box.box',
            7, 12, false, false
    ) as r INNER JOIN hh_2po_4pgr as g ON r.id2 = g.id ;

A* from 2s to ~50ms 

SELECT seq, id1 AS node, id2 AS edge, cost
    FROM pgr_astar(
           'SELECT id, source, target, cost, x1,y1,x2,y2 FROM hh_2po_4pgr as r, 
             (SELECT ST_Expand(ST_Extent(geom_way),0.1) as box  FROM hh_2po_4pgr as l1    WHERE l1.source =7 OR l1.target = 12) as box
            WHERE r.geom_way && box.box',
            7, 12, false, false
    );

osm2po was used to import data (finland-latest) into postgis table. gist index added to geom_way column and full vacuum analyze run for database. shared memory 1G . workmem 512M

Related Question