如何在PostgreSQL中实现text[]类型前缀匹配并利用GIN索引

问题场景 #

现有text[]类型列的表，已基于大写转换后的数组创建GIN索引，需要实现数组中任意元素以指定前缀开头的查询，且要求该查询能利用适配的GIN索引，替代虚构的&&%运算符实现前缀匹配功能。

已创建的表、函数及索引如下：

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));

当前精确匹配查询select * from tab where upper(col) && upper('{oRUNc9ka3f12reEW8OmzaYQufLYRAlHWGTo}')::text[]可正常利用GIN索引，现需实现类似select * from tab where upper(col) &&% upper('{ORUNC}')::text[]的前缀匹配查询。

解决方案一：利用pg_trgm扩展实现前缀匹配并复用GIN索引 #

步骤1：安装pg_trgm扩展 #

PostgreSQL的pg_trgm扩展支持基于三元组（trigram）的文本匹配，可优化前缀、模糊查询的索引效率：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

步骤2：创建适配前缀匹配的GIN索引 #

基于大写转换后的数组，创建使用gin_trgm_ops操作符类的GIN索引，该索引支持trigram匹配，可加速前缀查询：

CREATE INDEX tab_col_upper_trgm_idx ON tab USING GIN (upper(col) gin_trgm_ops);

步骤3：编写前缀匹配查询 #

通过EXISTS子句结合unnest拆解数组，对每个元素做前缀匹配，该查询会自动利用上述创建的trigram GIN索引：

SELECT * FROM tab 
WHERE EXISTS (
  SELECT 1 FROM unnest(upper(col)) elem
  WHERE elem LIKE 'ORUNC%'
);

解决方案二：利用全文检索实现前缀匹配 #

若更倾向于使用全文检索语法实现前缀匹配，可采用以下方式：

步骤1：创建数组转tsvector的函数 #

将text[]转换为适合全文检索的tsvector类型（使用simple配置避免分词干扰）：

CREATE OR REPLACE FUNCTION text_array_to_tsvector(text[]) RETURNS tsvector AS $$
SELECT to_tsvector('simple', array_to_string($1, ' '));
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;

步骤2：创建全文检索GIN索引 #

基于大写数组转换后的tsvector创建GIN索引：

CREATE INDEX tab_col_tsvector_idx ON tab USING GIN (text_array_to_tsvector(upper(col)));

步骤3：编写前缀查询 #

使用@@运算符结合带前缀通配符的tsquery实现前缀匹配：

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

内容的提问来源于stack exchange，提问作者Gunther Schadow