[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 07:16:10PM +0000, Lars Aksel Opsahl wrote:
> Hi 
> 
> Thanks a lot for the quick response. Sorry I forget to say that I have already have added indexes to get things to turn around with these number rows. I also refer to this id's from my own tables.
> 
> Here are the indexes that I have added on topo_ar5_sysdata.relation 
> 
>     "opo_ar5_sysdata_relation_element_id_idx" btree (element_id)
>     "opo_ar5_sysdata_relation_layer_id_idx" btree (layer_id)
>     "opo_ar5_sysdata_relation_topogeo_id_idx" btree (topogeo_id)

Ok, that explains.
I think simply adding a btree on abs(element_id) would give you a good
speed with the current query (the number of layers and topogeometry sharing
the same element should generally be low).

> I will add a comment to http://trac.osgeo.org/postgis/ticket/2083

Thanks, numbers with the expressional index will be useful.
Note that adding an index will slighly slow down insertions, so it will
be worth timing that one too with and w/out the index.

--strk;