当前位置：首页 > news >正文

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

news 来源：原创 2025/6/26 7:40:50

建表&数据初始化可参考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( * ) 
FROMt_common_work_order_log 
WHEREhandle_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);

注意事项

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

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

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

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

SELECTi.relname AS index_name,a.attname AS column_name,opc.opcname AS operator_class
FROMpg_index xJOIN pg_class i ON i.oid = x.indexrelidJOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = ANY(x.indkey)JOIN pg_opclass opc ON opc.oid = ANY(x.indclass)
WHEREi.relname = 'order_log_handle_user_name_index';

index_name	column_name	operator_class
order_log_handle_user_name_index	handle_user_name	text_ops

修改运算符类别为`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 *
FROMt_common_work_order_log 
WHEREhandle_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 *
FROMt_common_work_order_log 
WHEREhandle_user_name LIKE'张秀%';

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

建表&数据初始化可参考PostgreSQL 分区表——范围分区SQL实践背景： 给t_common_work_order_log的handle_user_name新建索引后，使用LIKE右模糊匹配查询时，发现走的全表扫描 CREATE INDEX order_log_handle_user_name_index ON t_commo…...

编程日记 2025/6/26 7:40:50

Jenkins流水线管理工具

文章目录前言： DevOps时代的自动化核心 —Jenkins一、Jenkins是什么？二、Linux安装Jenkinswar包方式安装依赖环境下载 Jenkins WAR 包启动 Jenkins 服务启动日志验证配置插件镜像源 docker镜像方式安装依赖环境拉取 Jenkins 镜像运行 Jenkins 容器获取初…...

编程日记 2025/5/26 4:31:12

2025年保安员证考试题库及答案

一、单选题 96、手指出血，为达到止血作用，应该压住（）。 A.出血手指的尖端 B.出血手指根部前后两侧 C.出血手指根部左右两侧 D.腕部的桡动脉、尺动脉答案：C 97、下列选项中对干粉灭火器使用方法叙述错误的是&…...

编程日记 2025/6/23 22:13:56

观测云数据在Grafana展示的最佳实践

背景在当今的数据驱动世界中，组织越来越依赖于实时数据来做出决策。数据可视化是理解和分析这些数据的关键工具，它帮助用户将复杂的数据集转换成直观的图表和仪表板，从而更容易识别趋势、模式和异常。Grafana，作为一个功能强大的…...

编程日记 2025/6/22 2:08:03

点云从入门到精通技术详解100篇-基于二次误差和高斯混合模型的点云配准算法

目录知识储备结合二次误差度量与高斯混合模型的点云配准算法核心创新点：关键参数说明：性能优化建议：前言国内外研究现状全局配准算法的国内外研究局部配准算法的国内外研究 2 点云配准相关概念与方法 2.1 什么是点云配准 2.2 点云的获取及点云主要数据…...

编程日记 2025/6/21 5:42:31

shell命令一

&> /dev/null yum -y install vsftpd &> /dev/null&> /dev/null &>：将命令的**标准输出（stdout）和标准错误（stderr）**同时重定向。/dev/null：Linux中的“黑洞”设备&#xf…...

编程日记 2025/6/21 8:46:13

1. 索引优化创建合适的索引 -- 为常用查询条件创建索引 ALTER TABLE users ADD INDEX idx_email (email); ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);避免索引失效的情况 -- 避免在索引列上使用函数 SELECT * FROM users WHERE DATE(crea…...

编程日记 2025/6/26 7:11:00

在 Spring Boot 中实现 WebSockets

什么是 WebSockets？ WebSockets 是一种基于 TCP 的全双工通信协议，允许客户端和服务器之间建立持久的双向连接，用于实时数据交换。相较于传统的 HTTP 请求-响应模型，WebSockets 提供了低延迟、高效率的通信方式，特别适…...

编程日记 2025/6/26 6:15:08

stone 3d v3.3.0版本发布，含时间线和连接器等新功能

1.新加了时间线（timeline）编辑器，可以类似blender一样给对象制作动画 2.新加了度量（metrics）系统，通过scene对象检测器中的useMetrics属性来启用或禁用，启用时所选物体将显示三维度量数据新加了…...

编程日记 2025/6/24 4:53:57

Parasoft C++Test软件单元测试_对函数打桩的详细介绍

系列文章目录 Parasoft C++Test软件静态分析：操作指南（编码规范、质量度量）、常见问题及处理 Parasoft C++Test软件单元测试：操作指南、实例讲解、常见问题及处理 Parasoft C++Test软件集成测试：操作指南、实例讲解、常见问题及处理进阶扩展：自动生成静态分析文档、自动…...

编程日记 2025/6/21 11:27:56

Safety Estimands与Efficacy Estimands的差异剖析

1. 研究目标差异 1.1 安全性估计目标 1.1.1 关注潜在风险安全性估计目标着重于治疗可能引发的不良事件（AE）、严重不良事件（SAE）或实验室指标异常，如化疗药物导致中性粒细胞减少症的发生率，这些指标直接关联到患者治疗过程中的健康风险。这些潜在风险的评估对于确保治…...

编程日记 2025/6/23 13:49:31

HTML 详解：从基础结构到语义标签

目录一、HTML 是什么？二、HTML 的基本结构✅ 简要说明： 三、常见 HTML 标签讲解3.1 标题标签 <h1> ~ <h6>3.2 段落和换行3.3 超链接3.4 图像插入3.5 列表无序列表：有序列表： 3.6 表格结构四、HTML 语义化标签详解五…...

编程日记 2025/6/25 17:49:11

联合索引`ABC`，使用`B=... AND C=... AND A=...`会走索引吗？

在MySQL中，联合索引ABC的查询使用B... AND C... AND A...时，是否使用索引取决于查询条件的顺序和优化器的处理。一、索引使用原理最左前缀原则联合索引的底层存储和查询优化遵循最左前缀匹配原则，即查询条件必须从索引的最左侧列开始连续匹…...

编程日记 2025/6/26 6:53:34

HTML 模板技术与服务端渲染

HTML 模板技术与服务端渲染引言在现代前端开发生态中，HTML模板技术与服务端渲染(SSR)构成了连接前后端的重要桥梁。当单页应用(SPA)因其客户端渲染特性而面临首屏加载速度慢、白屏时间长和SEO不友好等问题时，服务端渲染技术提供了一种优雅的解决方案…...

编程日记 2025/5/29 18:57:06

MySQL的MVCC【学习笔记】

MVCC 事务的隔离级别分为四种，其中Read Committed和Repeatable Read隔离级别，部分实现就是通过MVCC（Multi-Version Concurrency Control，多版本并发控制） 版本链版本链是通过undo日志实现的， 事务每次修改…...

编程日记 2025/6/25 9:49:25

linux安装单节点Elasticsearch（es），安装可视化工具kibana

真的，我安装个es和kibana，找了好多帖子，问了好几遍ai才安装成功，在这里记录一下，我相信，跟着我的步骤走，99%会成功； 为了让大家直观的看到安装过程，我把我服务器的es和ki…...

编程日记 2025/6/25 10:42:29

（Go Gin）上手Go Gin 基于Go语言开发的Web框架，本文介绍了各种路由的配置信息；包含各场景下请求参数的基本传入接收

1. 路由 gin 框架中采用的路优酷是基于httprouter做的 HttpRouter 是一个高性能的 HTTP 请求路由器，适用于 Go 语言。它的设计目标是提供高效的路由匹配和低内存占用，特别适合需要高性能和简单路由的应用场景。主要特点显式匹配：与其他路由…...

编程日记 2025/6/25 16:47:18

纯HTMLCSS静态网站——元神

《原神》主题网页介绍以对该网页的详细介绍网页整体结构头部（header）：包含网站的 logo 和导航栏。logo 部分展示了 “原神” 字样，点击可返回首页。导航栏提供了多个页面链接，包括首页、音乐、视频、壁纸、世界、…...

编程日记 2025/6/26 0:00:51

嵌入式开发：基础知识介绍

一、嵌入式系统 1、介绍以提高对象体系智能性、控制力和人机交互能力为目的，通过相互作用和内在指标评价的，嵌入到对象体系中的专用计算机系统。 2、分类按其形态的差异，一般可将嵌入式系统分为：芯片级（MCU、SoC&am…...

编程日记 2025/5/7 20:56:40

华为VRP系统简介配置TELNET远程登录!

1.华为 VRP 系统概述 1.1 什么是 VRP VRP（Versatile Routing Platform 华为数通设备操作系统）是华为公司数据通信产品的通用操作系统平台，从低端到核心的全系列路由器、以太网交换机、业务网关等产品的软件核心引擎。 1.2 VRP 的功能统一…...

编程日记 2025/6/24 11:15:07

【高频考点精讲】CSS accent-color属性：如何快速自定义表单控件的颜色？

用CSS accent-color属性3分钟搞定表单控件换肤，原来这么简单！前几天有个学员问我，checkbox和radio这些表单控件默认样式太丑了，有没有什么办法能快速改颜色？" 我一看这问题就乐了——这不正是CSS accent-color属性的拿手好戏吗？今天咱们就来好好聊聊这个被低估的C…...

编程日记 2025/6/22 9:38:14

【Git】连接github时的疑难杂症（DNS解析失败）

大家好，我是jstart千语。最近在将项目推送到github的时候，突然github就拒绝访问了，即使挂了VPN，网页也进不去，通过git也不能把代码推送上去。即使后面看别人的一些解决方案，比如取消代理啊、更换ssh的方式…...

编程日记 2025/6/20 19:25:47

成熟的前端vue vite websocket,Django后端实现方案包含主动断开websocket连接的实现

可参考实现方式点击进入查看具体实现方案如下所示： import { useWebsocketMsessageStore } from /stores/websocketMsessageStore.js import {ElMessage} from "element-plus"; import {useUserStore} from "/stores/userStore.js"; // impo…...

编程日记 2025/6/25 15:52:17

广州 3D 展厅开启企业展示新时代

为了突破传统展厅的局限，满足企业日益增长的展示需求，3D 展厅应运而生。3D 展厅是利用虚拟现实（VR）、增强现实（AR）和三维建模等先进技术，构建出的一个高度逼真的数字化展示空间。它打破了传统展…...

编程日记 2025/6/23 13:03:56

【Django】新增字段后兼容旧接口 This field is required

背景我在Django模型里新增了两个字段后，旧的接口由于没有同时新增这两个字段的处理，因此旧的接口就报： This field is required 解决把序列化时的 required 的字段设置为False即可 class ServiceSerializer(DynamicFieldsModelSerializ…...

编程日记 2025/6/22 5:46:04

基于AIGC的3D场景生成实战：从文本描述到虚拟世界构建

一、3D AIGC技术解析 1.1 技术挑战与突破挑战维度传统方案局限 AIGC创新方案建模效率人工建模耗时数天文本到3D秒级生成细节丰富度重复使用素材库无限风格化生成物理合理性手动调整物理参数自动符合物理规律多平台适配需手动优化模型自适应LOD生成 1.2 主流技术路…...

编程日记 2025/6/26 1:24:45

手写Java线程池与定时器：彻底掌握多线程任务调度

目录一、线程池 1.1、什么是线程池 1.2、Java标准库中的线程池 1.3、ThreadPoolExecutor的七大参数 1.4、模拟实现线程池 1.4.1、submit () 1.4.2、构造方法 1.4.3、运行结果二、定时器 2.1、标准库中的定时器 2.2、模拟实现定时器 2.2.1、MyTimerTask类 2…...

编程日记 2025/6/25 9:31:14

【科研绘图系列】R语言绘制区间点图（dot plot）

禁止商业或二改转载，仅供自学使用，侵权必究，如需截取部分内容请后台联系作者! 文章目录介绍加载R包数据下载导入数据数据预处理画图1画图2输出图片系统信息介绍【科研绘图系列】R语言绘制区间点图（dot plot）加载R包 library(tidyverse) library(ggtext) library(r…...

编程日记 2025/6/25 18:54:46

【Agent实战】从0到1开发一个Python 解释器 MCP SSE Server

写在前面想象一个场景：LLM Agent（如 AutoGPT、MetaGPT 或我们自己构建的 Agent）在规划任务后，决定需要运行一段 Python 代码来处理数据或调用某个 API。它不能直接在自己的环境中执行（通常不具备这个能力，也不安全），而是需要将这段代码发送给一个专门的外部服务来执行…...

编程日记 2025/6/26 1:12:53

C语言大写转小写2.0

一、阐述关系上一次的题目是大写转小写,这一次代码不一样,运行的结果也不一样,这次的代码在此基础之上改动了一下,虽然看起来相似,但实际上运行结果不一样二、题目展示下面代码中,运行的结果是多少? 三、分析过程首先,进入main函数,先声明了一个字符型数组是"012…...

编程日记 2025/5/27 6:18:54

pnpm常见报错解决办法

PS D:\code\gitlab\manus-web> pnpm add -D types/framer-motion --save-exact --config.strict-peer-depsfalse Debugger attached. WARN deprecated eslint8.57.1: This version is no longer supported. Please see https://eslint.org/version-support for other op…...