[PostGIS] #5667: Performance Postgis Topolgy when adding millions by using TopoGeo_AddLineString

[PostGIS]

#5667: Performance Postgis Topolgy when adding millions by using
TopoGeo_AddLineString
--------------------------------+-------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  strk
      Type:  enhancement        |     Status:  new
  Priority:  medium             |  Milestone:
 Component:  topology           |    Version:  3.4.x
Resolution:                     |   Keywords:
--------------------------------+-------------------
Comment (by strk):

 There is a `topology/test/perf/TopoGeo_addLinestring.sql` script that was
 written specifically to analyze performance of that function.

 That script calls `TopoGeo_addLinestring` 30752 times and there are
 exactly 30752 queries to the topology.topology table (so ratio of 1/1).

 I should also mention that the time cost of the topology.topology queries
 sums up to 142ms over the 44212ms of the statement resulting in those
 30752 linestring additions, so we are talking about the 0.3% of the total
 operation. There are bigger fishes to tackle.

 Biggest fish confirms being the query that looks for newly formed ringsas
 shown by a query to pg_stat_statements ordered by total execution time:
 {{{
    n    | tot_ms | avg_ms |
 q
 --------+--------+--------+-------------------------------------------------------------------------------------------------------------------------
       1 |  44212 |  44212 | SELECT count(*) FROM ( SELECT
 topology.TopoGeo_addLinestring($1, g) FROM
 topoperf.case_full_coverage_no_holes ) foo
    2883 |   3480 |      1 | WITH RECURSIVE edgering AS ( SELECT $1 as
 signed_edge_id, edge_id, next_left_edge, next_right_edge FROM
 "topoperf".edge
  141824 |   1818 |      0 | SELECT $2 FROM ONLY "topoperf"."face" x WHERE
 "face_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
   46624 |   1783 |      0 | SELECT
 edge_id,start_node,end_node,left_face,right_face,next_left_edge,next_right_edge,geom
 FROM "topoperf".edge WHERE
     900 |   1688 |      2 | WITH newedges(edge_id,left_face) AS ( VALUES
 ($1,$2),($3,$4),($5,$6),($7,$8),($9,$10),($11,$12),($13,$14),($15,$16),($1
   46624 |   1557 |      0 | SELECT edge_id,geom FROM "topoperf".edge WHERE
 geom && $1::geometry
   46624 |   1427 |      0 | SELECT node_id,containing_face,geom FROM
 "topoperf".node WHERE geom && $1::geometry
     900 |   1147 |      1 | WITH newedges(edge_id,right_face) AS ( VALUES
 ($1,$2),($3,$4),($5,$6),($7,$8),($9,$10),($11,$12),($13,$14),($15,$16),($
   15870 |   1144 |      0 | UPDATE "topoperf".edge_data SET
 next_left_edge= $1, abs_next_left_edge= $2 WHERE edge_id = $3
   61504 |   1081 |      0 | SELECT node_id,geom FROM "topoperf".node WHERE
 ST_DWithin(geom, $1::geometry, $2)
   15872 |   1036 |      0 | INSERT INTO "topoperf".edge_data
 (edge_id,start_node,end_node,left_face,right_face,next_left_edge,
 abs_next_left_edge,n
     961 |    585 |      1 | SELECT edge_id,left_face,right_face,geom FROM
 "topoperf".edge_data WHERE ( left_face = ANY($1) OR right_face = ANY ($1)
   14912 |    542 |      0 | SELECT
 edge_id,start_node,end_node,left_face,right_face,geom FROM
 "topoperf".edge_data ORDER BY geom <-> $1 ASC LIMIT $
   14912 |    475 |      0 | INSERT INTO "topoperf".node
 (node_id,containing_face,geom) VALUES (DEFAULT,$1,$2::geometry) RETURNING
 node_id
   14912 |    386 |      0 | SELECT EXISTS ( SELECT $1 FROM
 "topoperf".edge_data WHERE ST_Within($2::geometry, geom))
  126912 |    338 |      0 | SELECT $2 FROM ONLY "topoperf"."node" x WHERE
 "node_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
   14912 |    322 |      0 | UPDATE "topoperf".node SET containing_face
 =$1::int WHERE node_id = $2
   14912 |    311 |      0 | SELECT EXISTS ( SELECT $1 FROM "topoperf".node
 WHERE ST_Equals(geom, $2::geometry))
   46654 |    248 |      0 | SELECT
 edge_id,start_node,end_node,left_face,right_face,next_left_edge,next_right_edge,geom
 FROM "topoperf".edge_data W
   14912 |    220 |      0 | SELECT edge_id,geom FROM "topoperf".edge_data
 WHERE ST_DWithin($1::geometry, geom, $2)
       1 |    170 |    170 | CREATE TABLE
 topoperf.case_full_coverage_no_holes AS SELECT
 ST_Subdivide(ST_Segmentize(ST_Boundary(geom), 0.5), 5) g FR
   30752 |    142 |      0 | SELECT id,srid,precision,$2::geometry FROM
 topology.topology WHERE name = $1::varchar
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5667#comment:16>
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.