postgresql – 如何索引pg_trgm“’term’％ANY(array_column)`q

索引类型(即运算符类)gin_trgm_ops基于％运算符,它适用于两个文本参数：

CREATE OPERATOR trgm.%(
  PROCEDURE = trgm.similarity_op,LEFTARG = text,RIGHTARG = text,COMMUTATOR = %,RESTRICT = contsel,JOIN = contjoinsel);

您不能将gin_trgm_ops用于数组.
为数组列定义的索引永远不会与任何(数组[…])一起使用,因为数组的各个元素未被索引.
索引数组需要不同类型的索引,即gin数组索引.

幸运的是,指数gin_trgm_ops设计得非常巧妙,它正在与像ilike这样的操作符合作,可以作为替代解决方案(例如下面描述的例子).

测试表

有两列(id serial primary key,names text [])并包含分成数组元素的100000个拉丁语句子.

select count(*),sum(cardinality(names))::int words from test;

 count  |  words  
--------+---------
 100000 | 1799389

select * from test limit 1;

 id |                                                     names                                                     
----+---------------------------------------------------------------------------------------------------------------
  1 | {fugiat,odio,aut,quis,dolorem,exercitationem,fugiat,voluptates,facere,error,debitis,ut,nam,et,voluptatem,eum}

搜索单词片段praesent在2400毫秒内提供7051行：

explain analyse
select count(*)
from test
where 'praesent' % any(names);

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5479.49..5479.50 rows=1 width=0) (actual time=2400.866..2400.866 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..5477.00 rows=996 width=0) (actual time=1.464..2400.271 rows=7051 loops=1)
         Filter: ('praesent'::text % ANY (names))
         Rows Removed by Filter: 92949
 Planning time: 1.038 ms
 Execution time: 2400.916 ms

物化视图

一种解决方案是规范化模型,包括在一行中创建一个具有单个名称的新表.由于现有的查询,视图,功能或其他依赖性,这种重组可能难以实现并且有时是不可能的.使用物化视图可以在不改变表结构的情况下实现类似的效果.

create materialized view test_names as
    select id,name,name_id
    from test
    cross join unnest(names) with ordinality u(name,name_id)
    with data;

因为没有必要,但在以与主表中相同的顺序聚合名称时可能很有用.查询test_names在同一时间提供与主表相同的结果.

创建索引后,执行时间会反复减少：

create index on test_names using gin (name gin_trgm_ops);

explain analyse
select count(distinct id)
from test_names
where 'praesent' % name

                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4888.89..4888.90 rows=1 width=4) (actual time=56.045..56.045 rows=1 loops=1)
   ->  Bitmap Heap Scan on test_names  (cost=141.95..4884.39 rows=1799 width=4) (actual time=10.513..54.987 rows=7230 loops=1)
         Recheck Cond: ('praesent'::text % name)
         Rows Removed by Index Recheck: 7219
         Heap Blocks: exact=8122
         ->  Bitmap Index Scan on test_names_name_idx  (cost=0.00..141.50 rows=1799 width=0) (actual time=9.512..9.512 rows=14449 loops=1)
               Index Cond: ('praesent'::text % name)
 Planning time: 2.990 ms
 Execution time: 56.521 ms

该解决方案有一些缺点.由于视图已实现,因此数据将在数据库中存储两次.您必须记住在更改主表后刷新视图.由于需要将视图连接到主表,查询可能会更复杂.

使用ilike

我们可以在表示为文本的数组上使用ilike.我们需要一个不可变函数来在整个数组上创建索引：

create function text(text[])
returns text language sql immutable as
$$select $1::text $$

create index on test using gin (text(names) gin_trgm_ops);

并在查询中使用该函数：

explain analyse
select count(*)
from test
where text(names) ilike '%praesent%' 

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=117.06..117.07 rows=1 width=0) (actual time=60.585..60.585 rows=1 loops=1)
   ->  Bitmap Heap Scan on test  (cost=76.08..117.03 rows=10 width=0) (actual time=2.560..60.161 rows=7051 loops=1)
         Recheck Cond: (text(names) ~~* '%praesent%'::text)
         Heap Blocks: exact=2899
         ->  Bitmap Index Scan on test_text_idx  (cost=0.00..76.08 rows=10 width=0) (actual time=2.160..2.160 rows=7051 loops=1)
               Index Cond: (text(names) ~~* '%praesent%'::text)
 Planning time: 3.301 ms
 Execution time: 60.876 ms

60与2400毫秒,相当不错的结果,无需创建额外的关系.

这种解决方案似乎更简单并且需要更少的工作,但是ilike是一种比trgm％运算符更不精确的工具就足够了.

我们为什么要将整个数组的ilike而不是％用作文本？
相似性在很大程度上取决于文本的长度.
在不同长度的长文本中搜索单词是非常困难的.
例如.限制= 0.3我们有结果：

with data(txt) as (
values
    ('praesentium,distinctio,modi,nulla,commodi,tempore'),('praesentium,commodi'),nulla'),modi'),distinctio'),('praesentium')
)
select length(txt),similarity('praesent',txt),'praesent' % txt "matched?"
from data;

 length | similarity | matched? 
--------+------------+----------
     49 |   0.166667 | f           <--!
     41 |        0.2 | f           <--!
     33 |   0.228571 | f           <--!
     27 |   0.275862 | f           <--!
     22 |   0.333333 | t
     11 |   0.615385 | t
(6 rows)