2

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?

  • Actually I wanted to avoid doing adding subqueries. I have a database with ten of thousands of edges, and using ksp has increased the query time by 3 to 5 times, and therefore increase the server load. I'm trying to find the solution that would avoid adding useless calculations and reducing the server load. – Rabou Nov 8 '16 at 4:43
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

  • Thanks a lot for the answer. That's what I was looking for! – Rabou Nov 7 '16 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.