PostgreSQL optimization: Select road edges where start and end vertex is in some set

Question

The pgRouting pgr_drivingDistance function returns only ids of road network vertices, but without ids of road edges in specific drive time.
To find this road edges I created the query:

SELECT all_roads.id, the_geom
    FROM
        (
            SELECT e.id, e.the_geom
            FROM tmp_ddist dd,
                tmp_edge e
            WHERE
                e.target = dd.vertex 
        UNION
            SELECT e.id, e.the_geom
            FROM tmp_ddist dd,
                tmp_edge e
            WHERE
                e.source = dd.vertex 
        ) all_roads
    GROUP BY all_roads.id, the_geom
    HAVING COUNT(all_roads.id) = 2

Basically it finds road edges which have source and target vertices in set generated by pgr_drivingDistance function and returns only those edges which have both source and target in this set.

Is there any way to optimize this query?

Answer 1

Can you try like that and see if this helps:

SELECT all_roads.id, the_geom
  FROM
    (
        SELECT e.id, e.the_geom
        FROM tmp_ddist dd,
            tmp_edge e
        WHERE
            e.target = dd.vertex 
    UNION
        SELECT e2.id, e2.the_geom
        FROM tmp_ddist dd2,
            tmp_edge e2
        WHERE
            e2.source = dd2.vertex AND
            e2.id <> e.id
    ) all_roads
GROUP BY all_roads.id, the_geom
HAVING COUNT(all_roads.id) = 2

I am curious to see the benchmarking between the queries.