[pgrouting-users] Speed when using big maps

[Stephen Woodbridge]

On 10/24/2013 6:16 AM, Philipp Hamm wrote:
> Hi everyone,
>
> I’ve just started testing pgRouting on a big network (Germany) and want
> to use the whole Europe map later, but it’s pretty slow.
> I takes around 2,5 min to calculate one route and it makes no difference
> if the route distance is 7km or 700km.
>
> Is there anything I can do to speed it up?
> I have already indices to ‘source’ and ‘target’ and id is primary key,
> but I don’t know how to use bounding boxes.

You need to create a spatial index:

create index myroads_gidx on myroads using gist (the_geom);

> Furthermore I’ve noticed: when selecting only a part of the graph
> (keeping in mind that the area of the dijkstra expansion is loaded) it
> gets much faster.
> Is this similar to what the bounding box would do?

Then only load the part of the roads needed to solve the graph. The more 
roads you load the long it takes to load the data and to solve the 
problem. Also astar and trsp are both faster then Dijkstra.

> To understand this I have some questions about how pgRouting works:
> Does pgRouting reloads the road data on every request and does the slow
> calculation results only from fetching the graph?
> If so, is it possible to load the graph into the memory and reuse it for
> further requests?

No, not currently because there is no place to load the graph into 
memory in the database that is persistent.

> As I want to execute a lot of kDijkstras consecutively I guess only
> bounding boxes won’t make it fast enough.

If you need many routes from a single start point, then you should look 
at pgr_kdijkstra().

If your routes are long and you want to use highways, then you can load 
data like a barbell using three bbox queries and union the results 
together. So, query 1, gets all roads for some radius around the start 
point, query2, gets all roads for some radius around the end point, and 
query 3, gets all the highways only in the bounding box between the 
start and end points.

-Steve

> Thanks in advance!
>
>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>