开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 多字段任意组合搜索的性能

简介:
+关注继续查看

标签

PostgreSQL , 多字段搜索 , 任意字段搜索


背景

PostgreSQL 多字段,任意组合搜索,有三种优化手段:

1、gin索引(支持任意字段组合的查询)

《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树 (GIN)》

2、bloom索引(支持任意只读组合的等值查询)

《PostgreSQL 9.6 黑科技 bloom 算法索引,一个索引支撑任意列组合查询》

3、每个单列btree索引(支持任意字段组合的查询)

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

例子

create table test(c1 int, c2 int, c3 int, c4 int, c5 int);  

bloom, gin, multi-btree几种索引创建方法

1、bloom

postgres=# create extension bloom ;  
CREATE EXTENSION  
postgres=# create index idx_test12_1 on test12 using bloom (c1,c2,c3,c4,c5);  
CREATE INDEX  
postgres=# explain select * from test12 where c1=1;  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=13.95..20.32 rows=8 width=20)  
   Recheck Cond: (c1 = 1)  
   ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)  
         Index Cond: (c1 = 1)  
(4 rows)  
postgres=# explain select * from test12 where c1=1 and c2=1;  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=18.20..19.42 rows=1 width=20)  
   Recheck Cond: ((c1 = 1) AND (c2 = 1))  
   ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..18.20 rows=1 width=0)  
         Index Cond: ((c1 = 1) AND (c2 = 1))  
(4 rows)  
postgres=# explain select * from test12 where c1=1 or c2=1;  
                                    QUERY PLAN                                      
----------------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=27.91..38.16 rows=17 width=20)  
   Recheck Cond: ((c1 = 1) OR (c2 = 1))  
   ->  BitmapOr  (cost=27.91..27.91 rows=17 width=0)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)  
               Index Cond: (c1 = 1)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)  
               Index Cond: (c2 = 1)  
(7 rows)  

2、gin

postgres=# create extension btree_gin;  
CREATE EXTENSION  
postgres=# create index idx_test12_1 on test12 using gin (c1,c2,c3,c4,c5);  
CREATE INDEX  
postgres=# explain select * from test12 where c1=1 or c2=1;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=4.94..15.19 rows=17 width=20)  
   Recheck Cond: ((c1 = 1) OR (c2 = 1))  
   ->  BitmapOr  (cost=4.94..4.94 rows=17 width=0)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..2.46 rows=8 width=0)  
               Index Cond: (c1 = 1)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..2.46 rows=8 width=0)  
               Index Cond: (c2 = 1)  
(7 rows)  
  
postgres=# explain select * from test12 where c1=1 and c2=1;  
                                QUERY PLAN                                   
---------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=3.60..4.82 rows=1 width=20)  
   Recheck Cond: ((c1 = 1) AND (c2 = 1))  
   ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..3.60 rows=1 width=0)  
         Index Cond: ((c1 = 1) AND (c2 = 1))  
(4 rows)  

3、multi-btree

postgres=# drop index idx_test12_1 ;  
DROP INDEX  
postgres=# create index idx_test12_1 on test12 using btree(c1);  
CREATE INDEX  
postgres=# create index idx_test12_2 on test12 using btree(c2);  
CREATE INDEX  
postgres=# create index idx_test12_3 on test12 using btree(c3);  
CREATE INDEX  
postgres=# create index idx_test12_4 on test12 using btree(c4);  
CREATE INDEX  
postgres=# create index idx_test12_5 on test12 using btree(c5);  
CREATE INDEX  
  
postgres=# explain select * from test12 where c1=1 and c2=1;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=3.08..4.29 rows=1 width=20)  
   Recheck Cond: ((c2 = 1) AND (c1 = 1))  
   ->  BitmapAnd  (cost=3.08..3.08 rows=1 width=0)  
         ->  Bitmap Index Scan on idx_test12_2  (cost=0.00..1.41 rows=8 width=0)  
               Index Cond: (c2 = 1)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..1.41 rows=8 width=0)  
               Index Cond: (c1 = 1)  
(7 rows)  
  
postgres=# explain select * from test12 where c1=1 or c2=1;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Bitmap Heap Scan on test12  (cost=2.83..13.09 rows=17 width=20)  
   Recheck Cond: ((c1 = 1) OR (c2 = 1))  
   ->  BitmapOr  (cost=2.83..2.83 rows=17 width=0)  
         ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..1.41 rows=8 width=0)  
               Index Cond: (c1 = 1)  
         ->  Bitmap Index Scan on idx_test12_2  (cost=0.00..1.41 rows=8 width=0)  
               Index Cond: (c2 = 1)  
(7 rows)  

gin, bloom, btree bitmap scan的性能如何呢?

1600个列的宽表,任意字段组合搜索性能

1、建表

postgres=# do language plpgsql $$  
declare  
  sql text;  
begin  
  sql := 'create table test1 (';  
  for i in 1..1600 loop  
    sql := sql||' c'||i||' int2 default random()*100,';  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;   
    
  for i in 1..1600 loop   
    execute 'create index idx_test1_'||i||' on test1 (c'||i||')';   
  end loop;  
end;  
$$;  
DO  

2、写入测试数据

postgres=# insert into test1 (c1)  select generate_series(1,10000);  
INSERT 0 10000  

3、测试脚本

vi test.sql  
  
\set c2 random(1,100)  
\set c3 random(1,100)  
\set c4 random(1,100)  
\set c5 random(1,100)  
\set c6 random(1,100)  
\set c7 random(1,100)  
select c1600 from test1 where c2=:c2 and c3=:c3 and c4=:c4 or (c5=:c5 and c6=:c6 and c7=:c7);  

4、测试

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120  

5、性能

progress: 33.0 s, 208797.8 tps, lat 0.307 ms stddev 0.016  
progress: 34.0 s, 208516.0 tps, lat 0.307 ms stddev 0.032  
progress: 35.0 s, 208574.0 tps, lat 0.307 ms stddev 0.050  
progress: 36.0 s, 208858.2 tps, lat 0.306 ms stddev 0.013  
progress: 37.0 s, 208686.8 tps, lat 0.307 ms stddev 0.043  
progress: 38.0 s, 208764.2 tps, lat 0.307 ms stddev 0.013  

注意,使用prepared statement,可以减少硬解析,提高性能。

从测试来看,任意字段的搜索,可以达到0.3毫秒的响应。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云RDS金融数据库(三节点版) - 性能篇
标签 PostgreSQL , MySQL , 三节点版 , 金融数据库 , Raft , 分布式共享存储版 背景 终于到了性能篇,三节点同时满足了企业对数据库的可用性、可靠性的要求,那么性能如何呢? 提到性能测试,我有几点一定要说明一下,很多朋友对性能的理解可能有偏差,那么如何评判性能
4785 0
阿里云RDS金融数据库(三节点版) - 背景篇
标签 PostgreSQL , MySQL , 三节点版 , 金融数据库 , Raft , 分布式共享存储版 背景 提到金融级数据库,大家可能不约而同的会想到Oracle,DB2等商业数据库。
4466 0
阿里云RDS金融数据库(三节点版) - 理论篇
标签 PostgreSQL , MySQL , 三节点版 , 金融数据库 , Raft , 分布式共享存储版 背景 《阿里云RDS金融数据库(三节点版) - 背景篇》说明了为什么需要推出金融级数据库的三节点版本,以及三节点引入的一个世界难题 - 拜占庭将军问题。
4939 0
性能测试:自建数据库与RDS性能对比SQL Server案例排查分析
近期经常遇到用户将自建数据库与RDS进行对比,简单的对比结果是自建库比RDS实例查询快。我们这里来看看一个实例,有一家物流公司,刚开始使用RDS SQL Server数据库,发现通过ECS访问RDS实例,执行语句需要60s左右,但是访问ECS本地自建库只需要2-3s。那么RDS是否是真的不如自建数据库呢? 接下来,我们来探讨对比自建库和RDS的正确姿势,如何公平地对比自建库和RDS的性能。对比自建
2980 0
玩转数据库新功能及应用—MySQL for RDS 新功能及应用场景
3月27日至29日,云栖大会于深圳举行。本次深圳带来了“安全&出海”两大主题,在3月28日闭门分享沙龙会场上,游戏行业专家针对“全球同服”、“阿里云数据库新特性”、“大数据运营方案”、“游戏安全保障”进行了深入沟通。
2902 0
性能测试:自建数据库对比RDS中应当注意的地方(适用于MySQL,SQL SERVER,MongoDB)
常常很多用户对比测试自建数据库和RDS的性能差异,其测试结果往往是RDS不如ECS自建,用户往往怀疑难道我花了那么多的钱买的RDS难道还不如自己在ECS上搭建吗?
16261 0
创建RDS到分析型数据库AnalyticDB数据实时同步
数据传输服务DTS支持RDS到分析型数据库AnalyticDB之间的数据实时同步。通过DTS提供的RDS->AnalyticDB实时同步功能,可以将将企业数据集中分析,实现企业内部各种BI、CRM、实时报表系统等。
3308 0
RDS for MySQL 备份文件恢复到本地数据库
RDS for MySQL 备份文件恢复到本地数据库
6450 0
通过excel的VBA 是否可以 直接访问阿里云的RDS数据库(SQL Server)
通过excel的VBA 直接访问阿里云的云数据库(SQL Server)
3656 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
PolarDB for PostgreSQL三节点功能介绍
立即下载
认识PostgreSQL中与众不同的索引
立即下载
金融级 PostgreSQL监控及优化
立即下载