On 05/14/2012 04:39 PM, Sandro Santilli wrote:
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 ?
No, the relesed 2.0.
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.
I see... well, having transactions similar to those in Oracle certainly would not
have hurt here.
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 :)
Ok, I'll try with LIMIT and OFFSET, thanks Sandro.
Luca Morandini
Data Architect - AURIN project
Department of Computing and Information Systems
University of Melbourne
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
