[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