In real life, the similarity search is used in numerous scenarios, such as:
PostgreSQL supports similarity search, including similarity search for images, arrays, and text. For text, you can use the pg_trgm plug-in to implement a similarity search. It is a test of strings with regular expressions of the magnitude of 10 billion (a combination of 32 random letters and arrays in each row) and fuzzy queries in milliseconds.
Similarity queries use the same plug-in and index. This article describes tests on random Chinese characters and similarity searches to see how PostgreSQL standalone performs.
Let's take a look at the following steps required to carry out the test on similarity searches:
Step 1. Create a function to generate random Chinese characters.
- 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;
Step 2. Use partition tables to improve write and query performance.
Note: The preceding partition method is not used for testing purposes in this article.
Step 3. Now, create a parent table. In order to accelerate the import process, use an unlogged table here. Do not use it if it is generated.
create unlogged table tbl(id int primary key, info text);
alter table tbl set (parallel_workers =64);
create extension pg_trgm;
Step 4. Create 64 sub-tables.
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;
$$;
Step 5. Next, write 1 billion pieces of test data into the partition. The quick writing method is as follows. It uses DBLink to asynchronously call parallel loading.
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;
There are 64 partitions with 64 random Chinese characters in each row. Each partition has 15,625,000 written rows. Therefore, there are 1 billion rows inserted in total.
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;
$$;
This kind of parallel writing method uses the full CPU to write 1 billion random texts at a high speed.
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
Once the writing is complete, there will be 1 billion records. The table occupies 223 GB of space, and it takes 18 minutes to write records. The sample is as follows.
postgres=# select * from tbl limit 10;
id | info
----+----------------------------------------------------------------------------------------------------------------------------------
1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
2 | 荓嚅鑀鑬抾诐裹坲雚囻卥饸數拰絔刦霨礸诿廓琫颧仯瞱捲瘰弶瓴鹝逼倭舌飂陭盒寚芘怦敍种椡檱玠肙羡兎蒿眤粆焙蟸儌樛裦窽美影诳哜帪粊圊鈵疧
3 | 齷楣莁艪箉髎岒險旚舲瞞靻薀岹滺扡習坍敮鯭鈳鈫篖刀繹芲截孞讼咺茅讎瀝曡湓鶦戊糥钫秤彲沤熻雲筝銵妮宊鰂焜埒躐採薨銐鐚梶唕俓响寏蓘鉛緬
4 | 鶹愈篭怞迭烲调侺辖帘颬歎儨劵磘鼪痐芪踖譱梮脁翦荣蠖膹訰闥曬糦琬攀迮偳真耷獦捼臱捗玕竷肥皽羬姘癃嗗躂撴鍉垊鞵玊賮耦喞睹癦溊咺鲒薋隨
5 | 鼅崄眹狆犁妅蠝頖虼椝漮暄瓴靰湛揑屿懿浛咏螈媤蚴輦萝嵵帋諗婢閖臙姂勵奮纈睶擳最濧鵯舜鄕摎坫裠蒩洽靟颧貘鷮肋餼蓽瀌綴鑳耗棦估瘈鲿嫲竾
6 | 嚈譺勏浺勔璶歅蛰春膒遜你暖巳颿徙鲋霈鈣阣籡把琲焮钢輗牞欅谱罐頃钹欤鳑抏濸燢翓坄訇懁馠譧穗埮蒂诰哔篥繮鳷墡鋸熃篏蟵惶予单鼧翘鵗鐻鳼
7 | 骄圥浏況裸皓圣鲹炎钊睫穼祧掶腐喧鐤红恈蝷傀踗濇捶躟甜拸滒狎垎氩涭悳譸豭鮬执閐飀蓴詵炆忋搷蘼錛毞窻爘縦抌璘沙葓訍宓姊鼅籥纘囯骎鹄榢
8 | 虢謌斩髈胷廄耘毇腊釣臾柡蕙丷钛埋繝垃繣鳶跖棋壤馟栬蝉碒焚舲眱貽棯抙勀搒閐掄阪憲雎表閯弊減闦吀矦璞嶃嚤燯鵘煯糓靓讛摷灀崐颩饱鯍懳層
9 | 仨砆剏摬溋昁宕坍尋沟睨剌犟侩磫舢塎鳚翕箽稈瞂枲避駂盃覄鎎狪鵷偍珒痘咜訾陣沝韔下窨擎睳绵襭礜堺毩荪啰鶾徂腸疛礴牒澹偒就探甼娃旯鬎臛
10 | 沌薧碙謩緖碤昬钣偱霠繫箎侶鱔归圦驭烔誝灣鰈嵋鈜鹚歼嘘珰睿済潙妵貓啛葎砗蔱嵍遂稰徾螾壶赌襴喥麞銙偭濍綒狐氰賜敇櫤墳浟郕舲赧悉跧穕柤
(10 rows)
Step 6. Create an index to accelerate the generation speed. In actual production, you can also create an index first.
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;
$$;
There are 1 billion records. GIN inverted indexes occupy 332 GB of space, and it takes nearly 180 minutes to create indexes.
The index creation speed is explained as follows.
The strings written in this case are totally random Chinese characters from tens of thousands of Chinese characters. Therefore, there are a lot of tokens for GIN inverted indexes. In this case, the index is much larger than the index of normal production data. The normal index is not that large, and the normal creation speed is faster than this. However, the creation speed of a GIN index is much slower than that of a BTREE index (which is caused by the structure). You can create a BTREE index within 5 minutes.
Follow the steps listed below to implement SQL for similarity queries:
Step 1. View the current similarity threshold.
select show_limit();
show_limit
------------
0.3
(1 row)
Step 2. Set similarity thresholds for the current session. For other settings, refer to the end of the section.
select set_limit(0.9);
Step 3. The response speed of a similarity search is related to the similarity set by the user. The more the matched values, the slower the response speed. The smaller the matched value (that is, the higher the precision), the faster the response speed.
-- 响应速度更慢
postgres=# select set_limit(0.1);
set_limit
-----------
0.1
(1 row)
-- 响应速度更快
postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
Step 4. Now, query the similarity text based on input text and get the output in a similar order.
select similarity(info, '输入搜索词') as sml, -- 计算输入词与存储字符串的相似度 * from tbl
where info % '输入搜索词' -- 相似度超过阈值
order by sml desc -- 按相似度排序(倒排，越相似的排在越前面)
limit 10;
SQL Time: 71 ms.
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
Step 5. The SQL execution plan is as follows. The execution is very fast as index scan is performed.
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)
Step 6. Now, force parallel execution. PostgreSQL 11 supports the parallel execution of multiple partitions and will result in faster speed.
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;
After parallelism is applied, the SQL execution time becomes 40 milliseconds. Since it was already quite fast, the performance with parallelism added is almost unchanged. When the calculation workload is large, the performance will improve significantly.
sml | id | info
------+----+----------------------------------------------------------------------------------------------------------------------------------
0.75 | 1 | 懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁内橰畁蜫征瘭缆竟
(1 row)
Time: 40.298 ms
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).
Use substring(info,1,28)||gen_hanzi(4)||substring(info,29,28) to generate a new string with a similarity of 0.75.
从第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
Implement the stress test function as follows.
-- 使用随机字符串进行相似搜索（用于压测）
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;
Next, you need to query test.
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
Stress test script is as follows.
vi test.sql
\set id random(1,15625000)
select * from get_tbl(1) as t(sml float4, id int, info text);
Implement the stress test as shown below.
-- 并行度调低
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);
Performance bottleneck analysis is as follows.
CPU is in full workload, and I/O is high.
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
CPU consumption is mainly caused by a bitmap scan, and a tuple recheck is performed by the CPU.
I/O consumption is mainly caused by a large amount of I/O access. This is because the data and index size is close to 600 GB, which exceeds the memory size.
Following indicates the data structure performance:
The following table shows the respective performance indicators:
| Case | The response speed of a single similarity search | TPS of similarity searches in a machine stress test | RT of similarity searches in a machine stress test |
|---|---|---|---|
| 1 billion rows with 64 random Chinese characters per row | 40 ms | 428 | 149 ms |
CPU is in full workload, and I/O is high. The CPU consumption is mainly caused by a bitmap scan, and a tuple recheck is performed by the CPU.
I/O consumption is mainly caused by a large amount of I/O access. This is because the data and index size is close to 600 GB, which exceeds the memory size.
1. PostgreSQL 11 Parallel appending significantly improves the performance.
A similarity search of 1 billion random Chinese strings (64 characters in length) takes only XXX seconds.
postgres=# show enable_parallel_append ;
enable_parallel_append
------------------------
on
(1 row)
2. The benefits of using partition tables, in this case, are as follows.
2.1. Improved parallelism of data writing
2.2. Faster index creation
2.3. Faster index maintenance
Meanwhile, PostgreSQL 11 supports the parallel scanning of multiple partitions (enable_parallel_append) and parallel + merge sort. Therefore, the performance of massive data similarity searches is not a problem in the case of a large amount of computing.
Before PostgreSQL 11, you can use DBLink asynchronous calling to support the parallel scanning of multiple partitions.
3. Similarity is a key point in similarity search. You can use show_limit () to view similarity limits and use set_limit to set similarity thresholds. The greater the similarity value, the higher the matching degree, for which 1 indicates the exact match.
postgres=# select show_limit();
show_limit
------------
0.3
(1 row)
postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
4. You can use similarity or word_similarity to view the similarity values of two strings.
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)
For more information about similarity algorithms, check this document.
5. The response speed of a similarity search is related to the similarity set by the user. The more the matched values, the slower the response speed. The smaller the matched value (that is, the higher the precision), the faster the response speed.
-- 响应速度更慢
postgres=# select set_limit(0.1);
set_limit
-----------
0.1
(1 row)
-- 响应速度更快
postgres=# select set_limit(0.9);
set_limit
-----------
0.9
(1 row)
In actual production, use a high limit to search and gradually narrow down the limit to achieve a fast response speed. This logic can be encapsulated in UDFs, and users can call UDFs to search. Let's check the following example:
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);
UDF helps to search in a faster and more accurate manner.
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
6. If you want to set a PostgreSQL instance, database, or user-level threshold, you can use the following two parameters.
pg_trgm.similarity_threshold
pg_trgm.word_similarity_threshold
These parameters act on various operators and functions respectively, see this article for more information.
text % text
similarity(text, text)
与
text <% text
word_similarity(text, text)
The following snapshot shows a configuration example.
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)
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;
Step 2. Test the table using the command below.
create unlogged table t (id serial primary key, arr int[]);
Step 3. Create a function to generate random arrays.
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;
Step 4. Now write test data as shown below.
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)
Step 5. Create an index for an array similarity query.
create index idx_t_1 on t using gin (arr _int4_sml_ops);
Step 6. Next, create a UDF as shown below.
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;
Build your own PostgreSQL solution on Alibaba Cloud with ApsaraDB for RDS PostgreSQL.
PostgreSQL Application in Image Search and Video and Image Deduplication
ApsaraDB - June 16, 2023
Alibaba Clouder - March 8, 2017
Alibaba Cloud Community - September 5, 2024
digoal - February 3, 2020
digoal - September 12, 2019
digoal - February 5, 2020
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn More
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn MoreMore Posts by digoal
