I have table network which has about 2 million records (only 3 countries in Europe). When I run Shorthest-path it is very slow, it takes 3 min to take records only. I tried with dijkstra-sp-delta-directed but it is also slow, about 2-2.5 min. This sp is using bounding box and gist index so it should be faster but it is not. In the future I should add some other country so table will be very large.
What could I do to make this routing faster? As far as I know Postgre doesn't have option to save temp table in RAM, it works only in one session.
I have 4 GB RAM, I work with OSM and Postgre because it is free, I realy don't know is there any solution for this problem? I am not in position to buy some expensive solution, I have demand to work with open sources. Of course everyone expects quick and cheap solution that works like Google :)
Update:
For the fastest route (avoid toll , avoid highway) my query is:
SELECT * FROM shortest_path(
'SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
time + cost::float8 AS cost,
time + reverse_cost::float8 AS reverse_cost
from network where toll is null and road_class!=1 ',
257027, 276521, true, true)
For the shothest route (avoid toll , avoid highway) my query is:
SELECT * FROM shortest_path(
'SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
length + cost::float8 AS cost,
length + reverse_cost::float8 AS reverse_cost
from network where toll is null and road_class!=1 ',
257027, 276521, true, true)
