postgresql 中的 like 查询优化方案

当时数量量比较庞大的时候，做模糊查询效率很慢，为了优化查询效率，尝试如下方法做效率对比

一、对比情况说明：

1、数据量100w条数据

2、执行sql

二、对比结果

explain analyze SELECT
 c_patent,
 c_applyissno,
 d_applyissdate,
 d_applydate,
 c_patenttype_dimn,
 c_newlawstatus,
 c_abstract
FROM
 public.t_knowl_patent_zlxx_temp
WHERE
 c_applicant LIKE '%本溪满族自治县连山关镇安平安养殖场%';

1、未建索时执行计划：

"Gather (cost=1000.00..83803.53 rows=92 width=1278) (actual time=217.264..217.264 rows=0 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 -> Parallel Seq Scan on t_knowl_patent_zlxx (cost=0.00..82794.33 rows=38 width=1278) (actual time=212.355..212.355 rows=0 loops=3)
  Filter: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
  Rows Removed by Filter: 333333
Planning time: 0.272 ms
Execution time: 228.116 ms"

2、btree索引

建索引语句

CREATE INDEX idx_public_t_knowl_patent_zlxx_applicant ON public.t_knowl_patent_zlxx(c_applicant varchar_pattern_ops);

执行计划

"Gather (cost=1000.00..83803.53 rows=92 width=1278) (actual time=208.253..208.253 rows=0 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 -> Parallel Seq Scan on t_knowl_patent_zlxx (cost=0.00..82794.33 rows=38 width=1278) (actual time=203.573..203.573 rows=0 loops=3)
  Filter: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
  Rows Removed by Filter: 333333
Planning time: 0.116 ms
Execution time: 218.189 ms"

但是如果将查询sql稍微改动一下，把like查询中的前置%去掉是这样的

Index Scan using idx_public_t_knowl_patent_zlxx_applicant on t_knowl_patent_zlxx_temp (cost=0.55..8.57 rows=92 width=1278) (actual time=0.292..0.292 rows=0 loops=1)
 Index Cond: (((c_applicant)::text ~>=~ '本溪满族自治县连山关镇安平安养殖场'::text) AND ((c_applicant)::text ~<~ '本溪满族自治县连山关镇安平安养殖圻'::text))
 Filter: ((c_applicant)::text ~~ '本溪满族自治县连山关镇安平安养殖场%'::text)
Planning time: 0.710 ms
Execution time: 0.378 ms

3、gin索引

创建索引语句(postgresql要求在9.6版本及以上)

create extension pg_trgm;
CREATE INDEX idx_public_t_knowl_patent_zlxx_applicant ON public.t_knowl_patent_zlxx USING gin (c_applicant gin_trgm_ops);

执行计划

Bitmap Heap Scan on t_knowl_patent_zlxx (cost=244.71..600.42 rows=91 width=1268) (actual time=0.649..0.649 rows=0 loops=1)
 Recheck Cond: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
 -> Bitmap Index Scan on idx_public_t_knowl_patent_zlxx_applicant (cost=0.00..244.69 rows=91 width=0) (actual time=0.647..0.647 rows=0 loops=1)
  Index Cond: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
Planning time: 0.673 ms
Execution time: 0.740 ms

三、结论

btree索引可以让后置% "abc%"的模糊匹配走索引，gin + gp_trgm可以让前后置% "%abc%" 走索引。但是gin 索引也有弊端，以下情况可能导致无法命中：

搜索字段少于3个字符时，不会命中索引，这是gin自身机制导致。

当搜索字段过长时，比如email检索，可能也不会命中索引，造成原因暂时未知。

补充：PostgreSQL LIKE 查询效率提升实验

一、未做索引的查询效率

作为对比，先对未索引的查询做测试

EXPLAIN ANALYZE select * from gallery_map where author = '曹志耘';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=1025 width=621) (actual time=0.011..39.753 rows=1031 loops=1)
 Filter: ((author)::text = '曹志耘'::text)
 Rows Removed by Filter: 71315
 Planning time: 0.194 ms
 Execution time: 39.879 ms
(5 rows)

Time: 40.599 ms
EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=1025 width=621) (actual time=0.017..41.513 rows=1031 loops=1)
 Filter: ((author)::text ~~ '曹志耘'::text)
 Rows Removed by Filter: 71315
 Planning time: 0.188 ms
 Execution time: 41.669 ms
(5 rows)

Time: 42.457 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘%';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.017..41.492 rows=1031 loops=1)
 Filter: ((author)::text ~~ '曹志耘%'::text)
 Rows Removed by Filter: 71315
 Planning time: 0.307 ms
 Execution time: 41.633 ms
(5 rows)

Time: 42.676 ms

很显然都会做全表扫描

二、创建btree索引

PostgreSQL默认索引是btree

CREATE INDEX ix_gallery_map_author ON gallery_map (author);

EXPLAIN ANALYZE select * from gallery_map where author = '曹志耘';
                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gallery_map (cost=36.36..2715.37 rows=1025 width=621) (actual time=0.457..1.312 rows=1031 loops=1)
 Recheck Cond: ((author)::text = '曹志耘'::text)
 Heap Blocks: exact=438
 -> Bitmap Index Scan on ix_gallery_map_author (cost=0.00..36.10 rows=1025 width=0) (actual time=0.358..0.358 rows=1031 loops=1)
   Index Cond: ((author)::text = '曹志耘'::text)
 Planning time: 0.416 ms
 Execution time: 1.422 ms
(7 rows)

Time: 2.462 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘';
                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gallery_map (cost=36.36..2715.37 rows=1025 width=621) (actual time=0.752..2.119 rows=1031 loops=1)
 Filter: ((author)::text ~~ '曹志耘'::text)
 Heap Blocks: exact=438
 -> Bitmap Index Scan on ix_gallery_map_author (cost=0.00..36.10 rows=1025 width=0) (actual time=0.560..0.560 rows=1031 loops=1)
   Index Cond: ((author)::text = '曹志耘'::text)
 Planning time: 0.270 ms
 Execution time: 2.295 ms
(7 rows)

Time: 3.444 ms
EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘%';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.015..41.389 rows=1031 loops=1)
 Filter: ((author)::text ~~ '曹志耘%'::text)
 Rows Removed by Filter: 71315
 Planning time: 0.260 ms
 Execution time: 41.518 ms
(5 rows)

Time: 42.430 ms
EXPLAIN ANALYZE select * from gallery_map where author like '%研究室';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=2282 width=621) (actual time=0.064..52.824 rows=2152 loops=1)
 Filter: ((author)::text ~~ '%研究室'::text)
 Rows Removed by Filter: 70194
 Planning time: 0.254 ms
 Execution time: 53.064 ms
(5 rows)

Time: 53.954 ms

可以看到，等于、like的全匹配是用到索引的，like的模糊查询还是全表扫描

三、创建gin索引

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_gallery_map_author ON gallery_map USING gin (author gin_trgm_ops);
EXPLAIN ANALYZE select * from gallery_map where author like '曹%';
                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gallery_map (cost=19.96..2705.69 rows=1028 width=621) (actual time=0.419..1.771 rows=1031 loops=1)
 Recheck Cond: ((author)::text ~~ '曹%'::text)
 Heap Blocks: exact=438
 -> Bitmap Index Scan on ix_gallery_map_author (cost=0.00..19.71 rows=1028 width=0) (actual time=0.312..0.312 rows=1031 loops=1)
   Index Cond: ((author)::text ~~ '曹%'::text)
 Planning time: 0.358 ms
 Execution time: 1.916 ms
(7 rows)

Time: 2.843 ms
EXPLAIN ANALYZE select * from gallery_map where author like '%耘%';
             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on gallery_map (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.015..51.641 rows=1031 loops=1)
 Filter: ((author)::text ~~ '%耘%'::text)
 Rows Removed by Filter: 71315
 Planning time: 0.268 ms
 Execution time: 51.957 ms
(5 rows)

Time: 52.899 ms
EXPLAIN ANALYZE select * from gallery_map where author like '%研究室%';
                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gallery_map (cost=31.83..4788.42 rows=2559 width=621) (actual time=0.914..4.195 rows=2402 loops=1)
 Recheck Cond: ((author)::text ~~ '%研究室%'::text)
 Heap Blocks: exact=868
 -> Bitmap Index Scan on ix_gallery_map_author (cost=0.00..31.19 rows=2559 width=0) (actual time=0.694..0.694 rows=2402 loops=1)
   Index Cond: ((author)::text ~~ '%研究室%'::text)
 Planning time: 0.306 ms
 Execution time: 4.403 ms
(7 rows)

Time: 5.227 ms

gin_trgm索引的效果好多了

由于pg_trgm的索引是把字符串切成多个3元组，然后使用这些3元组做匹配，所以gin_trgm索引对于少于3个字符（包括汉字）的查询，只有前缀匹配会走索引

另外，还测试了btree_gin，效果和btree一样

注意：

gin_trgm要求数据库必须使用UTF-8编码

demo_v1 # \l demo_v1
        List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges
---------+-----------+----------+-------------+-------------+-------------------
 demo_v1 | wmpp_user | UTF8  | en_US.UTF-8 | en_US.UTF-8 |

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • 解析PostgreSQL中Oid和Relfilenode的映射问题

    解析PostgreSQL中Oid和Relfilenode的映射问题

    作者李传成 中国PG分会认证专家,瀚高软件资深内核研发工程师 https://zhuanlan.zhihu.com/p/342466054 PostgreSQL中的表会有一个RelFileNode值指定这个表在磁盘上的文件名(外部表.分区表除外).一般情况下在pg_class表的relfilenode字段可以查出这个值,但是有一些特定表在relfilenode字段的查询结果是0,这个博客中将会探究这些特殊表relfilenode的内核处理. 正常表的Relfilenode 当我们创建一张普通表时

  • 解决PostgreSQL Array使用中的一些小问题

    在PostgreSQL 中可以使用Array数据结构,例如 select array[1,2,3]; return {1,2,3} 但是,如果 select array[1,2,3][1]; --会报错 select (select array[1,2,3])[1] --可以使用 那么在用正则匹配函数 regexp_match 就会遇到这样的问题,如 select regexp_match('123-123', '(\d+)-(\d+)'); --return {123, 123} select

  • PostgreSQL 禁用全表扫描的实现

    PostgreSQL可以通过一些设置来禁用全表扫描(FULL SCAN/Seq Scan) 注意: 设置此功能后不是完全避免全表扫描,而是只要有不通过全表扫描能得出结果的就不走全表扫描. 如果什么路都不通,那肯定得全表扫描,不然怎么获取数据. 而且并不是不走全表扫描性能就一定好. 下面展示下这个功能: 查询表结构: highgo=# \d test Table test Column | Type | Modifiers -------------+-----------------------

  • postgresql 实现replace into功能的代码

    PostgreSQL 9.5- 使用函数或with实现 create table test(id int primary key, info text, crt_time timestamp); with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 fro

  • PostgreSQL regexp_matches替换like模糊查询的操作

    我就废话不多说了,大家还是直接看代码吧~ 改前: select * from 表名 where 字段名 like ||#{参数}||'%' 改后: select *,regexp_matches(字段名, #{参数}) from 表名 补充:postgresql实现模糊查询 正则表达式 因为数据库的查询操作比较单一,所以大部分的模糊查询操作都需要手动编写程序来实现. postgresql提供有强大的正则表达式系统,可以在数据库级别实现模糊查询. 正则表达式匹配操作符: 操作符 描述 例子 ~ 匹

  • postgresql 中的 like 查询优化方案

    当时数量量比较庞大的时候,做模糊查询效率很慢,为了优化查询效率,尝试如下方法做效率对比 一.对比情况说明: 1.数据量100w条数据 2.执行sql 二.对比结果 explain analyze SELECT c_patent, c_applyissno, d_applyissdate, d_applydate, c_patenttype_dimn, c_newlawstatus, c_abstract FROM public.t_knowl_patent_zlxx_temp WHERE c_a

  • SQL Server多表查询优化方案集锦

    SQL Server多表查询的优化方案是本文我们主要要介绍的内容,本文我们给出了优化方案和具体的优化实例,接下来就让我们一起来了解一下这部分内容. 1.执行路径 ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就 可能要几十秒了.这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.o

  • Postgresql备份和增量恢复方案

    前言 最近工作上使用的数据库一直是Postgresql,这是一款开源的数据库,而且任何个人可以将该数据库用于商业用途.在使用Postgresql的时候,让我最明显的感觉就是这数据库做的真心好,虽然说数据库的安装包真的很小,但是性能和操作的便捷是一点也不输给其他商业的大型数据库,另外在命令行界面下对该数据库直接进行操作的感觉真的是很爽.在使用数据库的时候,我们作为小公司的数据库管理员有一项工作是不可能避免的,那就是数据的备份和恢复问题.PostgreSQL虽然各个方面的有点很多,但是在数据库备份这

  • postgresql 中的序列nextval详解

    postgresql 中的序列nextval详解

    一.postgresql中的序列 1.1 场景需求 需要向下图一样,需要对产品编码编码设置一个序列.编码规则 SKU + 序列号: 1.2 序列 序列是基于bigint算法的,因此范围是不能超过一个八字节 整数的范围(-9223372036854775808 到 9223372036854775807). 由于nextval和setval调用绝不会回滚, 如果需要序数的"无间隙"分配,则不能使用序列对象.可以 通过在一个只包含一个计数器的表上使用排他锁来构建无间隙的分配, 但是这种方案

  • PostgreSQL 数据库跨版本升级常用方案解析

    PostgreSQL 数据库跨版本升级常用方案解析

    大家好,我是只谈技术不剪发的 Tony 老师.对于企业而言,将数据库系统升级到新版本通常可以获得更好的性能.更多的功能.最新的安全补丁和错误修复等.因此,本文就来介绍一下 PostgreSQL 数据库版本升级的 3 种常用方案. 升级方案概述 PostgreSQL 版本号由主要版本和次要版本组成.例如,PostgreSQL 12.4 中的 12 是主要版本,4 是次要版本:PostgreSQL 10.0 之前的版本由 3 个数字组成,例如 9.6.19,其中 9.6 是主要版本,19 是次要版本

  • PostgreSQL中常用的时间日期脚本使用教程

    获取系统时间函数 select now(); --2013-11-28 16:20:25.259715+08 select current_timestamp; --2013-11-28 16:20:38.815466+08 select current_date; --2013-11-28 select current_time; --16:21:08.981171+08 时间的计算 --使用interval select now()+interval '2 day'; --2013-11-3

  • PHP 读取Postgresql中的数组

    复制代码 代码如下: function getarray_postgresql($arraystr) {     $regx1 = '/^{(.*)}$/';     $regx2 = "/\"((\\\\\\\\|\\\\\"|[^\"])+)\"|[^,]+/";     $regx3 = '/^[^"].*$|^"(.*)"$/';     $match = null;     preg_match( $reg

  • 在PostgreSQL中使用数组时值得注意的一些地方

    在PostgreSQL中使用数组时值得注意的一些地方

    在Heap中,我们依靠PostgreSQL支撑大多数后端繁重的任务,我们存储每个事件为一个hstore blob,我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组,并将这些事件按时间排序. Hstore能够让我们以灵活的方式附加属性到事件中,而且事件数组赋予了我们强大的性能,特别是对于漏斗查询,在这些查询中我们计算不同转化渠道步骤间的输出. 在这篇文章中,我们看看那些意外接受大量输入的PostgreSQL函数,然后以高效,惯用的方式重写它. 你的第一反应可能是将PostgreSQ

  • 在PostgreSQL中使用日期类型时一些需要注意的地方

    当我们这些使用Rails的人看到例如5.weeks.from_nowor3.days.ago + 2.hours时并不会感到惊讶.同样,PostgreSQL也可以做到,你可以通过简单调用PostgreSQL内置函数来实现相同的功能. 当前时间/日期/时间戳 获取当前时间的方式有很多种,在这之前我们需要知道以下两种类型的区别: 总是返回当前的值 (clock_timestamp()) 总是返回当前值,但在事务中它返回的是事务开始的时间(now()) 让我们看下面这个例子 postgres=# BE

  • 详解vue中移动端自适应方案

    方案1: 直接引入js  (自己 写的动态改变fontsize的js) function htRem() { var ww = document.documentElement.clientWidth; if (ww > 750) { ww = 750; } document.documentElement.style.fontSize = ww / 7.5 + "px"; } htRem(); window.onresize = function() { htRem(); };

随机推荐