[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 06:46:14PM +0100, Sandro Santilli wrote:

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

For the record, these are the numbers I get on a 35,128,772 rows table:

strk=# with c as ( select id from tweets ) select count(*) from c;
 35128772
Time: 10305.404 ms
strk=# with c as ( select abs(id) from tweets ) select count(*) from c;
 35128772
Time: 11495.359 ms

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

I found a ticket which was filed starting from another function
( ST_RemEdgeModFace ) but is due to the same issue:
http://trac.osgeo.org/postgis/ticket/2083

You may add your observations there.

--strk;

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