一个内网项目,需要获取到距离 GPS 坐标最近的两条路,道路 polyline 保存在 PostgresSQL+PostGIS 数据库里。现在我的查询语句是这样的形式:
SELECT
name,
st_distance (
ST_GeomFromText ('POINT(108.862531 34.288909)') :: geography,
geom :: geography
) as distance,
id,
st_asgeojson(geom)
FROM
road1_polyline
WHERE name is not null
UNION
SELECT
name,
st_distance (
ST_GeomFromText ('POINT(108.862531 34.288909)') :: geography,
geom :: geography
) as distance ,
id,
st_asgeojson(geom)
FROM
road2_polyline
WHERE name is not null
ORDER BY distance ASC
LIMIT 100
总的数据记录数大概在 6w,执行时间 4-8s,explain 的输出如下:
Limit (cost=155093.26..155093.51 rows=100 width=358)
-> Sort (cost=155093.26..155226.28 rows=53207 width=358)
Sort Key: (_st_distance('0101000020E6100000513239B533375B40914259F8FA244140'::geography, (national_highway_polyline_clip.geom)::geography, '0'::double precision, true))
-> HashAggregate (cost=152527.66..153059.73 rows=53207 width=358)
Group Key: national_highway_polyline_clip.name, (_st_distance('0101000020E6100000513239B533375B40914259F8FA244140'::geography, (national_highway_polyline_clip.geom)::geography, '0'::double precision, true)), national_highway_polyline_clip.id, (st_asgeojson(national_highway_polyline_clip.geom, 15, 0))
-> Append (cost=0.00..151995.59 rows=53207 width=358)
-> Seq Scan on national_highway_polyline_clip (cost=0.00..6681.75 rows=2381 width=54)
Filter: (name IS NOT NULL)
-> Seq Scan on country_road_polyline_clip (cost=0.00..18560.35 rows=6620 width=58)
Filter: (name IS NOT NULL)
-> Seq Scan on city_fast_road_polyline_clip (cost=0.00..508.69 rows=180 width=68)
Filter: (name IS NOT NULL)
-> Seq Scan on highway_polyline_clip (cost=0.00..7240.14 rows=2566 width=70)
Filter: (name IS NOT NULL)
-> Seq Scan on level_nine_road_polyline_clip (cost=0.00..6616.24 rows=2362 width=59)
Filter: (name IS NOT NULL)
-> Seq Scan on other_road2_polyline_clip (cost=0.00..16308.71 rows=5172 width=59)
Filter: (name IS NOT NULL)
-> Seq Scan on provincial_highway_polyline_clip (cost=0.00..13497.61 rows=4811 width=59)
Filter: (name IS NOT NULL)
-> Seq Scan on sub_country_road2_polyline_clip (cost=0.00..82050.03 rows=29115 width=59)
Filter: (name IS NOT NULL)
求教如何优化?
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.