Why low speed function call pgr_dijkstra in database PostgreSQL after importing map from file osm.pbf using osm2po?

Question

I import map from file russia.osm.pbf using osm2po.

Why the low speed function call pgr_dijkstra in database Postgresql (>2 minute)?

My steps:

java -Xmx6g -jar osm2po-core-5.0.0-signed.jar prefix=pgr tileSize=45x45,1,c  RU.osm.pbf

set PSQL="c:\Program Files\PostgreSQL\9.4\bin\psql.exe"
set PGPORT=5432
set PGHOST=localhost
set PGPASSWORD=1
cd pgr
%PSQL% -U postgres -d pgrouter -q -f "pgr_2po_4pgr.sql"

The database created a table with an index on the field source and target:

CREATE TABLE pgr_2po_4pgr
(
id integer NOT NULL,
osm_id bigint,
osm_name character varying,
osm_meta character varying,
osm_source_id bigint,
osm_target_id bigint,
clazz integer,
flags integer,
source integer,
target integer,
km double precision,
kmh integer,
cost double precision,
reverse_cost double precision,
x1 double precision,
y1 double precision,
x2 double precision,
y2 double precision,
geom_way geometry(LineString,4326),
CONSTRAINT pkey_pgr_2po_4pgr PRIMARY KEY (id)
)

My query:

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM pgr_2po_4pgr', 
    30837, 682499,false, false
)

Query execution time is more than 2 minutes. Why?

This query is very fast (~1 second):

http://localhost:8888/Osm2poService?cmd=fr&source=30837&target=682499&findShortestPath=true&ignoreRestrictions=false&ignoreOneWays=false&routerClassId=0&heuristicFactor=0.0&maxCost=0.0&format=geojson

Answer 1

pgRouting queries are as faster as smaller your selected road network is. And it's as slower s larger your selected road network is. Your selection is the first argument of the pgr_dijkstra function:

SELECT id, source, target, cost FROM pgr_2po_4pgr;

So you see, that you select all records of your network table pgr_2po_4pgr, even if your start and end point are very close to each other. If that's the case, then you could clip a BBOX that contains your start and end point and it will improve the response time.

pgRouting does not store the routing graph in memory, so with every request the network data stored in a table is loaded and turned into a graph. This can be slow for large networks. osm2poService loads a pre-processed graph into memory and that's why it's faster for subsequent requests. But for every change in your road network (geometries or costs) you must process the data again.

Answer 2

SELECT  seq, id1 AS node, id2 AS edge, g.geom 
FROM pgr_dijkstra(
        'SELECT id, source, target, cost FROM pgr as r, 
        (SELECT ST_Expand(ST_Extent(geom),0.1) as box  FROM pgr as l1    WHERE l1.source =1 OR l1.target = 20) as box
        WHERE r.geom && box.box',
        1, 20, false, false
) as r INNER JOIN pgr as g ON r.id2 = g.id ;


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