全文检索 (不包含、不等于) 索引优化 - 阿里云RDS PostgreSQL最佳实践

背景

PostgreSQL内置了GIN索引，支持全文检索，支持数组检索等多值数据类型的检索。

在全文检索中，不包含某个关键字能用到索引吗？

实际上GIN是倒排索引，不包含某个关键字的查询，实际上是跳过主tree上面的TOKEN的扫描。

只要被跳过的TOKEN包含了大量数据，那么就是划算的。PostgreSQL是基于CBO的执行计划优化器，所以会自动选择最优的索引。

例子1，全文检索不包含查询

1、创建测试表

postgres=# create table notcontain (id int, info tsvector);
CREATE TABLE

2、创建生成随机字符串的函数

CREATE OR REPLACE FUNCTION
gen_rand_str(integer)
 RETURNS text
 LANGUAGE sql
 STRICT
AS $function$
  select string_agg(a[(random()*6)::int+1],'') from generate_series(1,$1), (select array['a','b','c','d','e','f',' ']) t(a);
$function$;

3、插入100万测试数据

postgres=# insert into notcontain select generate_series(1,1000000), to_tsvector(gen_rand_str(256));

4、创建全文索引（GIN索引）

create index idx_notcontain_info on notcontain using gin (info);

5、查询某一条记录

postgres=# select * from notcontain limit 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id   | 1
info | 'afbbeeccbf':3 'b':16 'bdcdfd':2 'bdcfbcecdeeaed':8 'bfedfecbfab':7 'cd':9 'cdcaefaccdccadeafadededddcbdecdaefbcfbdaefcec':14 'ceafecff':6 'd':17,18 'dbc':12 'dceabcdcbdca':10 'dddfdbffffeaca':13 'deafcccfbcdebdaecda':11 'dfbadcdebdedbfa':19 'eb':15 'ebe':1 'febdcbdaeaeabbdadacabdbbedfafcaeabbdcedaeca':5 'fedeecbcdfcdceabbabbfcdd':4

6、测试不包含某个关键字

数据库自动选择了全表扫描，没有使用GIN索引。

为什么没有使用索引呢，我前面解释了，因为这个关键字的数据记录太少了，不包含它时使用索引过滤不划算。

（当包含它时，使用GIN索引就非常划算。包含和不包含是相反的过程，成本也是反的）

select * from notcontain t1 where info @@ to_tsquery ('!eb');  

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!eb');
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on postgres.notcontain t1  (cost=0.00..318054.51 rows=950820 width=412) (actual time=0.016..1087.463 rows=947911 loops=1)
   Output: id, info
   Filter: (t1.info @@ to_tsquery('!eb'::text))
   Rows Removed by Filter: 52089
   Buffers: shared hit=55549
 Planning time: 0.131 ms
 Execution time: 1134.571 ms
(7 rows)

7、强制关闭全表扫描，让数据库选择索引。

可以看到，使用索引确实是慢的，我们大多数时候应该相信数据库的成本规划是准确的。（只要成本因子和环境性能匹配足够的准，这些都是可以校准的，有兴趣的同学可以参考我写的因子校准方法。）

postgres=# set enable_seqscan=off;
SET  

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!eb');
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on postgres.notcontain t1  (cost=82294981.00..82600120.25 rows=950820 width=412) (actual time=1325.587..1540.145 rows=947911 loops=1)
   Output: id, info
   Recheck Cond: (t1.info @@ to_tsquery('!eb'::text))
   Heap Blocks: exact=55549
   Buffers: shared hit=171948
   ->  Bitmap Index Scan on idx_notcontain_info  (cost=0.00..82294743.30 rows=950820 width=0) (actual time=1315.663..1315.663 rows=947911 loops=1)
         Index Cond: (t1.info @@ to_tsquery('!eb'::text))
         Buffers: shared hit=116399
 Planning time: 0.135 ms
 Execution time: 1584.670 ms
(10 rows)

例子2，全文检索不包含查询

这个例子造一份倾斜数据，这个TOKEN包含了大量的重复记录，通过不包含过滤它。看看能否使用索引。

1、生成测试数据

postgres=# truncate notcontain ;
TRUNCATE TABLE
postgres=# insert into notcontain select generate_series(1,1000000), to_tsvector('abc');
INSERT 0 1000000

2、测试不包含ABC的检索

数据库自动选择了索引扫描，跳过了不需要检索的数据块。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!abc');
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on postgres.notcontain t1  (cost=220432.15..220433.71 rows=1 width=21) (actual time=107.936..107.936 rows=0 loops=1)
   Output: id, info
   Recheck Cond: (t1.info @@ to_tsquery('!abc'::text))
   Buffers: shared hit=268
   ->  Bitmap Index Scan on idx_notcontain_info  (cost=0.00..220432.15 rows=1 width=0) (actual time=107.933..107.933 rows=0 loops=1)
         Index Cond: (t1.info @@ to_tsquery('!abc'::text))
         Buffers: shared hit=268
 Planning time: 0.183 ms
 Execution time: 107.962 ms
(9 rows)

3、强制使用全表扫描，发现性能确实不如索引扫描，也验证了我们说的PostgreSQL是基于成本的优化器，自动选择最优的执行计划。

postgres=# set enable_bitmapscan =off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!abc');
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on postgres.notcontain t1  (cost=0.00..268870.00 rows=1 width=21) (actual time=1065.436..1065.436 rows=0 loops=1)
   Output: id, info
   Filter: (t1.info @@ to_tsquery('!abc'::text))
   Rows Removed by Filter: 1000000
   Buffers: shared hit=6370
 Planning time: 0.059 ms
 Execution time: 1065.449 ms
(7 rows)

例子3，普通类型BTREE索引，不等于检索

这个例子是普通类型，使用BTREE索引，看看是否支持不等于的索引检索。

测试方法与GIN测试类似，使用倾斜和非倾斜两种测试数据。

1、非倾斜数据的不包含查询，使用索引过滤的记录非常少。

目前内核层面没有实现BTREE索引的不包含检索。（虽然技术上是可以通过INDEX SKIP SCAN来实现的，跳过不需要扫描的BRANCH节点）

postgres=# truncate notcontain ;
TRUNCATE TABLE
postgres=# insert into notcontain select generate_series(1,1000000);
INSERT 0 1000000
postgres=# create index idx1 on notcontain (id);
CREATE INDEX
postgres=# set enable_bitmapscan =on;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where id<>1;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on postgres.notcontain t1  (cost=0.00..16925.00 rows=999999 width=36) (actual time=0.011..110.592 rows=999999 loops=1)
   Output: id, info
   Filter: (t1.id <> 1)
   Rows Removed by Filter: 1
   Buffers: shared hit=4425
 Planning time: 0.195 ms
 Execution time: 156.013 ms
(7 rows)  

postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where id<>1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on postgres.notcontain t1  (cost=10000000000.00..10000016925.00 rows=999999 width=36) (actual time=0.011..110.964 rows=999999 loops=1)
   Output: id, info
   Filter: (t1.id <> 1)
   Rows Removed by Filter: 1
   Buffers: shared hit=4425
 Planning time: 0.062 ms
 Execution time: 156.461 ms
(7 rows)

2、更换SQL写法，可以实现索引检索。但实际上由于不是使用的INDEX SKIP SCAN，所以需要一个JOIN过程，实际上效果并不佳。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=0.85..25497.28 rows=999999 width=36) (actual time=0.023..277.639 rows=999999 loops=1)
   Output: t1.id, t1.info
   Merge Cond: (t1.id = t2.id)
   Buffers: shared hit=7164
   ->  Index Scan using idx1 on postgres.notcontain t1  (cost=0.42..22994.22 rows=1000000 width=36) (actual time=0.009..148.520 rows=1000000 loops=1)
         Output: t1.id, t1.info
         Buffers: shared hit=7160
   ->  Index Only Scan using idx1 on postgres.notcontain t2  (cost=0.42..3.04 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
         Output: t2.id
         Index Cond: (t2.id = 1)
         Heap Fetches: 1
         Buffers: shared hit=4
 Planning time: 0.223 ms
 Execution time: 322.798 ms
(14 rows)
postgres=# set enable_mergejoin=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=3.05..27053.05 rows=999999 width=36) (actual time=0.060..251.232 rows=999999 loops=1)
   Output: t1.id, t1.info
   Hash Cond: (t1.id = t2.id)
   Buffers: shared hit=4432
   ->  Seq Scan on postgres.notcontain t1  (cost=0.00..14425.00 rows=1000000 width=36) (actual time=0.011..84.659 rows=1000000 loops=1)
         Output: t1.id, t1.info
         Buffers: shared hit=4425
   ->  Hash  (cost=3.04..3.04 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
         Output: t2.id
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=4
         ->  Index Only Scan using idx1 on postgres.notcontain t2  (cost=0.42..3.04 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)
               Output: t2.id
               Index Cond: (t2.id = 1)
               Heap Fetches: 1
               Buffers: shared hit=4
 Planning time: 0.147 ms
 Execution time: 297.127 ms
(18 rows)  

postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=3.48..35622.27 rows=999999 width=36) (actual time=0.036..324.401 rows=999999 loops=1)
   Output: t1.id, t1.info
   Hash Cond: (t1.id = t2.id)
   Buffers: shared hit=7164
   ->  Index Scan using idx1 on postgres.notcontain t1  (cost=0.42..22994.22 rows=1000000 width=36) (actual time=0.017..149.383 rows=1000000 loops=1)
         Output: t1.id, t1.info
         Buffers: shared hit=7160
   ->  Hash  (cost=3.04..3.04 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)
         Output: t2.id
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=4
         ->  Index Only Scan using idx1 on postgres.notcontain t2  (cost=0.42..3.04 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)
               Output: t2.id
               Index Cond: (t2.id = 1)
               Heap Fetches: 1
               Buffers: shared hit=4
 Planning time: 0.141 ms
 Execution time: 369.749 ms
(18 rows)

3、PostgreSQL还支持多核并行，所以全表扫描还可以暴力提升性能。

如果记录数非常多，使用并行扫描，性能提升非常明显。

postgres=# create  unlogged table ptbl(id int);
CREATE TABLE
postgres=# insert into ptbl select generate_series(1,100000000);  

postgres=# alter table ptbl set (parallel_workers =32);  

\timing  

非并行查询：
postgres=# set max_parallel_workers_per_gather =0;
postgres=# select count(*) from ptbl where id<>1;
  count
----------
 99999999
(1 row)  

Time: 11863.151 ms (00:11.863)  

并行查询：
postgres=# set max_parallel_workers_per_gather =32;
postgres=# select count(*) from ptbl where id<>1;
  count
----------
 99999999
(1 row)  

Time: 610.017 ms

使用并行查询后，性能提升非常明显。

例子4，普通类型partial BTREE索引，不等于检索

对于固定的不等于查询，我们可以使用PostgreSQL的partial index功能。

create table tbl (id int, info text, crt_time timestamp, c1 int);

select * from tbl where c1<>1;

insert into tbl select generate_series(1,10000000), 'test', now(), 1;
insert into tbl values (1,'abc',now(),2);

create index idx_tbl_1 on tbl(id) where c1<>1;

cool，使用PARTIAL INDEX，0.03毫秒，在1000万数据中进行不等于检索。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c1<>1;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tbl_1 on postgres.tbl  (cost=0.12..1.44 rows=1 width=21) (actual time=0.015..0.015 rows=1 loops=1)
   Output: id, info, crt_time, c1
   Buffers: shared hit=1 read=1
 Planning time: 0.194 ms
 Execution time: 0.030 ms
(5 rows)

小结

1、PostgreSQL内置了GIN索引，支持全文检索、支持数组等多值类型的搜索。

2、PostgreSQL使用基于成本的执行计划优化器，会自动选择最优的执行计划，在进行不包含检索时，PostgreSQL会自动选择是否使用索引扫描。

3、对于BTREE索引，理论上也能实现不等于的搜索（INDEX SKIP SCAN），目前内核层面还没有实现它，目前可以通过调整SQL的写法来使用索引扫描。

4、PostgreSQL还支持多核并行，所以全表扫描还可以暴力提升性能。如果记录数非常多，使用并行扫描，性能提升非常明显。

5、PostgreSQL支持partial index，可以用于分区索引，或者部分索引。对于固定条件的不等于查询，效果非常显著。

时间： 2024-08-29 09:19:13

全文检索 (不包含、不等于) 索引优化 - 阿里云RDS PostgreSQL最佳实践的相关文章

空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , Greenplum , PostGIS , GiST , R-Tree , BRIN , 相关性 , 网格 , BOX , K-Mean 背景空间数据的搜索需求通常包括: 1.平面.三维.多维对象几何相交.不相交.相邻. 2.平面.三维.多维对象的方位判断(相交或严格在左边.右边.上边.下边),类似数值的大于.小于.大于等于.小于等于. 3.平面.三维.多维对象包含另一个对象 4.平面.三维.多维对象等于另一个对象 5.平面.三维.多维对象与另一个对

(新零售)商户网格化运营 - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , PostGIS , 地理位置 , KNN , 近邻检索 , 网格检索 , polygon中心点 , 半径搜索背景伟大的马老师说: "纯电商时代很快会结束,未来的十年.二十年,没有电子商务这一说,只有新零售这一说,也就是说线上线下和物流必须结合在一起,才能诞生真正的新零售" 线上是指云平台,线下是指销售门店或生产商,新物流消灭库存,减少囤货量. 电子商务平台消失是指,现有的电商平台分散,每个人都有自己的电商平台,不再入驻天猫.京东.亚马逊大型电子商务平

时间、空间、对象海量极速多维检索 - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , 时间 , 空间 , 对象属性 , 多维度检索 , 海量 , 空间索引 , 数据分区 , 块级索引BRIN , 多级索引 , GIN倒排索引 , JSON索引 , 多列索引 , 多索引扫描合并 , bitmapAnd , bitmapOr , 物理扫描 , ctid扫描 , intersect , partial index , partition index 背景人类或者其他对象的活动产生了海量的时间.空间数据,如果有科技能实现回到过去,过去的世界状态会是什么样

菜鸟末端轨迹(解密支撑每天251亿个包裹的数据库) - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , PostGIS , 多边形 , 面 , 点 , 面点判断 , 菜鸟背景菜鸟末端轨迹项目中涉及的一个关键需求,面面判断. 在数据库中存储了一些多边形记录,约几百万到千万条记录,例如一个小区,在地图上是一个多边形. 不同的快递公司,会有各自不同的多边形划分方法(每个网点负责的片区(多边形),每个快递员负责的片区(多边形)). 用户在寄件时,根据用户的位置,查找对应快递公司负责这个片区的网点.或者负责该片区的快递员. 一.需求 1.在数据库中存储了一些静态的面信息,

医疗大健康行业案例(老人健康实时监测和预警) - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , pipelineDB , 流式计算 , 独立事件相关性 , 舆情分析 , 实时状态分析 , 递归查询 , 时序数据背景人的身体和机器差不多,随着年龄的增长,器官逐渐老化,毛病也会越来越多,注意保养是一方面,另一方面也需要注意实时的监测和发出预警,在问题萌芽状态就解决掉. 以往我们检查身体得去医院或专业的体检机构,很麻烦,随着科技的进步,一些健康指标的监测变得更加方便,例如手环也是一个普及很快的监控检测终端(目前已能够检测心跳.温度.运动等各项指标),未来这种终

数据寻龙点穴（空间聚集分析） - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , Greenplum , PostGIS , K-Mean , 热力图背景最近鬼吹灯热播,胡八一的<十六字阴阳风水秘术>到底是什么武功秘籍?寻龙点穴又是什么?别问我,不知道. PS:截取自互联网.- 寻龙点穴是风水学术语.古人说:三年寻龙,十年点穴.意思就是说,学会寻龙脉要很长的时间,但要懂得点穴,并且点得准则难上加难,甚至须要用"十年"时间. 但是,若没正确方法,就是用百年时间,也不能够点中风水穴心聚气的真点,这样一来,寻龙的功夫也白费了

分区索引的应用和实践 - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , partial index , partition index 背景当表很大时,大家可能会想到分区表的概念,例如用户表,按用户ID哈希或者范围分区,拆成很多表. 又比如行为数据表,可以按时间分区,拆成很多表. 拆表的好处: 1.可以将表放到不同的表空间,表空间和块设备挂钩,例如历史数据访问量低,数据量大,可以放到机械盘所在的表空间.而活跃数据则可以放到SSD对应的表空间. 2.拆表后,方便维护,例如删除历史数据,直接DROP TABLE就可以了,不会产生REDO

机票业务(单实例 2700万行/s return)数据库架构设计 - 阿里云RDS PostgreSQL最佳实践

背景机票业务的某个模块,数据量10亿+,写.更新.删除量较低.根据KEY查询一些数据,每次查询返回1万条左右的记录. 就是这样简单的需求,业务方发现读成为了巨大的瓶颈,每次返回1万条,100个并发请求,每秒就是100万条(500MB左右),主要的瓶颈: 1.网络是个较大的开销. 2.不同KEY的数据可能是分散存放的,存在查询时的IO放大,可能有一定的性能影响. 3.每次请求的返回记录数较多,数据库search buffer调用可能开销会上升. 就这几个问题,我们来看看如何优化或解决业务方的问题

(时序业务)证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践

标签 PostgreSQL , 证券 , 时序数据 , JSON , HSTORE , 数组 , range索引 , BRIN块级索引 , 分时走势 , 线性回归 , MADlib , 机器学习背景证券行业产生的数据比较多,读写非常频繁. 以股票交易为例,一共有几千只股票.一年大概有240个交易日,交易日大概是从早上10点到下午4点. 1.数据写入需求: 实时的数据写入,按查询维度的实时数据合并(比如秒数据实时写入.分钟,几分钟,...则实时合并). 数据分为不同粒度的分时数据.(精确到秒,