I'm willing to optimise my queries to make it much faster.
In my Edge table, I have different information for each edges (distance, speed, duration, ...). The problem is so far my pgr_ksp() function only can return these data: seq, path_id, path_seq, node, edge, cost and agg_cost.
So far what I do to get the complete data needed, I need to add subqueries to return the complete data depending on the current edge of the sequence such as bellow:
SELECT seq, path_id AS route, path_seq, node, edge,
cost as duration,
(SELECT distance FROM edges WHERE id=edge) as distance,
(SELECT speed FROM edges WHERE id=edge) as speed,
agg_cost
FROM pgr_ksp('SELECT id,
source::integer,
target::integer,
duration::double precision AS cost,
reverse_cost::double precision
FROM edges
ORDER BY id',
source_id, target_id, alternatives, true, false);
How could I do to avoid subqueries and therefore improve query speed?