[postgis-users] toTopoGeom take long time

[Sandro Santilli]

On Sun, May 19, 2013 at 07:49:05PM +0200, Eva Linhartová wrote:
> Hi,
> I'm trying to insert geometry data to topology structure. I have 7000
> polygons and the procedure take very long time now it's about 22 hours.
> 
> UPDATE plochy  SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005);
> 
> I run the postgis database on my laptop:
> procesor: Intel(R) Core(TM) i5-2430M CPU 2.40GHz
> RAM: 8GB
> type: 64bit
> 
> I'm newbie and I tryed it only on my sample data.
> Is this time still ok or should I stop the query?

After 22 hours I'd rather keep it going, but next time you'd better do
this in chunks, ie:

  UPDATE plochy SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005)
    WHERE gid >= 0 AND gid < 500;
  UPDATE plochy SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005)
    WHERE gid >= 500 AND gid < 1000;
  ...

Doning so would first of all save partial work (which would instead by
completely reverted if a problem occurs) but could also speed things up
by reducing the memory required to rollback the whole operation.

I think the bad performance is due to slow disk operations, there are
a lot of read/writes to database tables during topology building.

--strk;