[postgis-tickets] [PostGIS] #2083: ST_RemEdgeModFace performance review

[PostGIS]

#2083: ST_RemEdgeModFace performance review
-------------------------+--------------------------------------------------
 Reporter:  strk         |       Owner:  strk         
     Type:  enhancement  |      Status:  new          
 Priority:  medium       |   Milestone:  PostGIS 2.2.0
Component:  topology     |     Version:  2.0.x        
 Keywords:               |  
-------------------------+--------------------------------------------------

Comment(by laopsahl):

 Hi

 After adding the index  topo_ar5_sysdata.relation(abs(element_id)) the
 performance is much better for a single select the time goes from more
 than 3000 ms to around 20-40 ms. In the layer we are testing the
 topo_ar5_sysdata.relation which contains  27794698 rows. For the end user
 the update time goes down from 30 seconds to about 1 second for a user
 that that is drawing simple polygon to update this layer.

 Her is the explain with the functional index added

 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 index I may also use a or, but that only works a long as
 most of the element_id > 0. I have not seen any rows with a value below
 zero yet.

 <       || ' AND abs(r.element_id) = e.edge_id';
 ---
 >       || ' AND ((r.element_id >-1 AND r.element_id = e.edge_id) OR (
 r.element_id < 0 AND abs(r.element_id) = e.edge_id))';


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

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/2083#comment:6>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.