为什么PostgreSQL在此查询中不使用索引 [英] Why postgresql does not use index in this query

问题描述

这是完整的表定义：

CREATE TABLE search.tablename
(
  id integer NOT NULL,
  name character varying(300) NOT NULL,
  CONSTRAINT tablename_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX tablename_name_idx
  ON search.tablename
  USING btree
  (name COLLATE pg_catalog."default");

它有大约73,000行，只是通过手动运行进行清理/分析。

It has ~73k rows and was just vacuumed/analyzed by manual run.

我不明白的是为什么这个琐碎的查询

What I cannot understand is why this trivial query

SELECT * 
  FROM "tablename" AS "arn" 
 WHERE arn.name LIKE 'foo%'

使用相应的索引。

此查询的执行计划是

Seq Scan on tablename arn  (cost=0.00..1626.96 rows=7 width=47) (actual time=8.682..8.682 rows=0 loops=1)
  Filter: ((name)::text ~~ 'foo%'::text)
  Rows Removed by Filter: 73197
Total runtime: 8.703 ms

有人可以指出我所缺少的内容吗？

Could anyone point to what I am missing?

UPD ：将列名称类型更改为文本不变。

UPD: changing column name type to text changes nothing.

UPD 2 ： name ='foo'谓词预期使用索引

推荐答案

尝试添加 varchar_pattern_ops 到您的索引

CREATE INDEX tablename_name_idx
  ON search.tablename
  USING btree
  (name COLLATE pg_catalog."default" varchar_pattern_ops);

运算符类text_pattern_ops，varchar_pattern_ops，
bpchar_pattern_ops和name_pattern_ops在
类型的文本，varchar，char和name上分别支持B树索引。与
默认操作符类的不同之处在于，将严格按字符对值进行比较，而不是根据特定于语言环境的
整理规则进行比较。当服务器不使用标准的 C语言环境时，这使这些运算符类适合包含模式匹配表达式（LIKE或POSIX常规
表达式）的
查询使用。

The operator classes text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types text, varchar, char, and name, respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the server does not use the standard "C" locale.

这篇关于为什么PostgreSQL在此查询中不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！