[postgis-users] toTopoGeom performance tips

[Luca Morandini]

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