有限制的慢速顺序

Question

我有一个疑问：

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount

我对此很满意：

"Sort  (cost=3842.56..3847.12 rows=1826 width=123) (actual time=1.915..2.084 rows=1307 loops=1)"
"  Sort Key: displaycount"
"  Sort Method: quicksort  Memory: 206kB"
"  ->  Bitmap Heap Scan on location  (cost=34.40..3743.64 rows=1826 width=123) (actual time=0.788..1.208 rows=1307 loops=1)"
"        Recheck Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"        ->  Bitmap Index Scan on location_lower_idx  (cost=0.00..33.95 rows=1826 width=0) (actual time=0.760..0.760 rows=1307 loops=1)"
"              Index Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2.412 ms"

但是，当我添加限制时，执行时间超过2秒：

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount 
limit 20

解释：

"Limit  (cost=0.00..1167.59 rows=20 width=123) (actual time=2775.452..2775.643 rows=20 loops=1)"
"  ->  Index Scan using location_displaycount_index on location  (cost=0.00..106601.25 rows=1826 width=123) (actual time=2775.448..2775.637 rows=20 loops=1)"
"        Filter: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2775.693 ms"

我认为这是一个有条不紊的问题。如何强制PostgreSQL使用索引并在末尾进行排序？

Subquery没有帮助：

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
    order by displaycount
) t 
LIMIT 20;

或者：

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
) t 
order by displaycount 
LIMIT 20;

Answer 1

我的猜测是，这将修复您的查询：

SELECT * 
FROM   location 
WHERE     to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
ORDER  BY to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) DESC
         ,displaycount 
LIMIT  20;

我将WHERE条件作为ORDER BY子句的第一个元素重复--这在逻辑上是多余的，但应该避免查询计划器假设按照索引location_displaycount_index处理行会更好--结果证明这要昂贵得多。

根本的问题是，查询规划器显然严重错误地判断了WHERE条件的选择性和/或成本。我只能猜测这是为什么。

您是否正在运行自真空 --哪个也应该负责在您的表上运行ANALYZE？因此，你的表统计数据是最新的吗？如果您运行的话，任何效果：

ANALYZE location;

然后再试一次？

也可能是@@运算符的选择性被误判了。我可以想象，基于逻辑原因，估计是非常困难的。

如果我的查询不应该解决问题，并且通常要验证基本理论，那么做以下两件事之一：

• 临时删除索引location_displaycount_index
• 临时运行禁用基本索引扫描：设置enable_indexscan = OFF；

后者的侵扰性较低，只影响当前会话。它使bitmap heap scan和bitmap index scan方法处于打开状态，这些方法被快速计划所使用。

然后重新运行查询。

顺便说一句:如果这个理论是正确的，那么你的查询(就像你现在所做的那样)会更快，在FTS条件下，搜索词的选择性会更低--与你可能预期的相反。试试看。

Answer 2

当使用极限postgresql调整时，它的计划是最优的，只检索行的子集。不幸的是，在你的情况下，它做出了错误的选择。这可能是因为表的统计数据太旧了。尝试通过发布真空分析位置来更新统计；

强制使用索引通常是通过禁止使用顺序扫描(set enable_seqscan=false)来完成的。但是，在您的示例中，它并不是在执行顺序扫描，它只是切换到一个不同的索引，用于有限制的查询。

如果分析没有帮助，您能分辨您使用的postgresql的哪个版本吗？表中还有多少行？