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

《中文模煳查詢性能優化 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                 
----+----------------------------------  
  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)

《PostgreSQL 生成隨機身份證ID》

使用這種方法，生成10億身份證信息，前後模煳查詢，性能杠杠的。5毫秒內返回。