PostgreSQL相似文本查询

最新推荐文章于 2024-04-25 15:22:29 发布

foucus、

最新推荐文章于 2024-04-25 15:22:29 发布

阅读量3.6k

点赞数 1

分类专栏： PostgreSQL 文章标签： postgresql 数据库 sql

本文链接：https://blog.csdn.net/weixin_39540651/article/details/104047525

版权

PostgreSQL 专栏收录该内容

282 篇文章 156 订阅

订阅专栏

在实际应用中，我们常常会使用到相似搜索，例如图片、数组、文本等相似搜索。对于文本，可以使用pg_trgm插件来实现相似搜索。
在使用pg_trgm插件来进行相似搜索前，我们需要知道相似度是什么，相似搜索中很关键的一个点是相似度。通过show_limit()可以查看相似度限制，通过set_limit可以设置相似度阈值。相似度值越大，表示需要的匹配度越高，1表示完全匹配。

bill=# select show_limit();   
 show_limit 
------------
        0.3
(1 row)

bill=# select set_limit(0.9); 
 set_limit 
-----------
       0.9
(1 row)

然后我们该如何查看两个文本的相似度呢？pg_trgm自带的函数similarity或word_similarity可以查看两个字符串的相似度值。

bill=# select similarity('abc','abcd');
 similarity 
------------
        0.5
(1 row)
bill=# select word_similarity('abc','abcd');
 word_similarity 
-----------------
            0.75
(1 row)

bill=# select show_trgm('abc');
        show_trgm        
-------------------------
 {"  a"," ab",abc,"bc "}
(1 row)
bill=# select show_trgm('abcd');
          show_trgm          
-----------------------------
 {"  a"," ab",abc,bcd,"cd "}
(1 row)

我们可以利用文本的相似度来判断两个文本的相似程度，例如查询和一个文本相似度大于0.9的文本，那么查出来的结果可以说是和原文本十分相似。

例子：
1、生成随机中文的函数

bill=# create or replace function gen_hanzi(int) returns text as $$      
bill$# declare      
bill$#   res text;      
bill$# begin      
bill$#   if $1 >=1 then      
bill$#     select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);      
bill$#     return res;      
bill$#   end if;      
bill$#   return null;      
bill$# end;      
bill$# $$ language plpgsql strict;    
CREATE FUNCTION

2、建表

create unlogged table tbl(id int primary key, info text);
--建64个子表    
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;    
$$;

3、插入数据
插入100万条记录。

bill=# insert into tbl select generate_series(1,1000000),gen_hanzi(64);
INSERT 0 1000000

4、创建索引
创建GIN索引。

bill=# create index idx_tbl_info on tbl using gin(info gin_trgm_ops); 
CREATE INDEX

5、查询
可以看到使用了索引扫描，所以速度很快。

bill=# explain select similarity(info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁') as sml, -- 计算输入词与存储字符串的相似度    
  * from tbl     
  where info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'    -- 相似度超过阈值    
  order by sml desc            -- 按相似度排序(倒排，越相似的排在越前面)    
  limit 10;  
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3418.16..3418.19 rows=10 width=194)
   ->  Sort  (cost=3418.16..3420.82 rows=1064 width=194)
         Sort Key: (similarity(tbl.info, '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)) DESC
         ->  Result  (cost=484.85..3395.17 rows=1064 width=194)
               ->  Append  (cost=484.85..3381.87 rows=1064 width=190)
                     ->  Bitmap Heap Scan on tbl  (cost=484.85..1720.55 rows=1000 width=200)
                           Recheck Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                           ->  Bitmap Index Scan on idx_tbl_info  (cost=0.00..484.60 rows=1000 width=0)
                                 Index Cond: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl0  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl1  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl2  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl3  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl4  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl5  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl6  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl7  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl8  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl9  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl10  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl11  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl12  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl13  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl14  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl15  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl16  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl17  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl18  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl19  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl20  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl21  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl22  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl23  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl24  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl25  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl26  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl27  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl28  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl29  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl30  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl31  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl32  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl33  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl34  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl35  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl36  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl37  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl38  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl39  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl40  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl41  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl42  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl43  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl44  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl45  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl46  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl47  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl48  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl49  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl50  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl51  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl52  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl53  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl54  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl55  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl56  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl57  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl58  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl59  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl60  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl61  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl62  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
                     ->  Seq Scan on tbl63  (cost=0.00..25.88 rows=1 width=36)
                           Filter: (info % '懛瑌娺罊鶩凳芹緔茙蠡慺礛唾霹蹺憙胣緗犭昉鬪蒽麴牵癰嚒巈蔦焳邹祧鵅莥钶们鞀楝嬦眥条弘娸霵鐲鑚夊涨鮗傞屽嶋莁豓舸鮉蟙材骘媨迁'::text)
(137 rows)

当然，查询的速度还和相似度的限制有关，因为相似度限制越高即代表返回的记录更少，那么查询的速度显然会更快。

foucus、

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL相似文本查询

在实际应用中，我们常常会使用到相似搜索，例如图片、数组、文本等相似搜索。对于文本，可以使用pg_trgm插件来实现相似搜索。在使用pg_trgm插件来进行相似搜索前，我们需要知道相似度是什么，相似搜索中很关键的一个点是相似度。通过show_limit()可以查看相似度限制，通过set_limit可以设置相似度阈值。相似度值越大，表示需要的匹配度越高，1表示完全匹配。bill=# select ...
复制链接

扫一扫