[PostGIS] #6155: Slow getClosestEdge

PostGIS trac at osgeo.org
Wed Nov 4 08:21:08 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
Resolution:                     |   Keywords:
--------------------------------+---------------------------
Comment (by Lars Aksel Opsahl):

 This from from @GrayShade work very well


 {{{
 explain analyze
 SELECT *
 FROM (
     SELECT edge_id, geom
     FROM "arstat_2026_overlay_slow_185_185".edge_data
     ORDER BY geom <->
 ST_PointOnSurface('0103000020A210000001000000050000006E784AD6CAC426408E3A076D0D6F4F406E784AD6CAC42640C06269229DA54F4082B482DAFFD22840C06269229DA54F4082B482DAFFD228408E3A076D0D6F4F406E784AD6CAC426408E3A076D0D6F4F40')
 ASC
     FETCH FIRST 1 ROW WITH TIES
 ) AS nearest
 ORDER BY edge_id
 LIMIT 1;
 QUERY PLAN
 ---------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=1.19..1.20 rows=1 width=134) (actual time=0.272..0.273
 rows=1 loops=1)
    ->  Sort  (cost=1.19..1.20 rows=1 width=134) (actual time=0.271..0.272
 rows=1 loops=1)
          Sort Key: nearest.edge_id
          Sort Method: quicksort  Memory: 26kB
          ->  Subquery Scan on nearest  (cost=0.41..1.18 rows=1 width=134)
 (actual time=0.255..0.267 rows=1 loops=1)
                ->  Limit  (cost=0.41..1.17 rows=1 width=142) (actual
 time=0.255..0.266 rows=1 loops=1)
                      ->  Index Scan using edge_gist on edge_data
 (cost=0.41..693101.85 rows=907361 width=142) (actual time=0.254..0.264
 rows=2 loops=1)
                            Order By: (geom <->
 '0101000020A210000078966658E5CB2740A74EB847558A4F40'::geometry)
  Planning Time: 0.148 ms
  Execution Time: 0.330 ms
 (10 rows)

 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6155#comment:5>
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