[postgis-users] OT: Question on performance in plpgsql function

Stephen Woodbridge woodbri at swoodbridge.com
Tue Sep 6 06:32:40 PDT 2011


On 9/5/2011 11:26 PM, Stephen Woodbridge wrote:
> Hi all,
>
> This may be a little off topic, but there are a lot of smart minds here
> that might know.
>
> I have a plpgsql function the does some work to compute all the
> intersections with street names in a road network. It creates a vertex
> table like in pgrouting and an intersection table. Then populates the
> tables from a streets network and finally updates the intersection table
> based on the number of connected segments that have names.
>
> The performance issue I'm having is that if I include the final update
> in the procedure is takes forever (well longer than 12 hours when I
> aborted it) and postgres runs at 100 CPU for all that time. But if I
> comment out the update and run the procedure it takes say 1 hour to run,
> and then I then run the update from the command line it takes say
> another hour to run that, so a total of 2 hours.

OK, so here are some real numbers as i just aborted it after 48000 sec 
and rerunning part one without the update took 460 sec. and then running 
the update alone took 75 sec. So aborted at about 13 hrs vs completing 
in two steps in 9 minutes.

And after reading the docs some more I can not do a commit in any stored 
procedure, I guess I will have to wrap the two commands into a 
client-side script, unless anyone has any other ideas on why this is 
happening.

-Steve

> plpgsql function run in a single transaction and breaking it into two
> steps does it in two transaction, so I figure that has something to do
> with it. An you can not COMMIT in a plpgsql function.
>
> Any thoughts on how to fix this?
> Can a SQL procedure do a COMMIT? If so I might be able to reorg the
> process into two some plpgsql procedures that get called from a SQL
> procedure.
>
> Thoughts?
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users




More information about the postgis-users mailing list