[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):

 I've configured my PostgreSQL-14 cluster to preload the
 `pg_stat_statements` module and created the `pg_stat_statements` extension
 in a test database. Then I've created a topology with a single segment
 edge, reset the stats with `SELECT pg_stat_statements_reset()` and then
 used `TopoGeo_addLineString` to add a single-segment line crossing the
 existing line.

 These are the statements (including nested) resulting form that simple
 operation:
 {{{
  n  |
 query
 ----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  11 | SELECT $2 FROM ONLY "t"."node" x WHERE "node_id"
 OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
   2 | SELECT edge_id,start_node,end_node,left_face,right_face,geom FROM
 "t".edge_data ORDER BY geom <-> $1 ASC LIMIT $2
  12 | SELECT $2 FROM ONLY "t"."face" x WHERE "face_id"
 OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
   1 | SELECT
 edge_id,start_node,end_node,left_face,right_face,next_left_edge,next_right_edge,geom
 FROM "t".edge_data WHERE edge_id IN ($1)
   3 | INSERT INTO "t".node (node_id,containing_face,geom) VALUES
 (DEFAULT,$1,$2::geometry) RETURNING node_id
   1 | UPDATE "t".edge_data SET end_node= $1,next_left_edge= $2,
 abs_next_left_edge= $3,geom=$4::geometry WHERE edge_id = $5
   3 | SELECT node_id,containing_face,geom FROM "t".node WHERE geom &&
 $1::geometry
   3 | SELECT edge_id,geom FROM "t".edge_data WHERE
 ST_DWithin($1::geometry, geom, $2)
   3 | SELECT EXISTS ( SELECT $1 FROM "t".node WHERE ST_Equals(geom,
 $2::geometry))
   3 | SELECT nextval($1)
   3 | SELECT
 edge_id,start_node,end_node,left_face,right_face,next_left_edge,next_right_edge,geom
 FROM "t".edge WHERE geom && $1::geometry
   1 | SELECT r.element_id, r.topogeo_id, r.layer_id, r.element_type FROM
 "t".relation r , topology.layer l WHERE l.topology_id = $1 AND l.level =
 $2 AND l.layer_id = r.layer_id AND r.element_id IN ( $3, $4) AND
 r.element_type = $5
   3 | INSERT INTO "t".edge_data
 (edge_id,start_node,end_node,left_face,right_face,next_left_edge,
 abs_next_left_edge,next_right_edge, abs_next_right_edge,geom) VALUES
 ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10::geometry)
   2 | SELECT EXISTS ( SELECT $1 FROM "t".edge_data WHERE
 ST_Within($2::geometry, geom))
   5 | SELECT
 edge_id,start_node,end_node,left_face,right_face,next_left_edge,next_right_edge,geom
 FROM "t".edge_data WHERE start_node IN ($1) OR end_node IN ($2)
   4 | SELECT node_id,geom FROM "t".node WHERE ST_DWithin(geom,
 $1::geometry, $2)
   1 | UPDATE "t".edge_data SET next_right_edge= $1, abs_next_right_edge=
 $2 WHERE start_node = $3 AND next_right_edge= $4 AND  abs_next_right_edge=
 $5 AND edge_id != $6
   4 | SELECT edge_id,geom FROM "t".edge WHERE geom && $1::geometry
   1 | select topogeo_addlinestring($1, $2)
   8 | SELECT $2 FROM ONLY "t"."edge_data" x WHERE "edge_id"
 OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
   2 | UPDATE "t".node SET containing_face =$1::int WHERE node_id = $2
   1 | UPDATE "t".edge_data SET next_left_edge= $1, abs_next_left_edge= $2
 WHERE end_node= $3 AND next_left_edge= $4 AND  abs_next_left_edge= $5 AND
 edge_id != $6
   2 | SELECT node_id,containing_face,geom FROM "t".node WHERE node_id IN
 ($1,$2)
   1 | SELECT id,srid,precision,null::geometry FROM topology.topology WHERE
 name = $1::varchar
   1 | UPDATE "t".edge_data SET next_right_edge= $1, abs_next_right_edge=
 $2 WHERE edge_id = $3
   1 | UPDATE "t".edge_data SET next_left_edge= $1, abs_next_left_edge= $2
 WHERE edge_id = $3
 (26 rows)
 }}}

 The query I've used to extract the information:
 {{{
 select calls n, query from pg_stat_statements where dbid = ( select oid
 from pg_database where datname = current_database() ) and query not like
 '%pg_stat_statements%';
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5667#comment:15>
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.