[postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.

[Lars Aksel Opsahl]

Hi


We have a data set with many millions of lines/polygons and we use the postgis topology package and it is working quite well, but update is sometimes going slow.


The problem is related to abs(r.element_id) generated when the function

topology.ST_ModEdgeSplit(atopology varchar, anedge integer, apoint geometry) is called.


The sql generated is this :


“SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l WHERE l.topology_id = 21 AND l.level = 0 AND l.layer_id = r.layer_id AND abs(r.element_id) = 30145 AND r.element_type = 2”


and it returns 1 row in 3173.108 ms


If I remove the abs function and use the r.element_id directly it sometimes go about 200 times faster.


SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l WHERE l.topology_id = 21 AND l.level = 0 AND l.layer_id = r.layer_id AND r.element_id = 30145 AND r.element_type = 2

;

topogeo_id | layer_id | element_id | element_type

------------+----------+------------+--------------

30148 | 1 | 30145 | 2

(1 row)


Time: 14.746 ms


I checked the database and I could not find any negative values for element_id in the table topo_ar5_sysdata.relation which contains 27794698 rows.


I have changed all places in the topology package from “abs(r.element_id)” to “r.element_id” and it seems to work ok.


Is this a safe change or do I have to use functional index instead ?


Thanks Lars at Skogoglandskap