为什么PostgreSQL在此查询中不使用索引

本文介绍了为什么PostgreSQL在此查询中不使用索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是完整的表定义：

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.