相关但不同的问题Using a GIN index in PostgreSQL with a LIKE clause揭示了三元组扩展，它对于相似性搜索和排序非常强大。
但在我的例子中，我有一个数组列，用GIN索引（并规范化为upper），但我希望允许头部搜索使用像在like操作符中一样的索引。

create table tab (
  col text[]
);

create or replace function upper(text[]) returns text[] language sql as $$
   select upper($1::text)::text[]
$$ strict immutable parallel safe;

create index on tab using GIN(upper(col));

现在用随机数据填充它：

insert into tab
select array(select array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', trunc(random() * 62)::integer + 1, 1) from generate_series(1, slen)), '')
                  from (select trunc(random() * 60)::integer + 10 as slen from generate_series(1,tlen)) x) as col
  from (select id, trunc(random() * 5)::integer + 1 as tlen from generate_series(1,10000) as id) x;

现在从中间取一个值：

select * from tab offset 5000 limit 1;
                                                                                         col                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {hSQbUOKSTA1A9ISb5qx9kkG6fJ5HP3lQhaoEbM4MHgJZ,IMzcLns5waH8Gat5njGBwv4AptkmtxlfUxLyPx1VSZsitvq,pNw67x9mZ6nhJrGDONNRmpT7pbx,oRUNc9ka3f12reEW8OmzaYQufLYRAlHWGTo,PBD87EsMtupwZpVgfcxu5}
(1 row)

然后查一下

explain analyze 
select * from tab where upper(col) && upper('{oRUNc9ka3f12reEW8OmzaYQufLYRAlHWGTo}')::text[]

给予

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tab  (cost=841.23..1103.03 rows=158 width=32) (actual time=1.526..1.549 rows=1 loops=1)
   Recheck Cond: (upper(col) && ('{ORUNC9KA3F12REEW8OMZAYQUFLYRALHWGTO}'::cstring)::text[])
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on tab_upper_idx  (cost=0.00..841.19 rows=158 width=0) (actual time=1.510..1.515 rows=1 loops=1)
         Index Cond: (upper(col) && ('{ORUNC9KA3F12REEW8OMZAYQUFLYRALHWGTO}'::cstring)::text[])
 Planning Time: 0.095 ms
 Execution Time: 1.596 ms
(7 rows)

所以它使用了GIN索引。我在这里注意到“cstring”数据类型，这是我以前从未见过的。
现在我希望能够做一个部分匹配，让我们找到所有行ILIKE 'ORUNC %'。

select * from tab where upper(col) &&% upper('{ORUNC}')::text[]

这里我有&&%作为一个虚构的操作符，它将进行前缀匹配。
如何实施？

CREATE OR REPLACE FUNCTION arr_to_upper_string(_arr text[], _sep text = ' ') RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE BEGIN ATOMIC SELECT upper(array_to_string($1, $2)); END; COMMENT ON FUNCTION arr_to_upper_string(text[], text) IS 'array_to_string() is not immutable, as it takes "anyarray" input, and some types have no immutable conversion to text. For text[] input it is, in fact immutable. We need an immutable fucntion for indexes. Hence the wrapper. upper(text) is immutable anyway. But feeding other array types to this function (and casting implicitly or explicitly) may not be immutable!'

1条答案

按热度按时间

wsxa1bj11#

思路一

规范化您的设计，并将数组解嵌套到单独的表中，每行一个元素。然后用COLLATE "C"或SP-GiST索引创建一个普通的B树索引，以支持前缀匹配--在相应调整的查询中。理想情况下，使用**“starts with”运算符^@**：

...
WHERE col_element ^@ 'foo';

参见：

PostgreSQL LIKE查询性能变化

思路二

在使用数组列时，在转换为tsvector的数组上使用**text search索引，它支持前缀匹配**。你可能需要一本“简单”的字典来解释这个问题。您还需要一个array_to_string()的IMMUTABLE Package 器，它允许anyarray输入，因此只有STABLE。同时，将upper()合并为：

使用SQL标准函数（可选）。参见：

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

用它创建一个函数索引（或向表中添加一个冗余的生成列）。

CREATE INDEX tab_col_tsv_gin_idx ON tab USING gin (to_tsvector('simple', arr_to_upper_string(col)));

那么您的查询可以是：

SELECT * FROM tab
WHERE to_tsvector('simple', arr_to_upper_string(col)) @@ to_tsquery('simple', 'ORUNC:*');

fiddle
参见：

Get partial match from GIN indexed TSVECTOR column

赞(0）回复(0）举报 2023-06-05

PostgreSQL部分匹配&&运算符在text[]上，如LIKE和GIN索引

1条答案

思路一

思路二

相关问题

热门标签

最新问答