标签
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%';
《中文模糊查询性能优化 by PostgreSQL trgm》
5、正则，如：
select * from table where column ~ 'abc[he|ww]{1,3}.?[\d]*';
《中文模糊查询性能优化 by PostgreSQL trgm》
6、相似，如：
select * from table where similar(column, 'postgresql');
《中文模糊查询性能优化 by PostgreSQL trgm》
7、短文向量相似，如：
select * from table where column % array['x1','x2',''''];
《海量数据,海明(simhash)距离高效检索(smlar) - 阿里云RDS PosgreSQL最佳实践》
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
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支持的范畴更加广泛。
参考
《PostgreSQL 全表 全字段 模糊查询的毫秒级高效实现 - 搜索引擎颤抖了》
《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
《中文模糊查询性能优化 by PostgreSQL trgm》
《PostgreSQL 百亿数据 秒级响应 正则及模糊查询》
《聊一聊双十一背后的技术 - 毫秒分词算啥, 试试正则和相似度》
