Greenplum模糊查询实践标签
PostgreSQL , Greenplum , orafunc , 阿⾥云HybridDB for PostgreSQL , reverse , like , 模糊查询背景
⽂本搜索的需求分为：
1、全匹配，如：
select * from table where column = 'xxxx';
2、后模糊，如：
select * from table where column like 'xxxx%';
3、前模糊，如：
select * from table where column like '%xxxx';
4、前后模糊，如：
select * from table where column like '%xxxx%';
5、正则，如：
select * from table where column ~ 'abc[he|ww]{1,3}.?[\d]*';
6、相似，如：
select * from table where similar(column, 'postgresql');
7、短⽂向量相似，如：
select * from table where column % array['x1','x2',''''];
8、全⽂检索，如：
select * from table where column @@ to_tsquery('zhongguo & hello');
9、特征匹配搜索，如：
select * from table where column op array['',......];  -- 涉及到⽂本的关系、知识图谱、机器学习的领域
以及忽略⼤⼩写的搜索。。。。。
其中模糊查询最为常见。下⾯分享⼀下在Greenplum数据库中，如何更好的实现前、后模糊搜索。
创建⽀持反转查询的插件
create extension orafunc;
构建测试数据
1、建表
postgres=> create table test1(id int, info text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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
2、写⼊测试数据
postgres=> insert into test1 select id, md5(random()::text) from generate_series(1,10000000) t(id);
INSERT 0 10000000
3、创建正向搜索和反向搜索的索引
postgres=> create index idx1 on test1(info);
CREATE INDEX
create index idx2 on test1(reverse(info));
4、数据样本
postgres=> select * from test1 limit 10;
id |              info
----+----------------------------------
3 | ab66abe2d548eb1f21cdb410e27c43a6
7 | b4717483def19ec9426548a452b190e0
greenplum数据库11 | 1bf7dfa6205b19337c486b8a3ac1981f
15 | 68bc077b0283e29db0516e90c7a9ae49
19 | 6171f25d1b3306f794fa508ae72f2f2f
23 | 7f8e50c0a2a1114816afd93f36585715
27 | 0d1b246c9b35b199512c500617f011b8
31 | 359a18646f95daa28ae8070a73b9b2bf
35 | f0786c5efdc526a3aab79479e5c65e83
39 | e6fac18e0a464487ef72a55cbbce3ca4
(10 rows)
5、创建模糊查询依赖的函数（求前缀或后缀的下⼀个边界值）。
postgres=> create or replace function next_str(text) returns text as $$
select lpad($1, length($1)-1) || chr(ascii(substring($1, length($1), 1))+1);
$$ language sql strict immutable;
CREATE FUNCTION
6、后模糊查询（提供前缀）。
postgres=> explain analyze select * from test1 where info >= 'ab66abe' and info < next_str('ab66abe');
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..2085583.56 rows=98944 width=37)
Rows out:  1 rows at destination with 1.721 ms to first row, 1.842 ms to end, start offset by 0.214 ms.
->  Index Scan using idx1 on test1  (cost=0.00..2085583.56 rows=24736 width=37)
Index Cond: info >= 'ab66abe'::text AND info < 'ab66abf'::text
Rows out:  1 rows (seg0) with 0.034 ms to first row, 0.036 ms to end, start offset by 1.971 ms.
Slice statistics:
(slice0)    Executor memory: 159K bytes.
(slice1)    Executor memory: 145K bytes avg x 4 workers, 145K bytes max (seg0).
Statement statistics:
Memory used: 2047000K bytes
Settings:  effective_cache_size=8GB; enable_bitmapscan=off; enable_seqscan=off; gp_statistics_use_fkeys=on
Optimizer status: legacy query optimizer
Total runtime: 2.182 ms
(13 rows)
7、前模糊查询（提供后缀）。
postgres=> explain analyze select * from test1 where reverse(info) >= 'e4495c' and reverse(info) < next_str('e4495c');
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Gather Motion 4:1  (slice1; segments: 4)  (cost=0.01..2085704.88 rows=100010 width=37)
Rows out:  1 rows at destination with 1.694 ms to first row, 1.768 ms to end, start offset by 0.224 ms.
->  Index Scan using idx2 on test1  (cost=0.01..2085704.88 rows=25003 width=37)
Index Cond: reverse(info) >= 'e4495c'::text AND reverse(info) < 'e4495d'::text
Rows out:  1 rows (seg1) with 0.034 ms to first row, 0.037 ms to end, start offset by 1.844 ms.
Slice statistics:
(slice0)    Executor memory: 159K bytes.
(slice1)    Executor memory: 151K bytes avg x 4 workers, 151K bytes max (seg0).
Statement statistics:
Memory used: 2047000K bytes
Settings:  effective_cache_size=8GB; enable_bitmapscan=off; enable_seqscan=off; gp_statistics_use_fkeys=on
Optimizer status: legacy query optimizer
Total runtime: 2.220 ms
(13 rows)
使⽤这种⽅法，⽣成10亿⾝份证信息，前后模糊查询，性能杠杠的。5毫秒内返回。
建议
建议，这类查询还是属于OLTP的范畴，如果数据量在单实例可以存下的范围内，建议还是使⽤PostgreSQL。例如阿⾥云PolarDB for PostgreSQL，可以COVER 100TB的容量规格，同时⽀持⼀写多读的架构。
同时，PostgreSQL还可以通过pg_trgm与GIN索引⽀持全模糊、正则、字符串相似、短⽂特征向量相似的搜索，⽐Greenplum在本case⽀持的范畴更加⼴泛。
参考

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。