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?