I am updating thousands of rows in a survey table finding shortest path from each place a survey respondent visits. My code below is the query I use to update the blank route column. The function "pgr_fromAtoPO" is the same as the "pgr_fromAtoB" listed on the pgr tutorial, except the name of the tables have been changed. I initially tried to run the querry to update my entire table, with 90,000 rows of start and end points. This ran for 2 days and crashed my 8GB RAM 2.0GHZ quad core macbook pro, so I have since changed my querry to limit the rows to 5,000. This querry took below took 112 minutes on 5000 routes.
update analysis.po_routing set route = (select st_multi(st_union(geom)) from pgr_fromAtoPO('osm2po_routing',x1,y1,x2,y2)) where uniqueid < 605300515 and uniqueid >= 604479116 and route is null and x1 is not null;
My question is: Are these functions optimized for single calls (i.e. what a website might get), so would I be better off looping through each individual row separately rather than a call to update 5,000 rows, and is there some sort of memory limit, such that I would be better off updating a smaller chunk of rows at a time, say 500 or 1000 to improve the speed? Thanks for your help.