您的位置：首页 > 数据库 > PostgreSQL  >  pgsql查询优化之模糊查询实例详解

pgsql查询优化之模糊查询实例详解

 更新时间：2020年7月11日 17:22  点击：1944

前言

一直以来，对于搜索时模糊匹配的优化一直是个让人头疼的问题，好在强大pgsql提供了优化方案，下面就来简单谈一谈如何通过索引来优化模糊匹配

案例

我们有一张千万级数据的检查报告表，需要通过检查报告来模糊搜索某个条件，我们先创建如下索引：

CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);

然后搜个简单的模糊匹配条件如 LIKE "血常规%"，可以发现查询计划生成如下，索引并没有被使用上，这是因为传统的btree索引并不支持模糊匹配

查阅文档后发现，pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops，它们分别对应字段类型text、varchar和char，官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中。”， 有些抽象，我们先试试看。创建如下索引并查询刚才的条件 LIKE"血常规%"：（参考pgsql的文档https://www.postgresql.org/docs/10/indexes-opclass.html）

CREATE INDEX lab_report_report_name_index ON lab.lab_report (report_name varchar_pattern_ops);

发现确实可以走索引扫描 ，执行时间也从213ms优化到125ms，但是，如果搜索LIKE "%血常规%"就又会走全表扫描了！ 这里我们引入本篇博客的主角"pg_trgm"和"pg_bigm"。

创建这两个索引前分别需要引入如下两个扩展包 ：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_bigm;

这两个索引的区别是：“pg_tigm”为pgsql官方提供的索引，"pg_tigm"为日本开发者提供。下面是详细的对比：（参考pg_bigm的文档http://pgbigm.osdn.jp/pg_bigm_en-1-2.html）

Comparison with pg_trgm

Thepg_trgmcontrib module which provides full text search capability using 3-gram (trigram) model is included in PostgreSQL. The pg_bigm was developed based on the pg_trgm. They have the following differences:

Functionalities and Features pg_trgm pg_bigm
Phrase matching method for full text search 3-gram 2-gram
Available index GIN and GiST GIN only
Available text search operators LIKE (~~), ILIKE (~~*), ~, ~* LIKE only
Full text search for non-alphabetic language
(e.g., Japanese)		 Not supported (*1) Supported
Full text search with 1-2 characters keyword Slow (*2) Fast
Similarity search Supported Supported (version 1.1 or later)
Maximum indexed column size 238,609,291 Bytes (~228MB) 107,374,180 Bytes (~102MB)

(*1) You can use full text search for non-alphabetic language by commenting out KEEPONLYALNUM macro variable in contrib/pg_trgm/pg_trgm.h and rebuilding pg_trgm module. But pg_bigm provides faster non-alphabetic search than such a modified pg_trgm.

(*2) Because, in this search, only sequential scan or index full scan (not normal index scan) can run.

pg_bigm 1.1 or later can coexist with pg_trgm in the same database, but pg_bigm 1.0 cannot.

如无特殊要求推荐使用"pg_bigm"，我们测试一下效果：

可以使用位图索引扫描，对于本次案例，使用pg_trgm效果同pg_bigm。

以上

本文只是简单的介绍许多细节并未做深入的分析，欢迎留言指教或者讨论

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对猪先飞的支持。

[!--infotagslink--]

相关文章

  • Mybatis Plus select 实现只查询部分字段

    这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01

  • MyBatisPlus-QueryWrapper多条件查询及修改方式

    这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2022-06-27

  • Oracle使用like查询时对下划线的处理方法

    这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法，本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16

  • 解决mybatis-plus 查询耗时慢的问题

    这篇文章主要介绍了解决mybatis-plus 查询耗时慢的问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-07-04

  • MySQL中在查询结果集中得到记录行号的方法

    如果需要在查询语句返回的列中包含一列表示该条记录在整个结果集中的行号， ISO SQL:2003 标准提出的方法是提供 ROW_NUMBER() / RANK() 函数。 Oracle 中可以使用标准方法（8i版本以上），也可以使用非标准的 ROWNUM ； MS SQL...2015-03-15

  • Node实现搜索框进行模糊查询

    这篇文章主要为大家详细介绍了Node实现搜索框进行模糊查询，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2021-06-28

  • Mybatis用注解写in查询的实现

    这篇文章主要介绍了Mybatis用注解写in查询的实现方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-07-13

  • PHP+Mysql+jQuery查询和列表框选择操作实例讲解

    本文讲解如何通过ajax查询mysql数据，并将返回的数据显示在待选列表中，再通过选择最终将选项加入到已选区，可以用在许多后台管理系统中。本文列表框的操作依赖jquery插件。HTML <form id="sel_form" action="post.php" me...2015-10-23

  • Element-ui 自带的两种远程搜索(模糊查询)用法讲解

    这篇文章主要介绍了Element-ui 自带的两种远程搜索(模糊查询)用法讲解,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2021-01-29

  • Mybatis和Mybatis-Plus时间范围查询方式

    这篇文章主要介绍了Mybatis和Mybatis-Plus时间范围查询方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-08-06

  • mysql like查询字符串示例语句

    MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式 一、SQL模式SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符（包括零个字符）。在 MySQL...2013-10-04

  • JPA如何使用nativequery多表关联查询返回自定义实体类

    这篇文章主要介绍了JPA如何使用nativequery多表关联查询返回自定义实体类，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-11-18

  • C#百万数据查询出现超时问题的解决方法

    这篇文章主要介绍了C#百万数据查询出现超时问题的解决方法,是非常实用的技巧,需要的朋友可以参考下...2020-06-25

  • Select下拉框模糊查询功能实现代码

    这篇文章主要介绍了Select下拉框模糊查询功能实现代码的相关资料,非常不错，具有参考借鉴价值，需要的朋友可以参考下...2016-07-29

  • postgresql数据库连接数和状态查询操作

    这篇文章主要介绍了postgresql数据库连接数和状态查询操作，具有很好的参考价值，对大家有所帮助。一起跟随小编过来看看吧...2021-02-01

  • postgresql的jsonb数据查询和修改的方法

    这篇文章主要介绍了postgresql的jsonb数据查询和修改的方法,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下...2021-03-03

  • Mybatis-Plus根据时间段去查询数据的实现示例

    这篇文章主要介绍了Mybatis-Plus根据时间段去查询数据的实现示例，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2021-04-18

  • rails常用数据库查询操作、方法浅析

    这篇文章主要介绍了rails常用数据库查询操作、方法浅析,总结的比较全,WEB开发种常用的数据库操作都列出了rails对应代码,需要的朋友可以参考下...2020-06-30

  • Oracle 实现将查询结果保存到文本txt中

    这篇文章主要介绍了Oracle 实现将查询结果保存到文本txt中的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-07

  • python3 sqlite3限制条件查询的操作

    这篇文章主要介绍了python3 sqlite3限制条件查询的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-04-07