PostgreSQL使用LIKE右模糊没有走索引分析&验证

建表&数据初始化可参考PostgreSQL 分区表——范围分区SQL实践

背景：

给t_common_work_order_log的handle_user_name新建索引后，使用LIKE右模糊匹配查询时，发现走的全表扫描

CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name);

EXPLAIN ANALYZE SELECT COUNT
  ( * ) 
FROM
  t_common_work_order_log 
WHERE
  handle_user_name LIKE'张秀%';

分析：

由于handle_user_name已经建了索引，查询资料发现B-tree索引需要特定的运算符类别（如text_pattern_ops 或 varchar_pattern_ops ）才能让LIKE右模糊生效

PostgreSQL 索引运算符类：text_ops 与 text_pattern_ops 的区别

在 PostgreSQL 中，text_ops 和 text_pattern_ops 是两种不同的运算符类(operator class)，它们决定了索引如何支持不同类型的文本比较操作。

text_ops (默认运算符类)

• 使用数据库的默认排序规则(LC_COLLATE)
• 支持所有标准的文本比较操作(=, <, >, <=, >=)
• 适用于常规的相等性检查和排序操作
• 对于使用 LIKE 或正则表达式等模式匹配操作的查询效率较低

text_pattern_ops

• 忽略语言环境特定的排序规则，使用简单的逐字符比较
• 专门优化了以 LIKE 或 ~ 开头的模式匹配查询
• 特别适合前缀搜索(如 column LIKE 'abc%')
• 不支持常规的 <, > 等比较操作
• 不适用于需要遵循语言特定排序规则的查询

使用场景示例

-- 使用默认的 text_ops (适合常规比较)
CREATE INDEX idx_name ON users (name);

-- 使用 text_pattern_ops (适合模式匹配)
CREATE INDEX idx_name_pattern ON users (name text_pattern_ops);

注意事项

1. 如果查询混合了常规比较和模式匹配，可能需要创建两个索引
2. text_pattern_ops 索引对于 LIKE '%suffix' 这样的后缀搜索没有帮助
3. 对于不区分大小写的模式匹配，考虑使用 citext 类型或表达式索引

选择哪种运算符类取决于具体查询模式。如果主要进行前缀搜索或模式匹配，text_pattern_ops 会提供更好的性能。

确认指定索引的运算符类别

relname输入实际的索引名称，通过查询结果可知当前的handle_user_name索引的运算符类别为默认的text_ops

SELECT
    i.relname AS index_name,
    a.attname AS column_name,
    opc.opcname AS operator_class
FROM
    pg_index x
    JOIN pg_class i ON i.oid = x.indexrelid
    JOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = ANY(x.indkey)
    JOIN pg_opclass opc ON opc.oid = ANY(x.indclass)
WHERE
    i.relname = 'order_log_handle_user_name_index';

修改运算符类别为text_pattern_ops

-- 删除旧索引
DROP INDEX IF EXISTS order_log_handle_user_name_index;

-- 创建新索引(使用text_pattern_ops)
CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name text_pattern_ops);

回退sql

-- 删除旧索引
DROP INDEX IF EXISTS order_log_handle_user_name_index;

-- 创建新索引(使用text_pattern_ops)
CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name text_ops);

验证

EXPLAIN ANALYZE验证

修改运算符类别为text_pattern_ops再次执行EXPLAIN ANALYZE，可知LIKE右模糊查询索引生效

查询速度对比

计算方法：查询10次，去掉最大和最小取平均值

默认运算符类别 3.585s

-- 3.510s 3.722s 3.485s 3.732s 3.478s 3.558s 3.729s 3.511s 3.599s 3.564s
SELECT *
FROM
  t_common_work_order_log 
WHERE
  handle_user_name LIKE'张秀%';

text_pattern_ops运算符类别 2.116s

-- 1.753s 2.296s 2.102s 2.159s 2.167s 2.055s 2.048s 2.169s 2.334s 1.934s
SELECT *
FROM
  t_common_work_order_log 
WHERE
  handle_user_name LIKE'张秀%';