Optimization of pgr_drivingdistance from pgrouting in PostGIS

Question

I am using pgr_DrivingDistance from pgrouting in PostGIS on a Linux server to calculate isolines of distances from a set given starting points. I include an example below. I have thousands of starting points and I am generating a polygon around each individual point representing 5km driving distances along the road network.

I have some functioning code which works but is both inelegant and very slow. So it works fine if there's only a few points but for thousands it is very slow indeed to the point of not actually being useful. I realise that calculating routes along a network will not be particularly fast but I'm thinking more is there anyway to improve the code structure (e.g. can I get rid of the loop somehow?) in order to get the best out of it? I'm still very much a novice to Postgresql/PostGIS so still learning.

-- Calculate driving distance for each starting_point
for id_number in select vertex_id, addresses_id from starting_points loop
    vid = id_number.vertex_id;
    aid = id_number.addresses_id;

    -- Create a table of points for each starting node within driving distance of buffer_size
    drop table if exists temp_points;
    Create temp table temp_points as 
        SELECT drivingdistances.*, topo_nodes.id, topo_nodes.the_geom 
        FROM pgr_drivingDistance( 'SELECT id, source, target, cost_len as cost FROM segmented_roads', 
        vid, buffer_size, false) as drivingdistances 
        LEFT JOIN topo_roads_vertices as topo_nodes on drivingdistances.node = topo_nodes.id;

    -- Put a polygon around the nodes for one address
    drop table if exists temp_polys;
    create temp table temp_polys as 
        select ST_ConcaveHull(ST_Collect(the_geom),0.99,false) as geom 
                from temp_points;

    ALTER TABLE temp_polys ADD vertex_id integer;
    UPDATE temp_polys SET vertex_id = vid;
    ALTER TABLE temp_polys ADD address_id integer;
    UPDATE temp_polys SET address_id = aid;
    
    -- insert polygon for single address into final output_polys
    insert into output_polys (geom, starting_vid, address_id)
        select geom, vertex_id, address_id from temp_polys;

end loop;

Answer 1

I can suggest small tweaks to help scaling down the temp memory space usage which may improve performance if your machine is short on available memory:

1. Your temp_points memory table does not need any of the drivingdistances columns, only topo_nodes.id and the_geom.
2. Next, perform the drop tables as soon as possible within the loop, leaving memory available to be swept up in garbage collection sooner rather than later.

-- As I said, only small tweaks, but if your machine is memory limited, this may help prevent spooling memory to disk.