PostGis Topology performance improvements : index on containing_face

[remic]

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)

[strk]

I just stumbled upon the usage in _ST_AddFaceSplit function as I move toward having it rewritten in C. This comment is just to confirm that an index on containing_face would be used. It'll be interesting to determine if the probable high frequency of NULL values there would recommend making a partial index rather than a full index (ie: WHERE containing_face IS NOT NULL).

Of course numbers would still be interesting to read here

[strk]

I'm witnessing a huge delay in a DELETE of a single face for a topology, and I suspect it's due to the lack of the index on containing_face.

[strk]

I wonder how we should deal with upgrades, if we add the index.

Maybe TopologySummary should notify the user about the need to update a postgis topology structure, and we'd provide a function to do so ?

[Sandro Santilli <strk@…>]

In e4495fb/git:

Add index on node(containing_face)

Closes #2861