I would like to compute shortest path between 2,5 millions pairs.

I created table with id, origin, the nearest node of origin, destination, the nearest node of destination. I also created index on source, target, id. I have table with roads network of one city - also with indexes. All geoms have gist index.

When I tried to use dijkstra, the execute of query is too slowly. It would take over a week. I have idea, I will add where condition (f.e. id <10000) and I will make insert to new table. Therefore I can do a few queries in the same time. I noticed that using a larger index (few columns) speeds up the query.

Below my code:

SELECT a."id" as id
, SUM(a.cost) AS "Aggregated Cost" 
, (pgr_dijkstra( 'select id, source, target, shape_leng as cost 
from roads', "mz_node", "tz_node", false )).* 
FROM pair_with_near where id < 1000) AS a
JOIN roads AS b ON a.edge = b.id GROUP BY a."id" 

I don't have more ideas, how can I make it faster. I read about buffer creating but I can't do it. The same with THIS

Do you have any idea?

  • Your query will become faster if you select fewer data. For example you can limit by bounding box and cut away all areas, that you will not need. Any road network edges, that you know you won't need, exclude them in your query. – dkastl Oct 8 '19 at 1:40
  • Yes, I know but I don't know, where can I add bounding box in my query. Could you help me? – Michal De Oct 8 '19 at 16:42

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.