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

Question

这是我的查询:

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:

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

我注意到，如果我将 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_DWithin 调用替换为 ST_Distance 调用，不是吗？

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

我认为这可能是 postgres 的查询规划器搞砸了的问题？比如，出于某种原因它认为它需要扫描整个表，尽管 ORDER BY x LIMIT 5 子句是最前面和最中间的？不确定..

Answer 1

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

由于 ST_DWithin 使用空间索引，规划器(错误地)认为使用它首先过滤掉行会更快。然后它必须对(几乎)所有行进行排序，最后将保留前 5 行。

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