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

[PostGIS]

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

Comment (by strk):

 I'm digging further in this, executing each query executed internally by
 ST_RemEdgeModFace.
 The first query looks like this:

 {{{
 select
  r.topogeo_id, r.layer_id, l.schema_name, l.table_name, l.feature_column
 FROM  topology.layer l
 INNER JOIN <TOPONAME>.relation r
 ON (l.layer_id = r.layer_id)
 WHERE l.level = 0 AND l.feature_type IN ( 2, 4 )
 AND l.topology_id = :topology_id
 AND r.element_type = 2 AND abs(r.element_id) = :edge_id;
 }}}

 The conditions used on the relation table are: layer_id, element_type,
 element_id.

 The index is created with fields: layer_id, topogeo_id, element_type,
 element_id -- this makes literal (query) values only usable in the order
 given, so since we do NOT have a 'topogeo_id' literal, the only usable
 literal is 'layer_id', which in the case I'm looking at matches *all*
 records, thus the planner decides to use a sequential scan.

 If the unique index was created with reordered fields (layer_id,
 element_type, element_id, topogeo_id) it would then be possibly usable for
 filtering on element_type, and if we changed the abs(r.element_id) to an
 r.element_id in (10, -10) we'd have the possibility to also make juse of
 the element_id in the filter. But reordering might slow down other uses
 (fetching elements of a known topogeometry) so this is not the best way to
 proceed.

 Rewriting this query is thus NOT a viable solution.

 A new index could serve queries searching for TopoGeometry objects using a
 given element of the topology.
 Elements of a topology are always identified by the tuple ( layer_id,
 element_type, element_id ) because the *same* element_id may have
 different meanings based on element_type and layer_id. Among the three
 fields, element_id is probably always the most selective.

 The next query, only performed if the edge to be removed has left_face !=
 right_face, looks like this:
 {{{
 SELECT t.* FROM (
   SELECT
     r.topogeo_id,
     r.layer_id,
     l.schema_name,
     l.table_name,
     l.feature_column,
     array_agg(r.element_id) as elems
   FROM topology.layer l
   INNER JOIN "${TOPO}".relation r ON (l.layer_id = r.layer_id)
   WHERE l.level = 0 and l.feature_type IN (3, 4)
   AND l.topology_id = id(findTopology('${TOPO}'))
   AND r.element_type = 3
   AND r.element_id = ANY (ARRAY[${LEFT_FACE}, ${RIGHT_FACE}]::int4[])
   GROUP by
     r.topogeo_id, r.layer_id, l.schema_name,
     l.table_name, l.feature_column
 ) t
 WHERE NOT t.elems @> ARRAY[${LEFT_FACE}, ${RIGHT_FACE}]::int4[];
 }}}

 The conditions used on the table are: element_type and element_id, no
 layer_id in this case as we're looking for faces in any non-hierarchical
 layer.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/2083#comment:13>
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.