shortest_path is not working on large source/target

Question

I tried to find a route using shortest_path function of pgrouting, and it took to the server (postgres) a lot time to process my query (more then 5 minutes!) even I have only 10 rows in my table.

After playing with my table I found out that the IDs of source & target must be short (up to 6-7 digits) in order to get the query results fast. But these IDs I get them from the map so I don't want to change them.

For Example, this is the table:

id   source     target   cost
--   -------    -------  -----
1    123456     2        1
2    3          2        2
3    3          4        3
4    4          5        4
5    5          6        3.3

This query takes ~30ms:

SELECT * FROM shortest_path('SELECT * FROM temp_table_2',123456, 3, false, false);

But the same query when I change the source 123456 to 1234567 I get the result after ~400ms! (and of course, If I try to increase the source to 12345678 it will take more than few seconds...)

How can I use pgrouting to get the shortest path (and not just wait...) from my table?

Thanks!

Answer 1

The shortest path functions of pgRouting require that source and target ID have the type INTEGER. (see http://www.pgrouting.org/docs/1.x/dijkstra.html)

Also it's recommended to renumber your source and target ID, that they start from 1. You can keep your original ID as a reference stored in a different attribute, but for the algorithm it's bad to have high numbers and "big wholes".

Furthermore check that you have indices on source and target column and that you run vacuum.