Greenplum 模糊查询实践

2 minute read

背景

文本搜索的需求分为：

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');

《PostgreSQL 行级全文检索》

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                 
----+----------------------------------  
| ab66abe2d548eb1f21cdb410e27c43a6  
| b4717483def19ec9426548a452b190e0  
| 1bf7dfa6205b19337c486b8a3ac1981f  
| 68bc077b0283e29db0516e90c7a9ae49  
| 6171f25d1b3306f794fa508ae72f2f2f  
| 7f8e50c0a2a1114816afd93f36585715  
| 0d1b246c9b35b199512c500617f011b8  
| 359a18646f95daa28ae8070a73b9b2bf  
| f0786c5efdc526a3aab79479e5c65e83  
| 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)  

《PostgreSQL 生成随机身份证ID》

使用这种方法，生成10亿身份证信息，前后模糊查询，性能杠杠的。5毫秒内返回。