I've just observed something quite odd.

I have created a table with data for routing, and this table contains data for a graph over six different hour conditions. So when performing shortest_path, I select only the segments that corresponds to the "hour" column that I currently want.

I have created an index on the hour column but not on the source/target columns, and afterwards performed an analyze.

But when querying for shortest_path, I was NOT getting the shortest path, but a path where the route was lead through edges with extremely high weights (1,000,000 (created to tell not to go this way) where other weights are in the range 1-100).

After a lot of debugging (and blaming both my own graphs, pgrouting and everything else), I tried to perform a "Vacuum Analyze" on the table, and suddenly the results are correct.

So it seems like PostgreSQL had some problems that lead to pgRouting determining the wrong shortest path.

Now I ask you, if you have seen something like this before, and how to make sure this does not happen? The creation task of the graph table is automatized and I can see that analyze was actually performed after creation of the table.

  • That should not be possible. What is possible is that your cost UPDATE was still running or data was not committed when you tested shortest_path and it used "old" data. – simplexio Feb 1 '13 at 8:10
  • How did you create your routing table? If you used @assign_vertex_id@ make sure that the unit of your network (degree/meter) suits to the snapping tolerance of the function. – dkastl Feb 1 '13 at 10:29
  • @simplexio: There should be no problem with "old" data - I first observered the problem yesterday, and restarted the DBMS this morning, and the problem still existed. – xcalibur666 Feb 1 '13 at 11:46
  • @dkastl: I created the table using a "Create table as " script, that just creates a table with the following columns: (id, source, target, cost, reverse_cost, hour). When querying I just use the simple shortest_path from pgrouting.org/docs/foss4g2008/ch07.html and sets directed and has_reversed_cost to True. – xcalibur666 Feb 1 '13 at 11:49

I found the problem. I have some edges that are going between the same vertices, hence I run into this problem, that a random edge will be chosen, and not the "shortest" edge.

Thank you though for the time you have taken.

Why doesn't pgRouting return the best path?

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.