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" FROM ( SELECT "id" , (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?