[postgis-users] toTopoGeom performance tips

[Luca Morandini]

On 05/14/2012 05:38 PM, Sandro Santilli wrote:
> On Mon, May 14, 2012 at 05:25:47PM +1000, Luca Morandini wrote:
>> On 05/14/2012 04:39 PM, Sandro Santilli wrote:
>
>>> 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.
>
> Please run the benchmark before jumping to conclusions.

As I see it, it is more a matter of flexibility than performance.


> Looking forward for your numbers.

It took nearly 4 times as much - I divided the load in chunks of 100 polygons each 
- hence it seems one big transaction is better than many small ones.

Just in case I did something silly, here are the commands I sent for you to check:
    INSERT INTO lga11aaustlines (ogc_fid, topology)
      SELECT ogc_fid, topology.toTopoGeom(wkb_geometry, 'lgatopo', 1, 0)
         FROM lga11aaust
         OFFSET 0 LIMIT 100;
...
    INSERT INTO lga11aaustlines (ogc_fid, topology)
      SELECT ogc_fid, topology.toTopoGeom(wkb_geometry, 'lgatopo', 1, 0)
         FROM lga11aaust
         OFFSET 500 LIMIT 100;

Regards and thanks for your time,

Luca Morandini
Data Architect - AURIN project
Department of Computing and Information Systems
University of Melbourne