[PostGIS] #6155: Slow getClosestEdge

PostGIS trac at osgeo.org
Wed Nov 4 07:33:21 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:
--------------------------------+---------------------------
Description changed by Lars Aksel Opsahl:

Old description:

> 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'

New description:

 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, edge_id ASC LIMIT 1
                                                                    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#comment:1>
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