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

前言

一直以来，对于搜索时模糊匹配的优化一直是个让人头疼的问题，好在强大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。

以上

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

总结

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

赞 (0)

相关推荐

  • Postgresql查询效率计算初探

    摘要 关系数据库很重要的一个方面是查询速度.查询速度的好坏,直接影响一个系统的好坏. 查询速度一般需要通过查询规划来窥视执行的过程. 查询路径会选择查询代价最低的路径执行.而这个代价是怎么算出来的呢. 主要关注的参数和表 参数:来自postgresql.conf文件,可以通过show 来查看 seq_page_cost = 1.0 # measured on an arbitrary scale random_page_cost = 4.0 # same scale as above cpu_t

  • 在PostgreSQL中实现递归查询的教程

    在PostgreSQL中实现递归查询的教程

     介绍 在Nilenso,哥在搞一个 (开源的哦!)用来设计和发起调查的应用. 下面这个是一个调查的例子: 在内部,它是这样表示滴: 一个调查包括了许多问题(question).一系列问题可以归到(可选)一个分类(category)中.我们实际的数据结构会复杂一点(特别是子问题sub-question部分),但先当它就只有question跟category吧. 我们是这样保存question跟category的. 每个question和category都有一个order_number字段.是个整

  • PostgreSQL中使用dblink实现跨库查询的方法

    PostgreSQL中使用dblink实现跨库查询的方法

    最近一个sql语句涉及到跨库的表之间的联合查询,故研究学习下. 一开始研究知道了sql语句的写法,但是执行通过不了,需要先安装dblink扩展.这些博文里都没说清楚,感谢网友指点,在windows下只需要在相应的数据库下执行sql语句"create extension dblink"就ok了.而以上的博文基本上说的都是linux下的操作方法,也因此我走了弯路. 2.下载了PostgreSQL的源代码,却不知道语句在哪执行,貌似是在linux下的shell里执行的,我却在psql和cmd

  • PostgreSQL树形结构的递归查询示例

    背景 处理不确定深度的层级结构,比如组织机构,一个常用的设计是在一张表里面保存 ID 和 Parent_ID ,并且通过自联结的办法构造一颗树.这种方式对写数据的过程很友好,但是查询过程就变得相对复杂.在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点. Oracle提供的connect by扩展语法,简单好用.但是其他的RDBMS就没这么人性化了(或者我不知道).最近在项目中使用PostgreSQL来查询树形数据,记录一下. 构造样本数据 drop table if ex

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

    前言 一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配 案例 我们有一张千万级数据的检查报告表,需要通过检查报告来模糊搜索某个条件,我们先创建如下索引: CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name); 然后搜个简单的模糊匹配条件如 LIKE "血常规%",可以发现查询计划生成如下,索

  • mybatis的动态SQL和模糊查询实例详解

    mybatis的动态SQL和模糊查询实例详解

    现在以一个例子来介绍mybatis的动态SQL和模糊查询:通过多条件查询用户记录,条件为姓名模糊匹配,并且年龄在某两个值之间. 新建表d_user: create table d_user( id int primary key auto_increment, name varchar(10), age int(3) ); insert into d_user(name,age) values('Tom',12); insert into d_user(name,age) values('Bob

  • Java模糊查询方法详解

    当我们需要开发一个方法用来查询数据库的时候,往往会遇到这样一个问题:就是不知道用户到底会输入什么条件,那么怎么样处理sql语句才能让我们开发的方法不管接受到什么样的条件都可以正常工作呢?这时where '1'='1'加上list就可以完美解决这个问题了,废话少说,上代码: // 模糊查询方法 public List<person> query() { List<person> list = new ArrayList<>(); Connection con = null

  • MySQL单表查询实例详解

    1.准备数据 以下操作将在该表中进行 create table student ( id int unsigned primary key auto_increment, name char(12) not null, gender enum("male","famale") default "male", age tinyint unsigned not null, hoc_group char(12) not null, html tinyi

  • mybatis-plus自带QueryWrapper自定义sql实现复杂查询实例详解

    mybatis-plus自带QueryWrapper自定义sql实现复杂查询实例详解

    目录 常用写法 自定义sql 补充: 查询条件中使用函数apply 补充: 末尾sql追加last 补充:排序 补充:Mybatis-Plus QueryWrapper +sql自定义查询 总结 常用写法 xxxDO=实体类, 相当于: select name, id from xxx group by name where delete_flag = 0 and status = 0 LambdaQueryWrapper<xxxDO> queryWrapper = Wrappers.lamb

  • ThinkPHP5联合(关联)查询、多条件查询与聚合查询实例详解

    本文实例讲述了ThinkPHP5联合(关联)查询.多条件查询与聚合查询.分享给大家供大家参考,具体如下: 一.联合(关联)查询 1. 项目表 DROP TABLE IF EXISTS `darling_project`; CREATE TABLE `darling_project` ( `project_id` int(32) NOT NULL AUTO_INCREMENT, `project_name` varchar(20) NOT NULL, `create_time` int(32) N

  • MySQL嵌套查询实例详解

    本文实例分析了MySQL嵌套查询.分享给大家供大家参考,具体如下: MySQl从4.11版后已经完全支持嵌套查询了,那么下面举些简单的嵌套查询的例子吧(源程序来自MySQL User Manual): 1. SELECT语句的子查询 语法: 复制代码 代码如下: SELECT ... FROM (subquery) AS name ... 先创建一个表: CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); INSERT INTO t1 VALUES (

  • Mysql自连接查询实例详解

    Mysql自连接查询实例详解

    本文实例讲述了Mysql自连接查询.分享给大家供大家参考,具体如下: 自连接查询 假想以下场景:某一电商网站想要对站内产品做层级分类,一个类别下面有若干子类,子类下面也会有别的子类.例如数码产品这个类别下面有笔记本,台式机,智能手机等:笔记本,台式机,智能手机又可以按照品牌分类:品牌又可以按照价格分类,等等.也许这些分类会达到一个很深的层次,呈现一种树状的结构.那么这些数据要怎么在数据库中表示呢?我们可以在数据库中创建两个字段来存储id和类别名称,使用第三个字段存储类别的子类或者父类的id,最后

  • MySQL连接查询实例详解

    本文实例讲述了MySQL连接查询.分享给大家供大家参考,具体如下: 创建表suppliers: CREATE TABLE suppliers ( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ; INSERT INTO suppliers(s_i

  • MySQL多表查询实例详解【链接查询、子查询等】

    本文实例讲述了MySQL多表查询.分享给大家供大家参考,具体如下: 准备工作:准备两张表,部门表(department).员工表(employee) create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'ma

随机推荐