[postgis-users] Topology building performance (was: Topology: cannot delete slivers)

[Sandro Santilli]

On Wed, Nov 19, 2014 at 04:47:48PM +0100, Sandro Santilli wrote:
> On Wed, Nov 19, 2014 at 12:50:09PM +0100, Rémi Cura wrote:
> >
> > Adding one feature is actually quite fast, even on already big topology.
> > 
> > Its when you want to add a lot's that it becomes increasingly slow (maybe
> > because indexes are not updated,or because we are in one transaction?)
> >
> > The slowing seems to be very non linear, probably following n^2, where n is
> > the number of feature already constructed in the transaction.
> 
> An issue with index use was recently fixed.
> There might be another one hiding somewhere.

On a closer look, I'm thinking the single-transaction is what commonly
hits during topology building (UPDATE .. SET tg = toTopoGeom ..)

Starting from an empty topology and running a single statement
invoking toTopoGeom for each of many inputs result in no stats ever
being visible by the planner within the transaction. In turn this
is likely to opt for sequencial scans (an empty table is quicker to
scan sequencially).

This would explain why populating in chunks works better, using
a transaction for each chunk
(UPDATE .. SET .. WHERE gid >= N AND gid < N+chunksize)

It could be interesting to try a wrapper function taking care of
running ANALYZE on the primitive tables every N calls to toTopoGeom
(or N primitives being created, regardless of number of simple inputs).

--strk;

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt