[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