这篇文章主要给大家介绍了关于pgsql查询优化之模糊查询的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用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"为日(J)本(P)开发者提供。下面是详细的对比:(参考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 Featurespg_trgmpg_bigmPhrase matching method for full text search3-gram2-gramAvailable indexGIN and GiSTGIN onlyAvailable text search operatorsLIKE (~~), ILIKE (~~*), ~, ~*LIKE onlyFull text search for non-alphabetic language
(e.g., Japanese)Not supported (*1)SupportedFull text search with 1-2 characters keywordSlow (*2)FastSimilarity searchSupportedSupported (version 1.1 or later)Maximum indexed column size238,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。

以上

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

总结

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

最新资讯
途牛12月1日发布2020年第三季度财报

途牛12月1日发布2020

途牛今日宣布,将于美国东部时间12月1日美国股市开盘前(
华米科技第三季度营收22.351亿元 同比增长20%

华米科技第三季度营收

华米科技今日发布了截至9月30日的2019年第三季度财报,
中国电信柯瑞文:5G好不好用户说了算 三方面推动数字经济发展

中国电信柯瑞文:5G好不

中国电信董事长柯瑞文今日在世界互联网大会主论坛上,围
行业加速出清之际 还有哪些P2P平台在发标?

行业加速出清之际 还

潮起潮落终有时,曾经红极一时的P2P网贷亦是如此,然而谁
同程艺龙第三季度营收19.14亿元,同比下降64.79%

同程艺龙第三季度营收

同程艺龙发布第三季度财报。财报显示,第三季度营收19.1
蛋壳要碎,危及37万租客,多少亿才能拯救它?

蛋壳要碎,危及37万租客

蛋壳公寓累计亏损超60亿元,潜在债务完全是个无底洞,若想
最新文章
PostgreSQL基础知识之SQL操作符实践指南

PostgreSQL基础知识之

这篇文章主要给大家介绍了关于PostgreSQL基础知识之SQ
pgsql查询优化之模糊查询实例详解

pgsql查询优化之模糊

这篇文章主要给大家介绍了关于pgsql查询优化之模糊查
Ubuntu PostgreSQL安装和配置的介绍

Ubuntu PostgreSQL安

今天小编就为大家分享一篇关于Ubuntu PostgreSQL安装
PostgreSQL实现一个通用标签系统

PostgreSQL实现一个通

这篇文章主要给大家介绍了关于利用PostgreSQL实现一个
PostgreSQL中使用数组改进性能实例代码

PostgreSQL中使用数组

这篇文章主要给大家介绍了关于PostgreSQL中使用数组改
Postgresql主从异步流复制方案的深入探究

Postgresql主从异步流

这篇文章主要给大家介绍了关于Postgresql主从异步流复