[postgis-users] toTopoGeom performance tips

[Sandro Santilli]

On Tue, May 15, 2012 at 03:45:13PM +1000, Luca Morandini wrote:
> 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.

Ok, then it's not an issue with transactions.

If you want to help further profiling please:
 (1) install latest GEOS from 3.3 branch
 (2) install latest postgis from trunk (make sure to upgrade topology scripts)
 (3) see how time relates to topology primitives population density, see if a
     specific geometry is taking a visible lot more than others to import,
     enable debugging in topology to figure where the time goes.

PS: your queries do look fine.

--strk;

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


> 
> 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,