[postgis-devel] Postgis topology creation - O(n-squared)? - creates problems with large datasets.

[Sandro Santilli]

On Tue, Jan 21, 2014 at 11:58:00AM +0100, Sandro Santilli wrote:
> On Tue, Jan 21, 2014 at 09:20:36AM +0000, Graeme B. Bell wrote:

> > - Profiling: currently, on large datasets, what 3-5 functions eat the most time in createTopo or the incremental approach, and why? 
> 
> ST_ModEdgeSplit is the most expensive function for the incremental approach.
> It performs checks that are not needed when called from the internal population
> function. An improvement would be splitting the function into an internal one
> and an external one, and calling the internal from internal code. This has been
> done successfully in other cases.

As for CreateTopoGeom (batch approach), here are the stats for adding the
first 10 polygons in the test dataset:

strk=# select * from pg_stat_user_functions order by total_time desc limit 10;                                          funcid | schemaname |     funcname      | calls | total_time | self_time
--------+------------+-------------------+-------+------------+-----------
  54510 | topology   | st_createtopogeo  |     1 |    125.349 |     7.153
  54503 | topology   | st_addedgemodface |    10 |     93.129 |    40.687
  62973 | topology   | _st_addfacesplit  |    20 |     48.949 |    32.145
  54491 | topology   | getringedges      |    20 |     15.872 |    15.872
  54508 | topology   | st_addisonode     |    10 |     13.167 |    12.953
  54490 | topology   | getnodebypoint    |    20 |      6.564 |     5.568
  52390 | public     | st_unaryunion     |     3 |      4.291 |     4.291
  52429 | public     | st_relate         |    15 |      1.527 |     1.527
  52436 | public     | _st_intersects    |    20 |       0.89 |      0.89
  52331 | public     | text              |   200 |      0.785 |     0.785

Again API-level functions being called internally (ST_AddEdgeModFace,
ST_AddIsoNode).

--strk;