使用varchar_pattern_ops索引的列的顺序扫描 [英] Sequential scan for column indexed with varchar_pattern_ops

问题描述

我有一个表USERS，它包含位置列。我已经使用varchar_pattern_ops对位置列进行了索引。但是当我运行query planner时，它告诉我它正在进行顺序扫描。

EXPLAIN ANALAYZE
SELECT * FROM USERS
WHERE lower(location) like '%nepa%'
ORDER BY location desc;

结果如下：

Sort  (cost=12.41..12.42 rows=1 width=451) (actual time=0.084..0.087 rows=8 loops=1)
Sort Key: location
Sort Method: quicksort  Memory: 27kB
  ->  Seq Scan on users  (cost=0.00..12.40 rows=1 width=451) (actual time=0.029..0.051 rows=8 loops=1)
      Filter: (lower((location)::text) ~~ '%nepa%'::text)
 Planning time: 0.211 ms
 Execution time: 0.147 ms

我已经在堆栈溢出中进行了搜索。发现大多数答案类似于"Postgres在大表中执行顺序扫描，以防索引扫描速度变慢"。但是我的桌子也不大。

我的users表中的索引是：

"index_users_on_lower_location_varchar_pattern_ops" btree (lower(location::text) varchar_pattern_ops)

发生了什么事？

推荐答案

*_patter_ops indexes适用于前缀匹配-LIKE模式锚定到开头，而不是前导通配符。但不适用于您的谓词：

WHERE lower(location) like '%nepa%'

我建议您改为创建三元组索引。而且您不需要在索引(或查询)中使用lower()，因为Trigram索引以几乎相同的成本支持不区分大小写的ILIKE(或~*)。

按照此处的说明操作：

• PostgreSQL LIKE query performance variations

同时：

但是我的桌子也不大。

您似乎把倒过来了。如果您的表不够大，Postgres只需按顺序读取它可能会更快，而不必费心使用索引。您根本不会为此创建任何索引。临界点取决于许多因素。

旁白：您的索引定义一开始就没有意义：

(lower(location::text) varchar_pattern_ops)

对于varchar列，请使用varchar_pattern_ops运算符类。
但如果强制转换为text，请使用text_pattern_ops。由于lower()即使对于varchar输入也会返回text，因此请使用text_pattern_ops。除了你可能不需要这个(或者任何？)完全没有索引，如建议的那样。

这篇关于使用varchar_pattern_ops索引的列的顺序扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！