[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

After adding the index  topo_ar5_sysdata.relation(abs(element_id)) we get about the same performance as with using the element_id direct, but it seems i vary some more .

explain analyze 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
;
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1450.51..64998.02 rows=100884 width=16) (actual time=0.061..0.066 rows=1 loops=1)
   Hash Cond: (r.layer_id = l.layer_id)
   ->  Bitmap Heap Scan on relation r  (cost=1449.96..63610.31 rows=100884 width=16) (actual time=0.027..0.032 rows=1 loops=1)
         Recheck Cond: (abs(element_id) = 30145)
         Filter: (element_type = 2)
         Rows Removed by Filter: 1
         ->  Bitmap Index Scan on opo_ar5_sysdata_relation_abs_element_id_idx  (cost=0.00..1424.74 rows=138973 width=0) (actual time=0.019..0.019 rows=2 loops=1)
               Index Cond: (abs(element_id) = 30145)
   ->  Hash  (cost=0.53..0.53 rows=2 width=4) (actual time=0.023..0.023 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Seq Scan on layer l  (cost=0.00..0.53 rows=2 width=4) (actual time=0.016..0.019 rows=2 loops=1)
               Filter: ((topology_id = 21) AND (level = 0))
 Total runtime: 0.126 ms
(13 rows)

To avoid extra a index I may also use a union but that only works a long as most of the element_id > 0.

SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l  
WHERE  r.element_id > -1 AND 
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
union
SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l  
WHERE  r.element_id < 0 
AND 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

I go for the functional index now and that may also cover other cases that I have not seen in the logs yet.

Thanks Lars

________________________________________
Fra: Sandro Santilli [sandro.santilli at gmail.com] på vegne av Sandro Santilli [strk at keybit.net]
Sendt: 28. mars 2015 21:32
Til: Lars Aksel Opsahl
Kopi: PostGIS Users Discussion
Emne: Re: SV: [postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.

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;