[PostGIS] #6155: Slow getClosestEdge

PostGIS trac at osgeo.org
Wed Nov 4 06:37:19 PST 2026


#6155: Slow getClosestEdge
-------------------------------+---------------------------
 Reporter:  Lars Aksel Opsahl  |      Owner:  strk
     Type:  defect             |     Status:  new
 Priority:  medium             |  Milestone:  PostGIS 3.7.1
Component:  topology           |    Version:  3.6.x
 Keywords:                     |
-------------------------------+---------------------------
 When I run analyze I found this issue

 {{{
 -[ RECORD 8
 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
 calls     | 3396
 total_ms  | 2531886.37
 mean_ms   | 745.55
 substring | SELECT edge_id,start_node,end_node,left_face,right_face,geom
 FROM "arstat_2026_overlay_slow_185_185".edge_data ORDER BY geom <-> $1
 ASC, edge_id ASC LIMIT 1

 }}}

 So I did a simple test with a query like this below

 {{{
 explain analyze SELECT
 edge_id,start_node,end_node,left_face,right_face,geom
 FROM "arstat_2026_overlay_slow_185_185".edge_data
 ORDER BY geom <->
 ST_PointOnSurface('0103000020A210000001000000050000006E784AD6CAC426408E3A076D0D6F4F406E784AD6CAC42640C06269229DA54F4082B482DAFFD22840C06269229DA54F4082B482DAFFD228408E3A076D0D6F4F406E784AD6CAC426408E3A076D0D6F4F40')
 ASC LIMIT 1
 [more] - > ;
                                                                    QUERY
 PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=280127.65..280127.76 rows=1 width=159) (actual
 time=334.671..369.921 rows=1 loops=1)
    ->  Gather Merge  (cost=280127.65..368475.68 rows=757216 width=159)
 (actual time=332.114..367.362 rows=1 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          ->  Sort  (cost=279127.62..280074.14 rows=378608 width=159)
 (actual time=305.781..305.782 rows=1 loops=3)
                Sort Key: ((geom <->
 '0101000020A210000078966658E5CB2740A74EB847558A4F40'::geometry)), edge_id
                Sort Method: top-N heapsort  Memory: 27kB
                Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
                Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
                ->  Parallel Seq Scan on edge_data  (cost=0.00..277234.58
 rows=378608 width=159) (actual time=2.612..240.422 rows=303150 loops=3)
  Planning Time: 0.165 ms
  JIT:
    Functions: 7
    Options: Inlining false, Optimization false, Expressions true,
 Deforming true
    Timing: Generation 0.825 ms, Inlining 0.000 ms, Optimization 0.664 ms,
 Emission 9.579 ms, Total 11.069 ms
  Execution Time: 370.278 ms
 (16 rows)

 }}}

 We here that no indexes are used her. So then I tried to remove order by
 edge_id and then we got a speed up by 1600 times for the same query.

 {{{
 explain analyze SELECT
 edge_id,start_node,end_node,left_face,right_face,geom
 FROM "arstat_2026_overlay_slow_185_185".edge_data
 ORDER BY geom <->
 ST_PointOnSurface('0103000020A210000001000000050000006E784AD6CAC426408E3A076D0D6F4F406E784AD6CAC42640C06269229DA54F4082B482DAFFD22840C06269229DA54F4082B482DAFFD228408E3A076D0D6F4F406E784AD6CAC426408E3A076D0D6F4F40')
 ASC LIMIT 1
 [more] - > ;
                                                               QUERY PLAN
 ---------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.41..1.16 rows=1 width=159) (actual time=0.196..0.197
 rows=1 loops=1)
    ->  Index Scan using edge_gist on edge_data  (cost=0.41..677985.46
 rows=908660 width=159) (actual time=0.195..0.195 rows=1 loops=1)
          Order By: (geom <->
 '0101000020A210000078966658E5CB2740A74EB847558A4F40'::geometry)
  Planning Time: 0.152 ms
  Execution Time: 0.220 ms
 (5 rows)

 }}}

 I have now tested attached patch on latest Postgis. The Postgis Topology
 testes are running through .

 When running with this patch for a topology job, the time for adding lines
 crossing cells borders goes down from '1 hour, 43 minutes, 37.24 seconds'
 to '24 minutes, 0.85 seconds'
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6155>
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.


More information about the postgis-tickets mailing list