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
