[postgis-tickets] [PostGIS] #4933: Slow topology population due to cost of finding face containing point.

[PostGIS]

#4933: Slow topology population due to cost of finding face containing point.
-------------------------+---------------------------
 Reporter:  strk         |      Owner:  strk
     Type:  enhancement  |     Status:  new
 Priority:  medium       |  Milestone:  PostGIS 3.2.0
Component:  topology     |    Version:
 Keywords:               |
-------------------------+---------------------------
 In a real-world case where multiple faces's MBR contain a given point,
 a
 ding unconnected linestrings has the most expensive phase being finding
 the face containing both of its endpoints. The following query is run
 twice (once per endpoint):

 {{{
 WITH faces AS (
  SELECT face_id
  FROM "topo_ar5ngis_sysdata_webclient".face
  WHERE mbr && $1
  ORDER BY ST_Area(mbr) ASC
 )
 SELECT face_id FROM faces
 WHERE
 _ST_Contains(topology.ST_GetFaceGeometry('topo_ar5ngis_sysdata_webclient',
 face_id), $1) LIMIT 1
 }}}

 In the intention of the author (me) the CTE would have been executed
 first, and then the filter would have been applied to those faces in the
 order specified in the CTE, but with PostgreSQL 12 this is not happening,
 and instead the CTE is being inlined.

 Chances are using WITH MATERIALIZED would fix this problem:
 https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH

 Another approach would do the loop internally rather than delegating the
 strategy to the PostgreSQL planner.
 Doing the loop internally could also avoid building the whole face
 geometry but only build the exterior ring of each face, as the smallest
 ring containing the point would be the exterior ring of the face
 effectively containing it.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4933>
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.