Opened 8 years ago

Closed 3 months ago

Last modified 5 weeks ago

#2861 closed enhancement (fixed)

PostGis Topology performance improvements : index on containing_face

Reported by: remic Owned by: strk
Priority: medium Milestone: PostGIS 3.3.0
Component: topology Version: 2.1.x
Keywords: performance Cc:

Description

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)

Change History (7)

comment:1 by strk, 7 years ago

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 :)

comment:2 by strk, 6 years ago

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.

comment:3 by strk, 6 years ago

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 ?

comment:4 by Algunenano, 3 years ago

Milestone: PostGIS Fund Me

comment:5 by strk, 5 months ago

Keywords: performance added

comment:6 by Sandro Santilli <strk@…>, 3 months ago

Resolution: fixed
Status: newclosed

In e4495fb/git:

Add index on node(containing_face)

Closes #2861

comment:7 by robe, 5 weeks ago

Milestone: PostGIS Fund MePostGIS 3.3.0
Note: See TracTickets for help on using tickets.