astar (#17) - The function shortest_path_shooting_star is slow (#216) - Message List

The function shortest_path_shooting_star is slow

Hi!!....

I'm using the function "shortest_path_shooting_star" and it returns the result perfectly but depending of the number of data the performance is slower. For example i created two tables.

Table 1 have this structure, the name of this table is "mtxlinks":

gid2 integer NOT NULL, source integer, target integer, "cost" double precision, reverse_cost double precision, to_cost double precision, x1 double precision, y1 double precision, x2 double precision, y2 double precision, "rule" text, id_cc1 integer, CONSTRAINT "PK_mtxlinks2" PRIMARY KEY (gid2)

Table 2 have the same structure, the name of this table is "mtxlinks2":

gid2 integer NOT NULL, source integer, target integer, "cost" double precision, reverse_cost double precision, to_cost double precision, x1 double precision, y1 double precision, x2 double precision, y2 double precision, "rule" text, id_cc1 integer, CONSTRAINT "PK_mtxlinks" PRIMARY KEY (gid2)

To know the number of rows:

select count(*) from mtxlinks ... this return: 507.770

select count(*) from mtxlinks2 .... this return: 77.463

When i run this SP:

SELECT "edge_id" FROM shortest_path_shooting_star(

'SELECT gid2 as id, source, target, cost, reverse_cost, x1, y1, x2, y2, rule, to_cost FROM mtxlinks2 order by gid2' ,113037234 ,113048625 , false , true )

in the table 1 "mtxlinks" is very very slow.... but in the table 2 "mtxlinks2" is very fast.

To analize this i execute EXPLAIN ANALYZE SELECT on the SP at the table "mtxlinks ":

EXPLAIN ANALYZE SELECT "edge_id" FROM shortest_path_shooting_star(

'SELECT gid2 as id, source, target, cost, reverse_cost, x1, y1, x2, y2, rule, to_cost FROM mtxlinks order by gid2' ,113037234 ,113048625 , false , true )

this return : "Function Scan on shortest_path_shooting_star (cost=0.00..12.50 rows=1000 width=4) (actual time=34172.209..34172.272 rows=45 loops=1)" and "Total runtime: 34172.372 ms".

EXPLAIN ANALYZE SELECT on the SP at the table "mtxlinks2":

EXPLAIN ANALYZE SELECT "edge_id" FROM shortest_path_shooting_star(

'SELECT gid2 as id, source, target, cost, reverse_cost, x1, y1, x2, y2, rule, to_cost FROM mtxlinks2 order by gid2' ,113037234 ,113048625 , false , true )

this return :

"Function Scan on shortest_path_shooting_star (cost=0.00..12.50 rows=1000 width=4) (actual time=4836.670..4836.733 rows=45 loops=1)" and "Total runtime: 4836.830 ms"

How can i fix that? How can i improve the speed of the function? is depends on columns? depend about the number of data? Becouse in the example www.ridethecity.com is very fast and i guess that they have more number of data ....

Help me please...

Thanks..