[postgis-tickets] [PostGIS] #2083: ST_RemEdgeModFace performance review

[PostGIS]

#2083: ST_RemEdgeModFace performance review
--------------------------+-----------------------------
  Reporter:  strk         |      Owner:  strk
      Type:  enhancement  |     Status:  reopened
  Priority:  medium       |  Milestone:  PostGIS Fund Me
 Component:  topology     |    Version:  2.0.x
Resolution:               |   Keywords:  performance
--------------------------+-----------------------------

Comment (by strk):

 Confirmed: those triggers are deferred so you don't see the cost of firing
 them until you COMMIT your transaction or force firing them (set
 constraint immediate).
 The cost of those triggers is very very high, especially during mass
 removal of edges.

 Given we have a good ValidateTopology I wonder if we should completely
 drop those foreign keys (and other constraints as well).

 Here are some times (seconds elapsed since start of process).
 The operation is conducted against a topology having

 Without the index on the abs_* attributes:
 {{{
 0 psql:clearonly.sql:50: NOTICE: Removing unused
 0.590315 psql:clearonly.sql:50: NOTICE: Removed 178
 0.590379 psql:clearonly.sql:50: NOTICE: Removing isolated
 0.622091 psql:clearonly.sql:50: NOTICE: Removed 127
 0.622126 psql:clearonly.sql:50: NOTICE: Removing unneeded
 0.624795 psql:clearonly.sql:50: NOTICE: Removed 0
 0.624816 305
 0.624918 Firing next_left_edge_exists
 249.487 SET CONSTRAINTS
 249.487 Firing next_right_edge_exists
 508.951 SET CONSTRAINTS
 }}}

 We're basically talking about under one second to remove 178 edges and 127
 nodes (which are basically merging of 127 pairs of edges, mostly. And over
 8 MINUTES to check the foreign keys.

 Adding those indexes give instead these numbers:
 {{{
 0 psql:clearonly.sql:53: NOTICE: Removing unused
 0.795837 psql:clearonly.sql:53: NOTICE: Removed 178
 0.795877 psql:clearonly.sql:53: NOTICE: Removing isolated
 0.827427 psql:clearonly.sql:53: NOTICE: Removed 127
 0.827465 psql:clearonly.sql:53: NOTICE: Removing unneeded
 0.829889 psql:clearonly.sql:53: NOTICE: Removed 0
 0.829908 305
 0.830004 Firing next_left_edge_exists
 0.843845 SET CONSTRAINTS
 0.843973 Firing next_right_edge_exists
 0.859349 SET CONSTRAINTS
 }}}

 Which means foreign keys check goes down from 8 minutes to 0.3 seconds.
 Worth adding those indices I'd say.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/2083#comment:20>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.