[postgis-users] toTopoGeom performance tips

[Sandro Santilli]

On Mon, May 14, 2012 at 11:59:32AM +1000, Luca Morandini wrote:
> Folks,
> 
> I have some complex geometries (about 500 polygons, each with an average number of
> xxx points) on which to build a topology.
> 
> Unfortunately, the process is rather slow - it is about 90 minutes on a dedicated
> 2GB Ubuntu VM - hence I am here asking for ideas on how to tune PostgreSQL.
> 
> I was unable to find any obvious bottleneck:
> - CPU load is about 8% after I increased the number of VCPUs.
> - Total consumption of RAM is low compared to the available memory.
> - I already increased the shared buffer memory to 128MB - still no joy. I doubt
> whether this last move was helpful... but I did try.

Are you using current PostGIS trunk ? 

I've noticed that a single transaction takes a lot more time than multiple.
This is because toTopoGeometry is an hard database writer and the transaction
must keep track of all changes to eventually roll-back. The presence of
sub-transactions make things somewhat worst.

Try loading your 500 polygons in 5 chunks of 100 or 10 chunks of 50,
and let us know how timing changes (if at all). Chunking is also a way to
eventually find geometries taking a lot more than the others. And is one
of the advantages of a persistent topology: can be built incrementally.

Keep me informed about your progress, I'd also love to speed things up :)

--strk;

  ,------o-. 
  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
  `-o------'