时间戳

首页 > 解决方案 > 按地理距离过滤的简单 SQL 查询非常慢

sql - 按地理距离过滤的简单 SQL 查询非常慢

问题描述

这是我的查询：

SELECT 1 
FROM post po
WHERE ST_DWithin(po.geog, (SELECT geog FROM person WHERE person.person_id = $1), 20000 * 1609.34, false)
ORDER BY post_id DESC
LIMIT 5;

这是EXPLAIN ANALYZE：

在此处输入图像描述

我对所有内容都有索引，所以我不确定为什么这很慢。按满足子句排序时的前 5 个帖子post_id DESC，那么这不应该立即返回吗？

我注意到，如果我将 ST_DWithin 调用替换为 ST_Distance 调用，它会立即运行，如下所示：

SELECT 1 
FROM post po
WHERE ST_Distance(po.geog, (SELECT geog FROM person WHERE person.person_id = $1)) < 20000 * 1609.34
ORDER BY post_id DESC
LIMIT 5;

那一个以.15毫秒为单位运行。所以，简单的解决方案就是用 ST_Distance 调用替换 ST_DWithin 调用，不是吗？

好吧，不幸的是不是，因为它并不总是匹配的前 5 行。有时它必须深入扫描表，所以此时 ST_DWithin 更好，因为它可以使用地理索引，而 ST_Distance 不能。

我认为这可能是 postgres 的查询计划器搞砸的问题？就像，出于某种原因，它认为它需要对整个表进行扫描，尽管该ORDER BY x LIMIT 5子句位于前面和中心？没有把握..

标签： sqlpostgresqlperformanceoptimizationpostgis

解决方案

您使用的距离几乎是赤道的长度，因此您可以期望（几乎）所有结果都满足此条款。

由于ST_DWithin使用了空间索引，规划器（错误地）认为使用它首先过滤掉行会更快。然后它必须订购（几乎）所有行，最后将保留前 5 行。

使用 时st_distance，不能使用空间索引，规划者将选择不同的计划，可能依赖于 上的索引post_id，这非常快。但是当要返回的行数 (the limit) 增加时，将使用不同的计划，并且计划者可能认为计算所有行的距离会再次更快。

推荐阅读