PostgreSQL Similarity Search: Millisecond-level Similarity Search for Addresses, QA, POIs, and Other Text

Image for post
Image for post

Background

In real life, the similarity search is used in numerous scenarios, such as:

  • Similarly, when you search for queries or problems, your search inputs may be different from existing problems. You can only perform a similarity search to find matches.
  • Similarity search is also used to search for interest points and more.

Build Test Sample Data

Let’s take a look at the following steps required to carry out the test on similarity searches:

- Generate random Chinese strings.      
create or replace function gen_hanzi(int) returns text as $$
declare
res text;
begin
if $1 >=1 then
select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);
return res;
end if;
return null;
end;
$$ language plpgsql strict;
create unlogged table tbl(id int primary key, info text);    

alter table tbl set (parallel_workers =64);

create extension pg_trgm;
do language plpgsql $$    
declare
begin
for i in 0..63
loop
execute format('drop table if exists tbl%s ', i);
execute format('create unlogged table tbl%s (like tbl including all) inherits(tbl)', i);
-- 提前设置好表级并行度,方便后面做并行测试
execute format('alter table tbl%s set (parallel_workers =64)', i);
end loop;
end;
$$;
create or replace function conn(      
name, -- dblink名字
text -- 连接串,URL
) returns void as $$
declare
begin
perform dblink_connect($1, $2);
return;
exception when others then
return;
end;
$$ language plpgsql strict;
create extension dblink;    

do language plpgsql $$
declare
begin
for i in 0..63
loop
perform conn('link'||i, 'hostaddr=127.0.0.1 user=postgres dbname=postgres');
perform dblink_send_query('link'||i, format('insert into tbl%s select generate_series(1, 15625000), gen_hanzi(64)', i));
end loop;
end;
$$;
top - 14:49:48 up 217 days,  4:29,  3 users,  load average: 64.33, 63.08, 46.16    
Tasks: 756 total, 65 running, 691 sleeping, 0 stopped, 0 zombie
%Cpu(s): 96.5 us, 3.5 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 52807456+total, 7624988 free, 19696912 used, 50075267+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 37125398+avail Mem
postgres=# select * from tbl limit 10;    
id | info
----+----------------------------------------------------------------------------------------------------------------------------------
1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
2 | 荓嚅鑀鑬抾诐裹坲雚囻卥饸數拰絔刦霨礸诿廓琫颧仯瞱捲瘰弶瓴鹝逼倭舌飂陭盒寚芘怦敍种椡檱玠肙羡兎蒿眤粆焙蟸儌樛裦窽美影诳哜帪粊圊鈵疧
3 | 齷楣莁艪箉髎岒險旚舲瞞靻薀岹滺扡習坍敮鯭鈳鈫篖刀繹芲截孞讼咺茅讎瀝曡湓鶦戊糥钫秤彲沤熻雲筝銵妮宊鰂焜埒躐採薨銐鐚梶唕俓响寏蓘鉛緬
4 | 鶹愈篭怞迭烲调侺辖帘颬歎儨劵磘鼪痐芪踖譱梮脁翦荣蠖膹訰闥曬糦琬攀迮偳真耷獦捼臱捗玕竷肥皽羬姘癃嗗躂撴鍉垊鞵玊賮耦喞睹癦溊咺鲒薋隨
5 | 鼅崄眹狆犁妅蠝頖虼椝漮暄瓴靰湛揑屿懿浛咏螈媤蚴輦萝嵵帋諗婢閖臙姂勵奮纈睶擳最濧鵯舜鄕摎坫裠蒩洽靟颧貘鷮肋餼蓽瀌綴鑳耗棦估瘈鲿嫲竾
6 | 嚈譺勏浺勔璶歅蛰春膒遜你暖巳颿徙鲋霈鈣阣籡把琲焮钢輗牞欅谱罐頃钹欤鳑抏濸燢翓坄訇懁馠譧穗埮蒂诰哔篥繮鳷墡鋸熃篏蟵惶予单鼧翘鵗鐻鳼
7 | 骄圥浏況裸皓圣鲹炎钊睫穼祧掶腐喧鐤红恈蝷傀踗濇捶躟甜拸滒狎垎氩涭悳譸豭鮬执閐飀蓴詵炆忋搷蘼錛毞窻爘縦抌璘沙葓訍宓姊鼅籥纘囯骎鹄榢
8 | 虢謌斩髈胷廄耘毇腊釣臾柡蕙丷钛埋繝垃繣鳶跖棋壤馟栬蝉碒焚舲眱貽棯抙勀搒閐掄阪憲雎表閯弊減闦吀矦璞嶃嚤燯鵘煯糓靓讛摷灀崐颩饱鯍懳層
9 | 仨砆剏摬溋昁宕坍尋沟睨剌犟侩磫舢塎鳚翕箽稈瞂枲避駂盃覄鎎狪鵷偍珒痘咜訾陣沝韔下窨擎睳绵襭礜堺毩荪啰鶾徂腸疛礴牒澹偒就探甼娃旯鬎臛
10 | 沌薧碙謩緖碤昬钣偱霠繫箎侶鱔归圦驭烔誝灣鰈嵋鈜鹚歼嘘珰睿済潙妵貓啛葎砗蔱嵍遂稰徾螾壶赌襴喥麞銙偭濍綒狐氰賜敇櫤墳浟郕舲赧悉跧穕柤
(10 rows)
do language plpgsql $$    
declare
begin
create index idx_tbl_info on tbl using gin(info gin_trgm_ops);

for i in 0..63
loop
perform conn('link'||i, 'hostaddr=127.0.0.1 user=postgres dbname=postgres');
perform dblink_send_query('link'||i, format('create index idx_tbl%s_info on tbl%s using gin(info gin_trgm_ops);', i, i));
end loop;
end;
$$;

SQL Usage for Similarity Queries

Follow the steps listed below to implement SQL for similarity queries:

select show_limit();    
show_limit
------------
0.3
(1 row)
select set_limit(0.9);
-- 响应速度更慢    
postgres=# select set_limit(0.1);
set_limit
-----------
0.1
(1 row)

-- 响应速度更快
postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
select similarity(info, '输入搜索词') as sml, -- 计算输入词与存储字符串的相似度      * from tbl     
where info % '输入搜索词' -- 相似度超过阈值
order by sml desc -- 按相似度排序(倒排,越相似的排在越前面)
limit 10;
postgres=# select set_limit(0.7);    
set_limit
-----------
0.7
(1 row)

select similarity(info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁') as sml, - 计算输入词与存储字符串的相似度
* from tbl
where info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁' - 相似度超过阈值
order by sml desc -- 按相似度排序(倒排,越相似的排在越前面)
limit 10;

sml | id | info
------+----+----------------------------------------------------------------------------------------------------------------------------------
0.75 | 1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
(1 row)

Time: 71.627 ms
explain select similarity(info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁') as sml, - 计算输入词与存储字符串的相似度   
* from tbl
where info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁' - 相似度超过阈值
order by sml desc -- 按相似度排序(倒排,越相似的排在越前面)
limit 10;



QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1025136.11..1025137.35 rows=10 width=204)
-> Gather Merge (cost=1025136.11..1148791.31 rows=999944 width=204)
Workers Planned: 8
-> Sort (cost=1024135.97..1024448.45 rows=124993 width=204)
Sort Key: (similarity(tbl2.info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)) DESC
-> Result (cost=554.09..1021434.91 rows=124993 width=204)
-> Parallel Append (cost=554.09..1019872.50 rows=124993 width=200)
-> Parallel Bitmap Heap Scan on tbl2 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl2_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl3 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl3_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl4 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl4_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl5 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl5_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl7 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl7_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl8 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl8_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl9 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl9_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl10 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl10_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl11 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl11_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl12 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl12_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl13 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl13_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl14 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl14_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl16 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl16_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl17 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl17_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl18 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl18_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl19 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl19_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl20 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl20_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl21 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl21_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl22 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl22_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl23 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl23_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl24 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl24_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl25 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl25_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl26 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl26_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl28 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl28_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl29 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl29_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl30 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl30_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl31 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl31_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl33 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl33_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl34 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl34_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl35 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl35_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl36 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl36_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl37 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl37_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl38 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl38_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl39 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl39_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl41 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl41_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl42 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl42_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl44 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl44_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl45 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl45_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl46 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl46_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl47 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl47_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl48 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl48_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl49 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl49_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl50 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl50_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl51 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl51_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl52 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl52_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl53 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl53_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl55 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl55_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl56 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl56_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl57 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl57_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl58 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl58_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl59 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl59_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl61 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl61_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl62 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl62_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl63 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl63_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl0 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl0_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl1 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl1_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl6 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl6_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl15 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl15_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl27 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl27_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl32 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl32_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl40 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl40_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl43 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl43_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl54 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl54_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Bitmap Heap Scan on tbl60 (cost=554.09..15935.51 rows=1953 width=200)
Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Bitmap Index Scan on idx_tbl60_info (cost=0.00..550.19 rows=15625 width=0)
Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
-> Parallel Seq Scan on tbl (cost=0.00..0.00 rows=1 width=36)
Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
(265 rows)
set enable_parallel_append =on;    
set max_parallel_workers_per_gather =16;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set enable_parallel_append =on;
postgres=# select set_limit(0.7);
set_limit
-----------
0.7
(1 row)

explain select similarity(info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁') as sml, - Calculate the similarity between input words and stored strings
* from tbl
where info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁' -- 相似度超过阈值 order by sml desc -- 按相似度排序(倒排,越相似的排在越前面)
limit 10;
sml  | id |                                                               info                                                                   
------+----+----------------------------------------------------------------------------------------------------------------------------------
0.75 | 1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
(1 row)

Time: 40.298 ms

Performance Stress Testing for Similarity Queries

Create a stress test function to randomly extract a string from an existing record by using the primary key (and process it to generate a new string with a certain similarity).

从第1位开始,取28位,然后插入4个随机中文,再从29位开始取28位。这个字符串作为相似查询的输入。相似度为0.75。    

postgres=# select substring(info,1,28)||gen_hanzi(4)||substring(info,29,28) newval, info, similarity(substring(info,1,28)||gen_hanzi(4)||substring(info,29,28) , info) from tbl limit 10;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------
newval | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁
info | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
similarity | 0.75
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------
newval | 荓嚅鑀鑬抾诐裹坲雚囻卥饸數拰絔刦霨礸诿廓琫颧仯瞱捲瘰弶瓴叠濷賨櫕鹝逼倭舌飂陭盒寚芘怦敍种椡檱玠肙羡兎蒿眤粆焙蟸儌樛裦窽美
info | 荓嚅鑀鑬抾诐裹坲雚囻卥饸數拰絔刦霨礸诿廓琫颧仯瞱捲瘰弶瓴鹝逼倭舌飂陭盒寚芘怦敍种椡檱玠肙羡兎蒿眤粆焙蟸儌樛裦窽美影诳哜帪粊圊鈵疧
similarity | 0.75
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------
newval | 齷楣莁艪箉髎岒險旚舲瞞靻薀岹滺扡習坍敮鯭鈳鈫篖刀繹芲截孞熒镻缮蜝讼咺茅讎瀝曡湓鶦戊糥钫秤彲沤熻雲筝銵妮宊鰂焜埒躐採薨銐鐚
info | 齷楣莁艪箉髎岒險旚舲瞞靻薀岹滺扡習坍敮鯭鈳鈫篖刀繹芲截孞讼咺茅讎瀝曡湓鶦戊糥钫秤彲沤熻雲筝銵妮宊鰂焜埒躐採薨銐鐚梶唕俓响寏蓘鉛緬
similarity | 0.75
-- 使用随机字符串进行相似搜索(用于压测)      
create or replace function get_tbl(int) returns setof record as
$$
declare
str text;
begin
perform set_limit(0.7);

-- 从第1位开始,取28位,然后插入4个随机中文,再从29位开始取28位。这个字符串作为相似查询的输入。相似度为0.75。
select substring(info,1,28)||gen_hanzi(4)||substring(info,29,28) into str from tbl where id=$1 limit 1;

return query execute format($_$select similarity(info, %L) as sml, -- 计算输入词与存储字符串的相似度
* from tbl
where info %% %L -- 相似度超过阈值
order by sml desc -- 按相似度排序(倒排,越相似的排在越前面)
limit 10$_$, str, str);
end;
$$ language plpgsql strict;
postgres=# select * from get_tbl(1) as t(sml float4, id int, info text);    
sml | id | info
------+----+----------------------------------------------------------------------------------------------------------------------------------
0.75 | 1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
(1 row)

Time: 92.229 ms
vi test.sql    
\set id random(1,15625000)
select * from get_tbl(1) as t(sml float4, id int, info text);
-- 并行度调低    
alter role postgres set max_parallel_workers_per_gather =2;

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 51503
latency average = 149.175 ms
latency stddev = 20.054 ms
tps = 428.589421 (including connections establishing)
tps = 428.699150 (excluding connections establishing)
statement latencies in milliseconds:
0.003 \set id random(1,15625000)
149.311 select * from get_tbl(1) as t(sml float4, id int, info text);
top - 19:32:05 up 217 days,  9:11,  3 users,  load average: 38.04, 21.38, 11.92    
Tasks: 768 total, 57 running, 710 sleeping, 0 stopped, 1 zombie
%Cpu(s): 82.0 us, 12.8 sy, 0.0 ni, 4.1 id, 1.1 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 52807456+total, 11373780 free, 14563392 used, 50213737+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 35995504+avail Mem

Conclusion

Data Structure Performance Indicators

Following indicates the data structure performance:

  • GIN index creation time: 180 minutes.

Space Occupied

  • 1 billion text: 223 GB
  • Index: 332 GB

Performance Indicators

The following table shows the respective performance indicators:

Image for post
Image for post

Summary

1. PostgreSQL 11 Parallel appending significantly improves the performance.

postgres=# show enable_parallel_append ;    
enable_parallel_append
------------------------
on
(1 row)
postgres=# select show_limit();    
show_limit
------------
0.3
(1 row)

postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
postgres=# select similarity('abc','abcd');    
similarity
------------
0.5
(1 row)

postgres=# select word_similarity('abc','abcd');
word_similarity
-----------------
0.75
(1 row)

postgres=# select word_similarity('abc','abc');
word_similarity
-----------------
1
(1 row)

postgres=# select similarity('abc','abc');
similarity
------------
1
(1 row)
-- 响应速度更慢    
postgres=# select set_limit(0.1);
set_limit
-----------
0.1
(1 row)

-- 响应速度更快
postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
create or replace function get_res(  
text, -- 要按相似搜的文本
int8, -- 限制返回多少条
float4 default 0.3, -- 相似度阈值,低于这个值不再搜搜
float4 default 0.1 -- 相似度递减步长,直至阈值
) returns setof record as $$
declare
lim float4 := 1;
begin
-- 判定
if not ($3 <= 1 and $3 > 0) then
raise notice '$3 must >0 and <=1';
return;
end if;

if not ($4 > 0 and $4 < 1) then
raise notice '$4 must >0 and <=1';
return;
end if;
loop
-- 设置相似度阈值
perform set_limit(lim);

return query select similarity(info, $1) as sml, * from tbl where info % $1 order by sml desc limit $2;

-- 如果有,则退出loop
if found then
return;
end if;

-- 否则继续,降低阈值
-- 当阈值小于0.3时,不再降阈值搜索,认为没有相似。
if lim < $3 then
return;
else
lim := lim - $4;
end if;
end loop;
end;
$$ language plpgsql strict;
select * from get_res('输入搜索文本', 输入限制条数, 输入阈值, 输入步长) as t(sml float4, id int, info text);
postgres=# select * from get_res('四餧麾鄟賃青乖涢鰠揃擝垭岮操彴淒鋺約韉夗缝特鏋邜鯩垭縳墙靰禮徛亦猰庴釅恎噡鈛翱勜嘹雍岈', 10, 0.4, 0.05) as t(sml float4, id int, info text);  
sml | id | info
----------+----+----------------------------------------------------------------------------------------------------------------------------------
0.602941 | 1 | 彿睰掇贼展跃鬠唂四餧麾鄟賃青乖涢鰠揃擝垭岮操彴淒鋺約韉夗缝特鏋邜鯩垭縳墙靰禮徛亦猰庴釅恎噡鈛翱勜嘹雍岈擦寵淽蒸佊鴁糜婡籹侰亇浰鶙
(1 row)

Time: 75.957 ms
pg_trgm.similarity_threshold    

pg_trgm.word_similarity_threshold
text % text    
similarity(text, text)



text <% text
word_similarity(text, text)
postgres=# alter system set pg_trgm.similarity_threshold =0.9;    
ALTER SYSTEM

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

-- 永久生效

postgres=# show pg_trgm.similarity_threshold;
pg_trgm.similarity_threshold
------------------------------
0.9
(1 row)

UDF Segmentation for the Similarity of smlar Arrays: An Example

Step 1. Prepare plug-ins.

git clone https://github.com/jirutka/smlar  
cd smlar/
USE_PGXS=1 make
USE_PGXS=1 make install

psql
create extension smlar;
create unlogged table t (id serial primary key, arr int[]);
create or replace function gen_rand_arr(int) returns int[] as $$  
select array(select (random()*$1*5)::int from generate_series(1,$1));
$$ language sql strict;
vi test.sql  
\set i random(50,100)
insert into t(arr) values (gen_rand_arr(:i));


pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120

vacuum analyze t;
postgres=# select count(*) from t;
count
----------
19237926
(1 row)
create index idx_t_1 on t using gin (arr _int4_sml_ops);
create or replace function get_res(  
int[], -- 要按相似搜的数组
int, -- 限制返回多少条
int, -- 重叠度个数
text default 'overlap' -- 相似度算法 cosine, overlap, tfidf
) returns setof record as $$
declare
lim int := array_length($1,1);
cnt int := 0;
tmp_cnt int8 :=0;
begin
set smlar.type='overlap';
set enable_seqscan=off;
loop
-- 设置相似度阈值
perform set_smlar_limit(lim);

return query select smlar(arr,$1,'N.i') as overlap , * from t where arr % $1 limit $2;
-- 一次性查询: select set_smlar_limit($3); select smlar(arr,$1,'N.i') as overlap , * from t where arr % $1 order by overlap desc limit $2;

-- 如果有,则退出loop
if found then
GET DIAGNOSTICS tmp_cnt = ROW_COUNT;
cnt := cnt + tmp_cnt;
if cnt >= $2 then
return;
end if;
end if;

-- 否则继续,降低阈值
-- 当阈值小于$3时,不再降阈值搜索,认为没有相似。
if lim < $3 then
return;
else
lim := lim - 1;
end if;
end loop;
end;
$$ language plpgsql strict;
select * from get_res(array[104,1,367,174,462,285,125,122,337,167,437,276,96,134,9,421,436,135,420,114,138,27,431,350,386,474,6,416,302,2,22,406,3,389,100,464,194,225,106,50,391,64,326,7,198,335],
10,
15)
as t (overlap real, id int, arr int[]);


select set_smlar_limit(25);
select smlar(arr,array[104,1,367,174,462,285,125,122,337,167,437,276,96,134,9,421,436,135,420,114,138,27,431,350,386,474,6,416,302,2,22,406,3,389,100,464,194,225,106,50,391,64,326,7,198,335],'N.i') as overlap , *
from t where arr % array[104,1,367,174,462,285,125,122,337,167,437,276,96,134,9,421,436,135,420,114,138,27,431,350,386,474,6,416,302,2,22,406,3,389,100,464,194,225,106,50,391,64,326,7,198,335]
order by overlap desc limit 10;

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store