在上一篇文章中我们通过分区表实现了“分而治之”——查询只扫描目标分区旧数据用 DETACH 而非 DELETE 来清理。但分区解决的是数据规模问题而不是搜索质量问题。当用户输入“数据库性能”时如果底层用的是 LIKE ‘%数据库性能%’数据库只能全表扫描即使命中了“数据库系统与性能优化”这样的文本LIKE 也无法理解“数据库”和“性能”是关键词更无法按相关性排序。 PostgreSQL 的原生全文检索正是为解决这个问题而设计的——它把自然语言文本转化为可索引的 tsvector把查询转化为 tsquery通过 操作符判断匹配由 GIN 索引加速候选集合最终用 ts_rank 给出相关性排序。 本文从 LIKE 的局限讲起深入拆解 tsvector/tsquery 的匹配机制、GIN 索引的加速原理以及中文场景下 zhparser 与 pg_jieba 的选型与实战。一、为什么 LIKE 不够用全文检索要解决的问题不是“字符串包含”而是“面向自然语言的可维护、可索引、可排序的文档检索表示”。LIKE、ILIKE、正则作为搜索方案有三个结构性短板全文检索并非一定要使用索引但没有索引的全文检索在生产环境中几乎不可用——每一次查询都是全表扫描还要对每一行做解析、分词和匹配。二、tsvector 与 tsquery把文本和查询变成“可索引的形式”2.1 文档表示层tsvectortsvector 是 PostgreSQL 内置的全文检索类型用来保存预处理后的文档。它的预处理拆成三步parser 把文本切成 tokendictionary 把 token 归一化成 lexeme 并过滤停用词结果保存为 tsvector。一个典型的 tsvector 值长这样SELECTto_tsvector(english,A fat cat sat on a mat and ate fat rats);-- ate:9 cat:3 fat:2,11 mat:7 rat:12 sat:4这里有三个容易混淆的概念tokenparser 从原文切出来的片段。lexemedictionary 归一化后的检索词。fat 和 rats 会被归一化为 fat 和 rat因此搜索 friend 也能匹配到 friends 和 friendly。tsvector保存去重、排序后的 lexeme可选保存位置和权重。一个关键理解tsvector 本身不“理解语言”语言规则来自 text search configuration。to_tsvector(‘english’, …) 和 to_tsvector(‘simple’, …) 会产生不同的分词和归一化结果。2.2 查询表示层tsquerytsquery 表达查询条件支持以下操作符 操作符判断 tsvector 是否匹配 tsquerySELECTto_tsvector(english,PostgreSQL database performance tuning) to_tsquery(english,database performance);-- true两个实用的查询构造函数plainto_tsquery() 将用户输入的自然语言文本转换为 tsquery忽略操作符适合“搜索框”场景。phraseto_tsquery() 将自然语言文本转换为短语查询词序必须匹配。三、GIN 索引让全文检索从全表扫描变成索引扫描没有索引的全文检索每一次查询都需要对每一行执行 to_tsvector 并比较。GIN 是全文检索的标准索引类型官方文档明确说明了两种可用于加速全文检索的索引GIN 和 GiST而 GIN 是默认推荐。3.1 表达式索引 vs 存储生成列有两种方式让 GIN 索引覆盖全文检索方式一表达式索引CREATEINDEXpgweb_idxONpgwebUSINGGIN(to_tsvector(english,body));一个极其重要的细节索引中必须使用 两参数版本的 to_tsvector显式指定配置名。因为索引内容必须不受 default_text_search_config 变化的影响——否则同一个索引中可能混入不同配置产生的 tsvector导致索引不一致甚至无法正确 dump 和 restore。查询时必须匹配索引的表达式。WHERE to_tsvector(‘english’, body) ‘a b’ 可以走索引但 WHERE to_tsvector(body) ‘a b’ 不能——因为后者使用的是默认配置可能和索引的配置不一致。方式二存储生成列推荐ALTERTABLEpostsADDCOLUMNsearch_vector tsvector GENERATED ALWAYSAS(to_tsvector(english,coalesce(title,)|| ||coalesce(content,)))STORED;CREATEINDEXposts_search_idxONpostsUSINGGIN(search_vector);生成列自动维护——插入和更新时重新分词查询时零开销。这比在每个查询中手动调用 to_tsvector 更可靠也避免了“查询表达式与索引表达式不匹配”的陷阱。3.2 查询与相关性排序SELECTtitle,ts_rank(search_vector,query)ASrankFROMposts,plainto_tsquery(english,database performance)queryWHEREsearch_vector queryORDERBYrankDESCLIMIT20;ts_rank 计算相关性评分ts_headline 生成带高亮标记的摘要片段。性能要点GIN 索引是前提生成列用 STORED大结果集配合 LIMIT 和分页rank 计算只发生在命中集上写入极重的表要注意 GIN 索引的写放大可以用 fastupdate 延迟合并来平衡。四、中文分词PostgreSQL 原生不支持需要扩展PostgreSQL 内置的分词器按空格切词对中文无效——一整段中文文本会被当成一个词。中文全文检索必须依赖分词扩展。4.1 两大主流方案zhparser vs pg_jieba选型建议新项目优先 pg_jieba因为它在中文分词准确性和标点鲁棒性上表现更好新词识别能力更强。zhparser 的优势在于 SCWS 引擎的成熟度和更丰富的分词模式配置适合已经使用 SCWS 生态的场景。4.2 中文全文检索的完整配置流程以 zhparser 为例展示从安装到可用的完整链路-- 1. 创建扩展CREATEEXTENSION zhparser;-- 2. 创建使用 zhparser 作为解析器的全文搜索配置CREATETEXTSEARCH CONFIGURATION chinese(PARSERzhparser);-- 3. 为分词结果添加映射n名词、v动词、a形容词等映射到 simple 字典ALTERTEXTSEARCH CONFIGURATION chineseADDMAPPINGFORn,v,a,i,e,lWITHsimple;-- 4. 验证分词效果SELECTto_tsvector(chinese,PostgreSQL数据库性能优化);– 期望输出‘postgresql’:1 ‘优化’:4 ‘性能’:3 ‘数据库’:2创建中文全文检索的存储生成列和 GIN 索引ALTERTABLEarticlesADDCOLUMNsearch_vector tsvector GENERATED ALWAYSAS(to_tsvector(chinese,coalesce(title,)|| ||coalesce(content,)))STORED;CREATEINDEXarticles_search_idxONarticlesUSINGGIN(search_vector);查询SELECTtitle,ts_rank(search_vector,query)ASrankFROMarticles,plainto_tsquery(chinese,数据库 性能)queryWHEREsearch_vector queryORDERBYrankDESCLIMIT20;4.3 中文分词的三个关键坑点坑点一索引配置必须与查询配置一致。 如果索引使用 chinese 配置查询也必须使用 chinese。配置不一致会导致索引不被使用退化为全表扫描。坑点二自定义词典的维护。 领域专有名词如产品名、内部术语默认分词器往往切不出来需要通过自定义词典补充。zhparser 通过 zhparser.extra_dicts 配置 txt 词典pg_jieba 也支持自定义词典。词典需要定期维护否则新出现的业务术语会分词失败。坑点三云托管环境的扩展限制。 很多托管 PostgreSQL 服务如 RDS不支持自定义扩展。如果无法安装 zhparser/pg_jieba可以退而求其次用 pg_trgm三元组匹配中文可用但相关度弱或者在应用层分词后存储关键词列。五、什么时候应该把搜索交给外部系统PostgreSQL 全文检索不是倒排搜索引擎的完整替代品。适合用 PostgreSQL 全文检索的场景与关系数据强绑定的站内搜索商品标题、文章、工单。需要事务一致性和权限过滤的搜索。数据量在千万级以内查询模式以布尔匹配和相关性排序为主。不想引入 Elasticsearch 带来的双写、一致性同步和额外运维成本。应该考虑外部搜索引擎的场景需要复杂召回策略同义词运营、拼写纠错、多字段 BM25 调参。分布式大规模索引数十亿文档。多语言高级分词和专门的相关性学习。核心判断如果搜索需求可以在 SQL 中表达并且数据量没有超出单库承载能力PostgreSQL 全文检索是“零额外基础设施”的最优解。只有当相关性质量、召回策略或数据规模超出它的能力边界时外部搜索引擎才值得引入。六、小结LIKE 的三个结构性短板无词形归一化、无相关性排序、大表全表扫描。全文检索要解决的是“面向自然语言的可索引、可排序的文档检索表示”问题。tsvector 是文档表示层parser 切 token → dictionary 归一化 lexeme → tsvector 保存 lexeme、位置和权重。tsquery 表达查询 判断匹配。GIN 是全文检索的标准索引优先使用 存储生成列 GIN 索引而不是在每个查询中手动调用 to_tsvector。表达式索引必须使用两参数版本的 to_tsvector查询必须匹配索引的配置。中文必须依赖扩展pg_jieba结巴分词在标点鲁棒性和新词识别上更优新项目优先选择zhparserSCWS适合已有 SCWS 生态的场景。中文分词的三个坑索引与查询配置必须一致自定义词典需要定期维护云托管环境可能不支持自定义扩展需要 pg_trgm 或应用层分词作为备选。能力边界PostgreSQL 全文检索适合与关系数据强绑定的站内搜索复杂召回策略、大规模分布式索引和高级相关性调优应该交给外部搜索引擎。
企业数字化 ERP 产品动态
相关推荐
智能划词翻译产品介绍和使用指南 适用于 Microsoft Word 的 Windows 划词翻译工具智能划词翻译是一款面向 Windows 桌面版 Microsoft Word 的轻量工具。程序启动后常驻系统后台,在 Word 中选中英文或中文,再按下 Ctrl 和 Alt,即可在鼠标附近查看译文。无需安装浏览器插件&… · 2026/11/13 22:02:01
记录Oracle11g数据库OGG同步实验 目录 一、系统环境准备 1.1.基础环境 1.2.上传OGG软件 1.3.主备机hosts参数 二、OGG前期准备 2.1.源库与备库ogg登录测试 2.2.创建OGG应用目录 2.3.开启数据库级别补全日志 2.4.创建OGG管理用户及其表空间,查询一下数据文件路径 2.5.添加功能和角色给OGG用户 2.6.配… · 2026/11/14 1:58:35
编程方式创建测试序列与语法参考 1. 概念讲解前面几讲都在图形编辑器里手动搭序列。当测试规模变大、需要批量生成或纳入持续集成时,就必须编程方式创建测试序列。Simulink Test 提供 sltest.testsequence API,配合 sltest.harness 创建测试框架,可以用脚本完成建框架、加符号… · 2026/11/15 21:10:05
白河县建筑电工证网上查询全攻略:工地太忙根本没时间复习考试 白河县建筑电工证网上查询全攻略:工地太忙根本没时间复习考试 工地太忙根本没时间复习考试?白河县建筑电工证的办理和网上查询流程,你真的了解吗?作为一线项目管理人员,每天在工地穿梭,时间被切割成碎片,但特种作业证的办理与年审,却直接影响到你能不… · 2026/11/19 2:07:37
杨城电工证全国通用吗?外省证能转过来吗? 杨城电工证全国通用吗?外省证能转过来吗? 之前考的证在外省能不能转过来?这是很多在外地打工、或者在外省考了电工证的朋友最关心的问题。特别是对于在许昌本地从事建筑施工、机电安装的中小施工企业负责人来说,这个问题不仅关系到员工能否顺利上岗,还涉… · 2026/11/19 2:07:37
电工证审验样本怎么过?学历不高也能拿电子证书! 电工证审验样本怎么过?学历不高也能拿电子证书! 你是不是还在担心学历不高报不了电工证?别急,今天就给你讲明白, 电工证审验样本 怎么准备,怎么过,关键是还能拿到 电子证书 !别让学历成为你拿证路上的绊脚石。… · 2026/11/19 2:07:37
电工证补贴思申请流程电子证书查询 工地太忙根本没时间复习考试?电工证补贴申请流程全攻略+官方报名入口详解 工地太忙根本没时间复习考试,这话不是说说而已。每天扛着工具、赶着进度,你哪还有心思去复习电工证考试?可现实是, 没有电工证,你连进工地的资格都没有… · 2026/11/19 2:07:37
电工证到手多久能查询在哪考的常见疑问全解答 电工证到手多久能查询在哪考的常见疑问全解答 怕考不过白交培训费?电工证到手多久能查询,这几乎是每个想考电工证的应届生最关心的问题。特别是像郑州的工程类毕业生,刚入行就想拿到证书,但又怕报名后流程太慢,证书到手晚了影响工作安排。别急,下面这些… · 2026/11/19 2:07:37
怎么知道电工证的真假模拟考试题库 郑州电工证怎么知道真假?在哪考?实操考试心里没底怕挂科 你是不是正愁着电工证真假难辨?实操考试心里没底怕挂科?别慌,今天咱们就从零开始,带你搞清楚郑州电工证怎么知道真假, 在哪考 ,还有实操考试怎么准备,别再被坑了!… · 2026/11/19 2:06:37
长春初级电工证怎么考?通过率揭秘全在这里 长春初级电工证怎么考?通过率揭秘全在这里 之前考的证在外省能不能转过来?这几乎是每个想从事电工工作的人都会问的问题。特别是那些在外地打拼多年、手里握着一张“外地电工证”的人,到了长春想继续干这一行,第一反应就是“我的证能用吗?”。今天我们就… · 2026/11/18 8:19:55
国内电工证报考条件与考试时间 低压电工证怎么考?学历不高也能报名,官方报名入口全在这 你是不是经常听说“电工证是入行门槛”,但又担心自己学历不够、经验不足,根本报不了名?别急,今天就带你从零了解【国内电工证】的报考流程、考试内容,还有怎么在 官方报名入口… · 2026/11/18 8:19:53
电工作业时如何断电?电子证书到底能不能全国通用? 电工作业时如何断电?电子证书到底能不能全国通用? 实操考试心里没底怕挂科,特别是电工作业时如何断电这一操作,不仅关系到你能不能拿到证书,更关系到以后能不能顺利上岗。很多人觉得这不过就是“断个电”,但其实这里面的门道可不少,稍有不慎就可能出错… · 2026/11/18 14:38:14
姑苏区哪里考电工证好考前押题怎么找 姑苏区哪里考电工证好考前押题怎么找 工地太忙根本没时间复习考试,电工证考前还总找不到靠谱的 考前押题 资源,真让人头疼。如果你也是在苏州姑苏区从事建筑或电力行业的,那这些建议你一定要看完。 为什么姑苏区考电工证的人这么多?… · 2026/11/19 0:00:12
罗定能考电工证吗?外省证转过来流程全解析+郑州报考避坑指南 罗定能考电工证吗?外省证转过来流程全解析+郑州报考避坑指南 之前考的证在外省能不能转过来?这几乎是每个特种作业证书持有者都会遇到的问题,特别是像你这样已经在外地工作多年,想回老家罗定发展或者重新规划职业路径的人。别急,今天就给你一条清晰的路… · 2026/11/19 0:00:12
罗定能考电工证吗 电工证含金量到底有多少? 罗定能考电工证吗 电工证含金量到底有多少? 之前考的证在外省能不能转过来,这几乎是每个准备考电工证的朋友最先想到的问题。特别是在罗定这样的地方,很多人的电工证都是在外地考的,现在想在当地继续使用,但不知道怎么办。实际上,电工证的 含金量… · 2026/11/19 0:00:12
双极步进电机控制实战:DRV8818PWPR与PIC18F86K22驱动调试 /* MD / 富文本中的 .toc(含博客园搬家等嵌套结构);.toc-box 在侧栏,不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p(:empty 仍占 20px),一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/17 21:47:42
STM32F207ZG + DRV8818PWPR 步进电机驱动实战解析 /* MD / 富文本中的 .toc(含博客园搬家等嵌套结构);.toc-box 在侧栏,不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p(:empty 仍占 20px),一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/17 21:47:42
DRV8818PWPR+dsPIC30F4013工业级双极步进电机控制方案 /* MD / 富文本中的 .toc(含博客园搬家等嵌套结构);.toc-box 在侧栏,不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p(:empty 仍占 20px),一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/19 0:22:38