PostGis Topology performance improvements : index on containing_face
|Reported by:||remic||Owned by:||strk|
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)