Greenplum 2000亿 近似度查询 性能 以及注意事项

greenplum和PostgreSQL一样，都是通过pg_trgm来支持近似度查询的。
原理是将字符串前加2空格，末尾加1空格，然后按照3个连续的字符串为一组，打散成多个字符串。然后计算字符串的重复度来计算两个字符串的相似度。
计算重复度时，需要进行去重复的操作。
例如:

postgres=# select similarity('abcde','abcabc');
 similarity
------------
   0.333333
(1 row)
Time: 0.413 ms

以上两个字符串被拆分成如下token(以下-代表空格)
--a, -ab, abc, bcd, cde, de-
--a, -ab, abc, bca, cab, abc, bc-
两者token去重后的集合为
--a, -ab, abc, bcd, cde, de-, bca, cab, bc-
重复的token为
--a, -ab, abc
所以abcde 和 abcabc 的近似度=3/9=0.333333

greenplum安装pg_trgm也很简单。

cd gpsrc/contrib/pg_trgm/

现在有个bug需要手工fix一下

vi trgm.h
#define TRGMINT(a) ( (*(((char*)(a))+2)<<16)+(*(((char*)(a))+1)<<8)+*(((char*)(a))+0) )  

make && make install  

gpscp -f ./host /home/digoal/gphome/lib/postgresql/pg_trgm.so =:/home/digoal/gphome/lib/postgresql/pg_trgm.so
gpscp -f ./host /home/digoal/gphome/share/postgresql/contrib/uninstall_pg_trgm.sql =:/home/digoal/gphome/share/postgresql/contrib/uninstall_pg_trgm.sql
gpscp -f ./host /home/digoal/gphome/share/postgresql/contrib/pg_trgm.sql =:/home/digoal/gphome/share/postgresql/contrib/pg_trgm.sql  

psql -f /home/digoal/gphome/share/postgresql/contrib/pg_trgm.sql

测试

postgres=# create table t(info text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'info' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# insert into t select md5(random()::text) from generate_series(1,100);
INSERT 0 100
postgres=# select * from t limit 10;
               info
----------------------------------
 2d33d6379b85eb7a3c4090dce7a0ebe2
 75b4be956b90f8a8c528f847eddd34fe
 ae7f1bb726486fba174cfc27a90ea080
 b714894688f9ef9272c61d09efebb361
 d8774ded2bad3c4aafb85cc98fea7d06
 afdc717a7e4c73e22a497db9c2812bfa
 eda761ac73f659072ae2084268d5f2fe
 e2660e7b3a9a7824611c4af93bc2c4d9
 8659bdb87b3f5d3e6d7f269233e12d4b
 fd28ec09a46d2f35b3b3461ab48d1998
(10 rows)

当前的近似度阈值为0.3，当两个字符串的近似度小于0.3时，返回false。

postgres=# select show_limit();
 show_limit
------------
        0.3
(1 row)  

postgres=# select '2d33d6379b85eb7a3c4090dce7a0ebe2' % 'eb7a3c409';
 ?column?
----------
 f
(1 row)  

postgres=# select * from t where info % 'eb7a3c409';
 info
------
(0 rows)

使用set_limit可以设置近似度阈值

postgres=# select set_limit(0.1);
 set_limit
-----------
       0.1
(1 row)  

postgres=# select '2d33d6379b85eb7a3c4090dce7a0ebe2' % 'eb7a3c409';
 ?column?
----------
 t
(1 row)  

postgres=# select * from t where info % 'eb7a3c409';
 info
------
(0 rows)

为什么查询表的记录时没有起作用呢？
原因是set_limit()函数没有在segment上执行，它们还是0.3：

postgres=# select show_limit() from gp_dist_random('gp_id');
 show_limit
------------
        0.3
        0.3
        0.3
        0.3
    ......

通过gp_dist_random强制在segment执行,

postgres=# select set_limit(0.1) from gp_dist_random('gp_id');
 set_limit
-----------
       0.1
       0.1
       0.1
       ......

postgres=# select * from t where info % 'eb7a3c409';
               info
----------------------------------
 2d33d6379b85eb7a3c4090dce7a0ebe2
(1 row)

由于GP有会话缓存，释放后，又需要重新和segment建立连接，这时又回到0.3了。

postgres=# select * from t where info % 'eb7a3c409';
 info
------
(0 rows)
postgres=# select show_limit() from gp_dist_random('gp_id');
 show_limit
------------
        0.3
        0.3
        0.3
        0.3
    ......

以上就是greenplum的近似度查询的用法。
还支持索引哦 :
索引不受set_limit的影响，也就是说索引中不存储固定的limit值，是随时可调整的。

create index idx on t using gist (info gist_trgm_ops);

目前还不支持GIN，因为GP的GIN索引在AO表的使用方面有问题，存在同步的问题，可能导致数据不一致。

src/backend/commands/indexcmds.c
..

        /* MPP-9329: disable creation of GIN indexes */
        if (accessMethodId == GIN_AM_OID)
                ereport(ERROR,
                                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                 errmsg("GIN indexes are not supported")));
..

预知2000亿的近似度查询性能，明天放出。

postgres=# select count(distinct info),count(*) from t_regexp_100billion ;
   count    |    count
------------+--------------
 2147475713 | 212600000000
(1 row)  

postgres=# explain select ctid,* from t_regexp_100billion where info >='3347597ec8' and info<'3347597ec9' and info like '3347597ec8%' limit 5;
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..2479.65 rows=5 width=19)
   ->  Gather Motion 240:1  (slice1; segments: 240)  (cost=0.00..2479.65 rows=5 width=19)
         ->  Limit  (cost=0.00..2479.55 rows=1 width=19)
               ->  Index Scan using idx_1 on t_regexp_100billion  (cost=0.00..396651039.36 rows=3333 width=19)
                     Index Cond: info >= '3347597ec8'::text AND info < '3347597ec9'::text AND info >= '3347597ec8'::text AND info < '3347597ec9'::text
                     Filter: info ~~ '3347597ec8%'::text
 Settings:  enable_seqscan=off
(7 rows)
Time: 55.146 ms  

postgres=# select ctid,* from t_regexp_100billion where info >='3347597ec8' and info<'3347597ec9' and info like '3347597ec8%' limit 5;
     ctid     |     info
--------------+--------------
 (663830,524) | 3347597ec812
 (704622,147) | 3347597ec812
 (682224,472) | 3347597ec812
 (644991,150) | 3347597ec812
 (667081,662) | 3347597ec812
(5 rows)
Time: 57.635 ms  

postgres=# explain select ctid,* from t_regexp_100billion where info >='3347597' and info<'3347598' and reverse(info)>='218c' and reverse(info)<'218d' and info like '3347597%c812' limit 5;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.01..304985.33 rows=5 width=19)
   ->  Gather Motion 240:1  (slice1; segments: 240)  (cost=0.01..304985.33 rows=5 width=19)
         ->  Limit  (cost=0.01..304985.23 rows=1 width=19)
               ->  Index Scan using idx_2 on t_regexp_100billion  (cost=0.01..99181639.41 rows=7 width=19)
                     Index Cond: reverse(info) >= '218c'::text AND reverse(info) < '218d'::text
                     Filter: info >= '3347597'::text AND info < '3347598'::text AND info ~~ '3347597%c812'::text
 Settings:  enable_seqscan=off
(7 rows)
Time: 55.338 ms  

postgres=# select ctid,* from t_regexp_100billion where info >='3347597' and info<'3347598' and reverse(info)>='218c' and reverse(info)<'218d' and info like '3347597%c812' limit 5;
     ctid     |     info
--------------+--------------
 (704622,147) | 3347597ec812
 (731733,400) | 3347597ec812
 (774593,650) | 3347597ec812
 (739526,433) | 3347597ec812
 (779749,565) | 3347597ec812
(5 rows)
Time: 104.845 ms
时间： 2024-11-13 04:27:49

Greenplum 2000亿 近似度查询 性能 以及注意事项的相关文章

惊天性能！单实例RDS PostgreSQL 支撑 2000亿 实时标签透视案例

标签 PostgreSQL , varbitx , 标签 , 任意圈选 , 阿里云 , RDS , bitmap , 分段 , 并行计算 , 异步调用 , dblink , 异步dblink , 游标 背景 20亿用户,每个用户1000个标签,基于任意标签组合圈选.透视(业务上的需求是一次最多计算100个标签的组合). 相当于要处理2000亿记录. 1.实时求标签组合的记录数.(即满足标签组合的用户有多少) 2.用户ID.(级满足标签组合的用户ID.) 要求实时响应. 通常你肯定会想,这个至少需

PostgreSQL 百亿地理位置数据 近邻查询性能

背景 本文主要要展示的是PostgreSQL在位置信息近邻(KNN)查询方面的性能. 测试类型point,索引类型GiST. (PostGIS同样支持KNN查询,性能和本文的测试差不多)  测试数据量大于100亿. 结果 64个并发,随机选点,单次KNN查询请求的平均响应时间为0.848毫秒. 测试环境和优化请参考 http://blog.163.com/digoal@126/blog/static/16387704020160941345888/ 创建测试表 postgres=# create

使用查询改写提高查询性能

性能 无需改变SQL查询就可以大幅提高查询性能. 你是否为等待你的查询返回结果而感到疲惫?你是否已经为增强索引和调优SQL而感到疲惫,但仍然不能提高查询性能?那么,你是否已经考虑创建物化视图?有了物化视图,那些过去需要数小时运行的报告可以在几分钟内完成.物化视图可以包括联接(join)和集合(aggregate),它提供了一种储存预计算结果的方法. 在执行一个查询时,优化器会判定访问物化视图或数据驻留的基础表是否更快一些.如果优化器判定查询物化视图是更好的解决方案,那么优化器会在一个被称为"查询

利用Oracle执行计划机制提高查询性能

oracle|性能|执行 消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分.但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间数量,并且能在大体上提高查询性能. 准备执行SQL语句 当SQL语句进入Oracle的库缓存后,在该语句准备执行之前,将执行下列步骤: 1) 语法检查:检查SQL语句拼写是否正确和词序. 2) 语义分析:核实所有的与数据字典不一致的表和列的名字. 3) 轮

用 SQL Server 2000 索引视图提高性能

server|视图|索引|性能 什么是索引视图? 许多年来,Microsoft SQL Server 一直都提供创建虚拟表(称为视图)的功能.在过去,这些视图主要有两种用途: 提供安全机制,将用户限制在一个或多个基表中的数据的某个子集. 提供一种机制,允许开发人员定制用户如何才能以逻辑方式查看存储在基表中的数据. SQL Server 2000 已经扩展了 SQL Server 视图的功能,以提高系统性能.它可以在一个视图上创建唯一的群集索引和非群集索引,可以改进最复杂查询的数据访问性能.在 S

使用SQL Server 2000索引视图提高性能

本文介绍 SQL Server 2000 企业版的新功能 - 索引视图.讲解索引视图并讨论一些提高性能的具体方案. 什么是索引视图? 许多年来,Microsoft SQL Server 一直都提供创建虚拟表(称为视图)的功能.在过去,这些视图主要有两种用途: 提供安全机制,将用户限制在一个或多个基表中的数据的某个子集. 提供一种机制,允许开发人员定制用户如何才能以逻辑方式查看存储在基表中的数据. SQL Server 2000 已经扩展了 SQL Server 视图的功能,以提高系统性能.它可以

Solr与MySQL查询性能对比

本文简单对比下Solr与MySQL的查询性能速度. 测试数据量:10407608     Num Docs: 10407608 这里对MySQL的查询时间都包含了从MySQL Server获取数据的时间. 在项目中一个最常用的查询,查询某段时间内的数据,SQL查询获取数据,30s左右 SELECT * FROM `tf_hotspotdata_copy_test` WHERE collectTime BETWEEN '2014-12-06 00:00:00' AND '2014-12-10 21

Solr全文搜索与MySQL查询性能比较

测试数据量:10407608Num Docs: 10407608 在项目中一个最常用的查询,查询某段时间内的数据,SQL查询获取数据,30s左右 SELECT * FROM `tf_hotspotdata_copy_test` WHERE collectTime BETWEEN '2014-12-06 00:00:00' AND '2014-12-10 21:31:55'; 对collectTime建立索引后,同样的查询,2s,快了很多. Solr索引 Solr查询,同样的条件,72ms "st

MongoDB查询性能优化验证及验证_MongoDB

结论: 1. 200w数据,合理使用索引的情况下,单个stationId下4w数据.mongodb查询和排序的性能理想,无正则时client可以在600ms+完成查询,qps300+.有正则时client可以在1300ms+完成查询,qps140+. 2. Mongodb的count性能比较差,非并发情况下client可以在330ms完成查询,在并发情况下则需要1-3s.可以考虑估算总数的方法,http://blog.sina.com.cn/s/blog_56545fd30101442b.htm