我有一张巨大的表 (约 4000 万行),名为 nearest_spot,表示线条 (以 linestring 格式表示) 和它们最近的地点 (存储在另一个表中有大约 1500 个不同的地点)。nearest_spot 表如下所示:
数据库是 PostgreSQL 10.6、PostGIS 2.5,link_geom 列有一项 gist 索引,并且已对 nearest_spot 表进行了 VACUUM ANALYZE。
我的目标是尽可能快地找到数据记录中最近的邻居 (在此表中)。
我已经知道如何找到最近的邻居,我的问题是找到它所需的时间。我对 PostgreSQL 和 PostGIS 还比较新,一直在阅读它们的文档,浏览很多关于 KNN 优化的话题,我一直在寻找最有效的答案,但即使只搜索一行,也无法在 5 分钟内得出结果 (有时甚至高达 30 分钟)。我尝试了以下不同的查询:
我之所以首先使用
我还参考了PostGIS的这篇有关空间索引的文档,以及这篇有关<->运算符的文档。 编辑:我意识到所有的坐标都是作为几何图形(SRID 4326)存储的,因此ST_DWithin调用虽然语法正确,但返回的所有行都不在20米范围内,而是在地球的20度范围内,所以实际上我的ST_DWithin没有缩小结果集的大小,这可能是导致耗时如此之长的最大原因之一,ST_Buffer也是如此。我将尝试在使用米之前将所有坐标转换为地理坐标(使用
data_id || spot_id || spot_name || link_geom其中 data_id 是主键,spot_id 是指向 spot 表主键的外键,spot_name 是地点名称 (我知道冗余不好,但我不能修改数据库),link_geom 是线的坐标。
数据库是 PostgreSQL 10.6、PostGIS 2.5,link_geom 列有一项 gist 索引,并且已对 nearest_spot 表进行了 VACUUM ANALYZE。
我的目标是尽可能快地找到数据记录中最近的邻居 (在此表中)。
我已经知道如何找到最近的邻居,我的问题是找到它所需的时间。我对 PostgreSQL 和 PostGIS 还比较新,一直在阅读它们的文档,浏览很多关于 KNN 优化的话题,我一直在寻找最有效的答案,但即使只搜索一行,也无法在 5 分钟内得出结果 (有时甚至高达 30 分钟)。我尝试了以下不同的查询:
SELECT *
FROM( SELECT A.position, B.spot_id
FROM data A, nearest_spot B
WHERE A.id = 1
AND ST_DWithin(A.position,B.link_geom,20)
ORDER BY A.position <-> B.link_geom
LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;
SELECT *
FROM( SELECT A.position, B.spot_id
FROM data A, nearest_spot B
WHERE A.id = 1
AND ST_Buffer(A.position,20) && B.link_geom
ORDER BY A.position <-> B.link_geom
LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;
SELECT *
FROM( SELECT A.position, B.spot_id
FROM data A, nearest_spot B
WHERE A.id = 1
AND ST_Intersects(ST_Buffer(A.position,20), B.link_geom)
ORDER BY A.position <-> B.link_geom
LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;
我之所以首先使用
<->
运算符,然后再使用 ST_Distance,是因为根据PostGIS的文档,<->
运算符速度更快但精度较低(对于边界框),而ST_Distance更准确但速度较慢。我还参考了PostGIS的这篇有关空间索引的文档,以及这篇有关<->运算符的文档。 编辑:我意识到所有的坐标都是作为几何图形(SRID 4326)存储的,因此ST_DWithin调用虽然语法正确,但返回的所有行都不在20米范围内,而是在地球的20度范围内,所以实际上我的ST_DWithin没有缩小结果集的大小,这可能是导致耗时如此之长的最大原因之一,ST_Buffer也是如此。我将尝试在使用米之前将所有坐标转换为地理坐标(使用
:: geography
),希望能看到改进。