[postgis-tickets] [PostGIS] #2861: PostGis Topology performance improvements : index on containing_face

[PostGIS]

#2861: PostGis Topology performance improvements : index on containing_face
-------------------------+--------------------------------------------------
 Reporter:  remic        |       Owner:  strk 
     Type:  enhancement  |      Status:  new  
 Priority:  medium       |   Milestone:       
Component:  topology     |     Version:  2.1.x
 Keywords:               |  
-------------------------+--------------------------------------------------
 The idea is to put a simple btree index on node.containing_face at table
 creation


 The index would be used in
 _the "intersects" function (case of point/polygon) "'WHERE  ... AND
 r2.element_id = n.containing_face" '"
 _the addface function ''WHERE containing_face IS NOT NULL"
 _ the ST_RemEdgeNewFace function "    -- Update containing_face for all
 nodes still referencing old faces"
 _the ST_RemEdgeModFace function "    -- Update containing_face for all
 nodes still referencing old faces"
 _ the _ST_AddFaceSplit function "  -- Update isolated nodes in new new
 face "


 I think there is significant evidence that such an index will be used.
 My usage of topology is very limited (I only use edge topo) and I don't
 have the benchmark to prove that such index would speed the operations.
 If somebody uses face/node, it should be simple to benchmark : drop
 topology, create topology, create index, import data in topology .
 Then a simple look on index stats should tell if it was used ans how much.

 However :
 _the above function will obviously be speeded.
 _index are mandatory on primary key (postgres default), this should be the
 same for "simple" foreign key
 _ the basic btree index is very low cost (time & memory)

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