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

[Sandro Santilli]

On Sat, Mar 28, 2015 at 03:47:51PM +0000, Lars Aksel Opsahl wrote:

> 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.

[...]

> 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.
> 
> 
> Is this a safe change or do I have to use functional index instead ?

For lineal TopoGeometry the sign in the relation table indicates
the direction of the line. It's not really well-tested in reality
but that's the theory. 

The only index on the relation table is a btree on

 (layer_id, topogeo_id, element_id, element_type)

So there should be no way for that query to use the index unless the
"topogeo_id" is also known, which is what we're looking for instead.

Since you instead get a 200 times speedup, I'd be curious to find
out why. Maybe that's Could you run that query trough EXPLAIN to check ?
Is it really that abs(int) for 27,794,698 times takes ~3 seconds ?
How would changing the query to use EXISTS help, if at all ?

I suggest you file an enhancement ticket on trac for this.
I'd be in favor of adding an index to speed up checking existance
of TopoGeometries defined by given primitives.

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html