Forum #23 - Topic #24 - Message List

I have made some testing on bulk data. Table "routing_roads" contains 328997 rows.

My software environment : WinXP, Postgresql 8.2.3,Postgis 1.2.1,pgRouting 1.0.0a

My hardware environment : Athlon 3 GHz, 2G ram

I have made 3 queries for searching shortest path using 3 algorithms:

1)Dijkstra algorithm

SELECT * FROM shortest_path('SELECT id,source,

target, cost,0 as reverse_cost FROM routing_roads order by 1,2,3',111465,112777,true,false);

Result : 6 rows fetched (4,14 sec)

vertex_id edge_id cost

111 465 1 3

112 766 3 3

112 761 5 3

111 685 7 3

112 773 9 3

112 777 -1 0

2)A* algorithm

SELECT * FROM shortest_path_astar('SELECT id,source,

target, cost,0 as reverse_cost, x1,y1,x2,y2 FROM routing_roads order by 1,2,3',111465,112777,true,false);

Result : 6 rows fetched (7,23 sec).

The output is the same as in Dijkstra algorithm

3)Shooting* algorithm (there are no restrictions)

SELECT * FROM shortest_path_shooting_star('SELECT id,source,

target, cost,0 as reverse_cost, x1,y1,x2,y2,null as rule,null as to_cost FROM routing_roads order by 1,2,3',1,9,true,false);

Result : 5 rows fetched (17,50 sec)

vertex_id edge_id cost

0 1 3

1 3 3

5 5 3

8 7 3

11 9 3

Conclusions: shortest path edges in results where found correctly. That's great.

But the time processing results (for example for Shooting* )for finding the directly connected edges is the same as for the edge which far from each other:

SELECT * FROM shortest_path_shooting_star('SELECT id,source,

target, cost,0 as reverse_cost, x1,y1,x2,y2,null as rule,null as to_cost FROM routing_roads order by 1,2,3',1,3,true,false);

1 rows fetched (18,02 sec)

vertex_id edge_id cost

0 1 3

1 3 3

Adding indexes to table doesn't improve the speed. I'm not c++ guru, but it seems that almost all time is spent for loading data from table into the memory (during processing postgres process takes up to 320Mb). After the processing of query all in memory data ( from "routing_roads" table) is removed and the next query has to wait for getting all data in memory again.

The question:

1)is it possible to make some sort of caching data in memory , so as to use it for next query?

2)Can all in memory data be shared across postgre processes ( for application using connection pools)?

3)Can be added some sort of api for manually removing cached in memory data ( in case of routing table updates)