POSTGRESQL(PG) 性能优化之like全文检索优化

已于 2023-09-22 19:13:52 修改
阅读量1.8k 收藏 2
点赞数
分类专栏： 数据库 性能分析 PostgreSQL 文章标签： postgresql 全文检索 数据库 性能优化
于 2023-09-22 19:08:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/helenbi/article/details/133179774
版权

使用like操作可以进行字符串比较，全文检索等，性能相对比较差，有些情况下可以通过建立索引来提升性能。下面我们通过使用TPCH orders表作为例子，来进行说明。但是请注意， not like是不能用任何索引的，BTREE不支持!=操作，只能进行=和范围查找。

TPCH orders表的定义如下，其中o_comment列是varchar类型的字符串：

tpch10=# \d orders
                          Table "public.orders"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 o_orderkey      | integer               |           | not null |
 o_custkey       | integer               |           | not null |
 o_orderstatus   | character(1)          |           | not null |
 o_totalprice    | numeric(15,2)         |           | not null |
 o_orderdate     | date                  |           | not null |
 o_orderpriority | character(15)         |           | not null |
 o_clerk         | character(15)         |           | not null |
 o_shippriority  | integer               |           | not null |
 o_comment       | character varying(79) |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

1. 前匹配 LIKE ‘prefix%’范围查询

前匹配可以使用BTREE索引进行范围查询，在没有任何索引的情况下，PG默认使用seqscan顺序扫描：

tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=75000 width=234) (actual time=0.662..5098.904 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Rows Removed by Filter: 14943130
 Planning Time: 0.268 ms
 Execution Time: 5103.280 ms
(5 rows)

我们在o_comment列加一下BTREE索引，然后再次尝试：

tpch10=# create index on orders(o_comment);
CREATE INDEX
tpch10=# analyze orders;
ANALYZE
tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=75000 width=234) (actual time=0.662..5098.904 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Rows Removed by Filter: 14943130
 Planning Time: 0.268 ms
 Execution Time: 5103.280 ms
(5 rows)
tpch10=# explain analyze select * from orders where o_comment like 'request';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_o_comment_idx on orders  (cost=0.56..8.58 rows=1 width=107) (actual time=0.188..0.188 rows=0 loops=1)
   Index Cond: ((o_comment)::text = 'request'::text)
   Filter: ((o_comment)::text ~~ 'request'::text)
 Planning Time: 0.408 ms
 Execution Time: 0.250 ms
(5 rows)

发现PG在like ‘prefix%’并没有使用BTREE索引，而精确匹配可以使用索引，问题在于默认的collate是en_US.UTF-8，而不是C，将o_comment列改为C collate再进行尝试。使用alter table命令将o_comment列改成collate C，可以查看到o_comment列的Collation属性是C。

tpch10=# alter table orders alter column o_comment TYPE VARCHAR(79) collate "C";
ALTER TABLE
tpch10=# \d orders
                          Table "public.orders"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 o_orderkey      | integer               |           | not null |
 o_custkey       | integer               |           | not null |
 o_orderstatus   | character(1)          |           | not null |
 o_totalprice    | numeric(15,2)         |           | not null |
 o_orderdate     | date                  |           | not null |
 o_orderpriority | character(15)         |           | not null |
 o_clerk         | character(15)         |           | not null |
 o_shippriority  | integer               |           | not null |
 o_comment       | character varying(79) | C         | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
    "orders_o_comment_idx" btree (o_comment)
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

tpch10=#

再执行一次之前的语句，发现使用了创建的BTREE索引，时间从5103ms提升到232ms，可以看到性能得到极大提升。

tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=3477.31..168092.77 rows=75000 width=234) (actual time=50.869..228.043 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Heap Blocks: exact=51160
   ->  Bitmap Index Scan on orders_o_comment_idx  (cost=0.00..3458.56 rows=75000 width=0) (actual time=33.213..33.213 rows=56870 loops=1)
         Index Cond: (((o_comment)::text >= 'request'::text) AND ((o_comment)::text < 'requesu'::text))
 Planning Time: 0.528 ms
 Execution Time: 232.989 ms
(7 rows)

tpch10=#

2. 后匹配 LIKE ‘%prefix’范围查询

后匹配的方式不能使用BTREE索引，原因在于BTREE索引只能做大于、大于等于、等于、小于、小于等于等操作决定的。因此这时候使用reverse index来处理后匹配的文本检索。

比如我们使用相同的例子，只是like匹配的是‘%request’，PG依旧使用顺序扫描，而没有使用之前建的索引。

tpch10=# explain analyze select * from orders where o_comment like '%request';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=151515 width=107) (actual time=0.043..8640.280 rows=57305 loops=1)
   Filter: ((o_comment)::text ~~ '%request'::text)
   Rows Removed by Filter: 14942695
 Planning Time: 1.065 ms
 Execution Time: 8645.223 ms
(5 rows)

我们给o_comment列建一个reverse的索引，会看到执行语句会选用reverse索引，时间由8645ms降到162ms，提升效率非常高。因此在这种情况下可以考虑使用reverse索引来提高后匹配的检索效率。

tpch10=# create index on orders(reverse(o_comment));
CREATE INDEX
tpch10=# analyze orders;
ANALYZE

tpch10=# explain analyze select * from orders where reverse(o_comment) like reverse('%request');
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_reverse_idx on orders  (cost=0.56..8.59 rows=1500 width=107) (actual time=0.142..157.412 rows=57305 loops=1)
   Index Cond: ((reverse((o_comment)::text) >= 'tseuqer'::text) AND (reverse((o_comment)::text) < 'tseuqes'::text))
   Filter: (reverse((o_comment)::text) ~~ 'tseuqer%'::text)
 Planning Time: 0.620 ms
 Execution Time: 162.048 ms
(5 rows)

tpch10=#

3. 中间匹配'%prefix%'范围查询

对于中间匹配的全文检索，以上两种索引都不能解决，PG提供了GIN索引，可以使用PG提供的pg_trgm插件来加速查询。GIN索引可以应用于前匹配、后匹配和中间匹配，但是因为GIN本身比较昂贵，对于前匹配和后匹配，可以使用上面提到的方式，中间匹配采用GIN索引，但是也需要考虑索引维护本身的代价。

在没有使用GIN索引时候，PG依旧使用顺序扫描：

tpch10=# explain analyze select * from orders where o_comment like '%request%';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448442.18 rows=2273093 width=107) (actual time=0.108..8773.358 rows=2318296 loops=1)
   Filter: ((o_comment)::text ~~ '%request%'::text)
   Rows Removed by Filter: 12681704
 Planning Time: 0.836 ms
 Execution Time: 8901.078 ms
(5 rows)

tpch10=# explain analyze select * from orders where reverse(o_comment) like reverse('%request%');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..485948.21 rows=3030791 width=107) (actual time=0.069..18095.022 rows=2318296 loops=1)
   Filter: (reverse((o_comment)::text) ~~ '%tseuqer%'::text)
   Rows Removed by Filter: 12681704
 Planning Time: 4.783 ms
 Execution Time: 18218.893 ms
(5 rows)

但是GIN索引的创建和维护非常昂贵，因为GIN索引对每组三个连续字符（三元组）进行索引，以便能够查找任何匹配模式。而且GIN索引也可以应用于前后匹配的组合使用，这种情况下效率也很高。

加载pg_trgm插件后，在o_comment列创建GIN索引，重新进行查询，时间由8901ms提升到3939ms，效率得到了提升，同时我们也可以看到GIN索引也可以进行‘%prefix%prefix%’的查询，效率也是很明显的提升。

tpch10=# create extension if not exists pg_trgm;
CREATE EXTENSION
tpch10=# create index on orders using gin (o_comment gin_trgm_ops);
CREATE INDEX
tpch10=# analyze orders;
ANALYZE

tpch10=# explain analyze select * from orders where o_comment like '%request%';
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=29361.97..330044.89 rows=3181674 width=107) (actual time=1727.012..3828.371 rows=2318296 loops=1)
   Recheck Cond: ((o_comment)::text ~~ '%request%'::text)
   Rows Removed by Index Recheck: 2088
   Heap Blocks: exact=260892
   ->  Bitmap Index Scan on orders_o_comment_idx1  (cost=0.00..28566.55 rows=3181674 width=0) (actual time=1604.355..1604.355 rows=2320384 loops=1)
         Index Cond: ((o_comment)::text ~~ '%request%'::text)
 Planning Time: 0.473 ms
 Execution Time: 3939.557 ms
(8 rows)


tpch10=# explain analyze select * from orders where o_comment like '%special%request%';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=1478.19..236736.24 rows=151508 width=107) (actual time=1794.711..2266.371 rows=168338 loops=1)
   Recheck Cond: ((o_comment)::text ~~ '%special%request%'::text)
   Rows Removed by Index Recheck: 80603
   Heap Blocks: exact=161167
   ->  Bitmap Index Scan on orders_o_comment_idx1  (cost=0.00..1440.31 rows=151508 width=0) (actual time=1731.915..1731.915 rows=248941 loops=1)
         Index Cond: ((o_comment)::text ~~ '%special%request%'::text)
 Planning Time: 0.501 ms
 Execution Time: 2278.128 ms
(8 rows)
helenbi
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
PostgreSQL LIKE 子句
12-16
PostgreSQL LIKE 子句 在 PostgreSQL 数据库中，我们如果要获取包含某些字符的数据，可以使用 LIKE 子句。 在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中，主要有以下两种通配符： 百分号 % 下划线 _ 如果没有使用以上两种通配符，LIKE 子句和等号 = 得到的结果是一样的。 语法 以下是使用 LIKE 子句搭配百分号 % 和下划线 _ 从数据库中获取数据的通用语法： SELECT FROM table_name WHERE column LIKE 'XXXX%'; 或者 SELECT FROM table_name
PostgreSQL Like查询与正则表达式
04-20 241
安装PostgreSQL之后，PostgreSQL会创建一个名为“postgres”的用户，创建一个名为“postgres”的数据库。我们就可以使用这个默认的库做实验。 首先建表并插入数据： CREATE TABLE public.user( ID SERIAL PRIMARY KEY NO...
PostgreSQL全文检索
风间琉璃zero
08-10 3289
PostgreSQL全文检索tsvectortsquery PostgreSQL全文检索 在日常的数据处理中，我们经常会有这样的需求：从一个文本中寻找某个字符串（比如某个单词）。 对这个需求，我们可以用类似这样的SQL完成： SELECT * FROM student WHERE text LIKE ‘%pgsql%’; （找到含有“pgsql”的文本）。 现在我们考虑一些特殊的情形： 需要查找的文本特别多，特别大； 不做单纯的字符串匹配，而是考虑自然语言的一些特性，比如匹配某一类字符串（域名、人名）.
MySql 中like的模糊查询如何优化
一千个读者就有一千个哈姆雷特
02-23 504
在MySQL中，使用like进行模糊查询，在一定情况下是无法使用索引的%abc%%abcabc%
postgresql 中的 like 查询优化
qq_23986087的博客
01-17 2324
当时数量量比较庞大的时候，做模糊查询效率很慢，为了优化查询效率，尝试如下方法做效率对比 一、对比情况说明： 1、数据量100w条数据 2、执行sql explain analyze SELECT c_patent, c_applyissno, d_applyissdate, d_applydate, c_patenttype_dimn, c_newlawstatus...
Postgres like 模糊查询匹配集合
gmHappy
11-10 1991
一、需求描述 存在两张表，表A的列要like模糊匹配表B的某列。 二、样例数据准备 create table t_goods ( id int primary key, name VARCHAR(50) ); insert into t_goods(id,name) VALUES (1,'iphone12'); insert into t_goods(id,name) VALUES (2,'iphone13'); insert into t_goods(id,name) VALUES
PostgreSQL优化SQL like ‘%%‘
Evil_0_0_0的博客
09-26 1046
简单原理是：一般数据库像like’%%‘不一定支持使用索引，但是postgresql的gin + gp_trgm支持like’%%'使用索引。创建索引：create index 索引名称 on 表名using gin (字段名 gin_trgm_ops);创建扩展：CREATE EXTENSION pg_trgm;
【记录】postgresql like 模糊查询优化
u010328311的博客
02-11 5266
【记录】postgresql like 模糊查询优化
postgresql性能优化
02-24
一个优化的SQL：下面默认以postgresql为例：1.尽量避免2.排序的数据量尽量少，并保证在内存里完成排序。（至于具体什么数据量能在内存中完成排序，不同数据库有不同的配置：oracle是sort_area_size；postgresql是...
PostgreSQL性能优化
08-24
给大家分享一套课程——PostgreSQL性能优化。
PostgreSQL窗口查询优化
01-06
一般我们都会用到窗口函数去解决这类问题，同样在pg中也支持窗口函数。 例子： 创建测试表，生成10000个分组，1000万条记录。 bill=# create table tbl(c1 int, c2 int, c3 int); CREATE TABLE bill=# create index ...
pgsql查询优化之模糊查询实例详解
01-19
前言 一直以来，对于搜索时模糊匹配的优化一直是个让人头疼的问题，好在强大pgsql提供了优化方案，下面就来简单谈一谈如何通过索引来优化模糊匹配 案例 我们有一张千万级数据的检查报告表，需要通过检查报告来模糊搜索某个条件，我们先创建如下索引： CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name); 然后搜个简单的模糊匹配条件如 LIKE “血常规%”，可以发现查询计划生成如下，索引并没有被使用上，这是因为传统的btree索引并不支持模糊匹配 查阅文档后发现，pgsql可以在Btree
PostgreSQL性能优化最佳综合案例实践
11-27
介绍了建立了一个包含INSERT, UPDATE, SELECT数据库请求的简单的业务模型, 并使用pgbench模拟业务请求, 详细的阐述PostgreSQL数据库的调优全过程。
一篇文章打通PostgreSql(pgsql)的正则匹配: like, similar to, ~。 以及与 mysql 正则匹配的区别。
m0_60851853的博客
10-04 5581
postgresql(pgsql) 模糊匹配与正则匹配， 与 mysql 的区别 : like, similar to, ~
PGSQL中的LIKE,ILIKE,SIMILAR TO的使用
Lou_Lan的博客
08-16 6466
这里主要时对模糊查询进行一些稍微细粒度的说明，更细力度的可能就需要各位看官各自去查询了。模糊查询只要是使用数据库，做开发的基本上都会知道这个LIKE。但是今天要进行说明的是PGSQL的模糊查询，PGSQL在很多地方都进行了功能的扩展（我猜想主要是由于开源的功劳）...
PostgreSQL里我用一下like
fgh123的博客
05-17 209
文章目录PostgreSQL里我用一下like PostgreSQL里我用一下like postgres=# select * from test1 where id2 like '%fg%'; id1 | id2 | id3 -----+-------+----- 12 | dfg | 32 162 | fgdgd | 3 162 | sgfg | 302 (3 rows) postgres=# explain analyze select * from test1
彻底搞懂PostgreSQL的模糊查询:LIKE和ILIKE
weixin_43922449的博客
09-23 7467
如果我们使用的是PostgreSQL数据库，那么我们可以使用LIKE和ILIKE做模糊查询，LIKE语法是SQL标准而ILIKE是PostgreSQL的一个扩展。 构造数据 先创建一张表，然后插入一些数据； create table test( id serial, name varchar(16) ); 基本使用 在使用 LIKE/ILIKE 时，有两个通配符：百分号 (%) 和下划线 (_) % - 匹配任何零个或多个字符 _ - 匹配单个任意字符 先看一下这个的查询： selec
postgresql关于like%xxx%的优化
yy23yy80的博客
01-16 1421
任何一个关系型数据库关于模糊匹配（like)的优化都是一件痛苦的事，相对而言，诸如like 'abc%'之类的还好一点，可以通过创建索引来优化，但对于like 'c%'之类的，真的就没有办法了。 这里介绍一种postgresql关于like 'c%'的优化方法，是基于全文检索的特性来实现的。 测试数据准备（环境centos6.5 + postgresql 9.6...
postgresql优化
最新发布
04-10
PostgreSQL是一种功能强大的开源关系型数据库管理系统，它提供了多种优化技术来提高数据库的性能和效率。下面是一些常见的PostgreSQL优化技术： 1. 索引优化：索引是提高查询性能的关键。在设计数据库时，根据查询的频率和类型选择合适的索引。同时，定期检查和重新构建索引以保持其有效性。 2. 查询优化：编写高效的SQL查询语句是提高性能的关键。使用合适的JOIN操作、WHERE条件和ORDER BY子句，避免不必要的数据扫描和排序。 3. 配置优化：通过调整PostgreSQL的配置参数来优化性能。例如，增加shared_buffers参数的值可以提高内存缓存的大小，减少磁盘IO操作。 4. 并发控制优化：PostgreSQL支持多个并发连接，但并发操作可能导致性能下降。通过合理设置max_connections参数和使用事务隔离级别来控制并发访问。 5. 查询缓存优化：PostgreSQL没有内置的查询缓存机制，但可以使用第三方扩展如pgpool-II或Redis来实现查询结果的缓存，减少重复查询的开销。 6. 数据分区和分表：对于大型数据库，可以将数据分区或分表存储，以减少单个表的数据量和查询开销。 7. 定期维护：定期进行数据库维护任务，如清理无用的数据、重新组织表和索引、更新统计信息等，以保持数据库的健康状态。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

helenbi

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值