pgr_ksp() returns other columns from edge table

Question

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?

Answer 1

Do a JOIN not a subselect.

Try this:

SELECT seq, path_id AS route, path_seq, node, edge,
   cost as duration,
  e.distance,
  e.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)  AS ksp
     LEFT JOIN edges AS e ON e.id = ksp.edge;

By the way, this very topic is covered in our book still in draft so half off until complete.

http://locatepress.com/pgrouting