[PostGIS] #5990: Adding 2 lines to Postgis topology takes 2 hours in a production line we have

[PostGIS]

#5990: Adding 2 lines to Postgis topology takes 2 hours in a production line we
have
--------------------------------+---------------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  strk
      Type:  enhancement        |     Status:  reopened
  Priority:  medium             |  Milestone:  PostGIS 3.6.1
 Component:  topology           |    Version:  3.6.x
Resolution:                     |   Keywords:  performance
--------------------------------+---------------------------
Comment (by strk):

 I've downloaded the ladd_2_lines_longtime.sql.gz attachment and split it
 in 2 files: init.sql and test.sql. The init.sql one only creates the
 topology and adds the first line, very quickly. The test.sql passes the
 second line to TopoGeo_addLinestring and indeed takes a very long time
 with:

 {{{
 POSTGIS="3.7.0dev 3.6.0rc2-52-g96ad06150" [EXTENSION]
 PGSQL="170"
 GEOS="3.15.0dev-CAPI-1.21.0"
 }}}

 I've enabled debugging in the build and found out that the initial
 snapping phase is pretty quick but the noding of the incoming line (4417
 vertices) with the existing line (7873) results in splitting the incoming
 line into 3772 portions, meaning it's found to intersect with the existing
 linework 3771 times !

 This in turn makes the code enter a loop with 3772 iterations, in which on
 every iteration (for each component of the splitting of the incoming
 line):

    - DB is queried to see if the start or end nodes already exist, or:
      - DB is updated to add the missing nodes, checking if any of them
 would split an existing edge
    - DB is queried to see if the component (to become an edge) already
 exists, or:
      - DB is updated to add the missing edge
      - DB is queried to check if the addition of the new edge forms a new
 face.

 It's really a lot of work, not surprising to take a long time (#2993
 applies here, for possible improvements).

 By looking at the image, I would expect that increasing the snap tolerance
 could reduce the number of split components, and indeed that's what
 happens: by using 1e-6 instead of 1e-7 the incoming line is more properly
 snapped to the existing line and only 19 parts of it are found to need
 being added to the database, which takes in total 400ms on my system.


 [[Image(Screenshot 2025-09-19 at 13.43.52.png)]]
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5990#comment:12>
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.