[PostGIS] #5707: slow cb_getEdgeByFace seems like missing value for bbox parameter

[PostGIS]

#5707: slow cb_getEdgeByFace seems like missing value for bbox  parameter
-------------------------------+---------------------------
 Reporter:  Lars Aksel Opsahl  |      Owner:  strk
     Type:  enhancement        |     Status:  new
 Priority:  medium             |  Milestone:  PostGIS 3.4.3
Component:  topology           |    Version:  3.4.x
 Keywords:                     |
-------------------------------+---------------------------
 In this case push data in to black box in the attached area where there
 are no data from before .


 From pg_stat_activity this in -[ RECORD 1 ]--

 {{{
 total_min | 1.6338761423000014
 avg_ms    | 123.15649313819097
 calls     | 796
 query     | SELECT edge_id,left_face,right_face,geom FROM
 "tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE ( left_face = ANY($1)  OR
 right_face = ANY ($1) )
 }}}

 If I run a test SQL like below we that takes 350 ms.


 {{{
  explain analyze              SELECT edge_id,left_face,right_face,geom
 FROM "tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE ( left_face =
 ANY('{0}')  OR right_face = ANY ('{0}') )
 ;
                                                     QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------
  Seq Scan on edge_data  (cost=0.00..29820.10 rows=94479 width=162) (actual
 time=0.072..346.232 rows=78609 loops=1)
    Filter: ((left_face = ANY ('{0}'::integer[])) OR (right_face = ANY
 ('{0}'::integer[])))
    Rows Removed by Filter: 243439
  Planning Time: 0.180 ms
  Execution Time: 348.289 ms
 (5 rows)


 }}}

 but if I add the bbox paramater it run more than 100 times faster and
 takes less 3 ms.


 {{{
  explain analyze              SELECT edge_id,left_face,right_face,geom
 FROM "tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE geom &&
 '0103000020A21000000100000005000000FC705E4CD73D15404AD7234690F24D40FC705E4CD73D15405870E3E178F54D40E01FF517366A15405870E3E178F54D40E01FF517366A15404AD7234690F24D40FC705E4CD73D15404AD7234690F24D40'
 AND ( left_face = ANY('{0}')  OR right_face = ANY ('{0}') )
 ;
 QUERY PLAN
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on edge_data  (cost=7.37..133.12 rows=37 width=162)
 (actual time=2.557..2.610 rows=9 loops=1)
    Recheck Cond: (geom &&
 '0103000020A21000000100000005000000FC705E4CD73D15404AD7234690F24D40FC705E4CD73D15405870E3E178F54D40E01FF517366A15405870E3E178F54D40E01FF517366A15404AD7234690F24D40FC705E4CD73D15404AD7234690F24D40'::geometry)
    Filter: ((left_face = ANY ('{0}'::integer[])) OR (right_face = ANY
 ('{0}'::integer[])))
    Heap Blocks: exact=3
    ->  Bitmap Index Scan on edge_gist  (cost=0.00..7.36 rows=127 width=0)
 (actual time=2.519..2.519 rows=9 loops=1)
          Index Cond: (geom &&
 '0103000020A21000000100000005000000FC705E4CD73D15404AD7234690F24D40FC705E4CD73D15405870E3E178F54D40E01FF517366A15405870E3E178F54D40E01FF517366A15404AD7234690F24D40FC705E4CD73D15404AD7234690F24D40'::geometry)
  Planning Time: 4.996 ms
  Execution Time: 2.753 ms
 (8 rows)


 }}}

 I assume the bbox parameter could be based on the line added.

 I have done vacuum analyze a all the tables and removed all dead rows.

 I am running on a compiled postgis code from last week at


 {{{

 git rev-parse HEAD
 d5ba6b71cf08baaf67d9646a7ca52d0dfb5c3e9c

 git status
 HEAD detached at d5ba6b71c

 }}}


 Yes in this case I could use https://trac.osgeo.org/postgis/ticket/5667
 but in other cases we also need to add single rows.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5707>
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.