sql - 顺序扫描以 varchar_pattern_ops 索引的列

Question

我有一张用户表，它包含位置列。我使用 varchar_pattern_ops 索引了位置列。但是当我运行查询规划器时，它告诉我它正在进行顺序扫描。

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

我已经通过 stackoverflow 进行了搜索。发现大多数答案类似于“postgres 在大表中执行顺序扫描，以防索引扫描变慢”。但是我的 table 也不大。

我的 users 表中的索引是:

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

这是怎么回事？

Answer 1

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

WHERE lower(location) like '%nepa%'

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

按照此处的说明操作:

• PostgreSQL LIKE query performance variations

还有:

But my table is not big either.

你似乎有那个倒退。如果您的表不够足够大，Postgres 只按顺序读取它而不用索引可能会更快。您根本不会为此创建任何索引。临界点取决于许多因素。

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

(lower(location::text) varchar_pattern_ops)

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