PostgreSQL中JSON字符串数组的子串索引与匹配优化问题

我在PostgreSQL里有一张带jsonb字段的表，字段定义是json JSONB NOT NULL，字段示例值如下：

{ "values": ["foo", "bar", "foobar"]}

要检查values数组里是否包含特定值（比如foo），用下面的索引和查询能高效跑起来：

-- 创建索引
CREATE INDEX table_idx ON T USING gin ((json -> 'values'));
-- 查询语句
SELECT * from T where T.json->'values' ?| array['foo']

但为了支持子串匹配，我建了另一个索引并写了对应的查询：

-- 创建索引
CREATE INDEX table_idx_trigram ON T USING gin ((json ->> 'values') gin_trgm_ops);
-- 查询语句
SELECT * from T 
where exists ( 
   select * from jsonb_array_elements_text(T.json->'values') values(elem) 
   where elem like '%foo%'
)

查询能正常执行，但索引完全没被用到，全程全表扫描。有没有办法优化查询或者索引来提升性能？

补充执行计划输出：

"Seq Scan on public.T  (cost=0.00..50630.30 rows=18268 width=379) (actual time=1.384..66.731 rows=173 loops=1)"
"  Output: T.json"
"  Filter: (SubPlan 1)"
"  Rows Removed by Filter: 36364"
"  Buffers: shared hit=548 read=3863"
"  SubPlan 1"
"    ->  Function Scan on pg_catalog.jsonb_array_elements_text values  (cost=0.01..1.25 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=36537)"
"          Function Call: jsonb_array_elements_text((T.json -> 'values'::text))"
"          Filter: (values.elem ~~ '%foo%'::text)"
"          Rows Removed by Filter: 4"
"Planning Time: 0.114 ms"
"Execution Time: 66.771 ms"

问题根因 #

你建的table_idx_trigram索引是基于json ->> 'values'的结果——也就是把整个JSON数组转换成了一个字符串（比如变成["foo","bar","foobar"]）来做trigram索引，但你的查询是把数组拆成单个元素逐个匹配子串，PostgreSQL没法把这种遍历元素的逻辑和整个数组字符串的索引关联起来，自然不会走索引。

优化方案 #

方案1：针对数组元素建trigram索引 + JSON路径查询 #

直接给JSON数组字段建trigram索引，然后用JSON路径查询来匹配元素：

-- 创建针对JSON数组的gin trigram索引（PostgreSQL 12+支持）
CREATE INDEX table_idx_array_trigram ON T 
USING gin ((json -> 'values')) gin_trgm_ops;

-- 对应的查询语句
SELECT * FROM T
WHERE jsonb_path_exists(json, '$.values[*] ? (@ like_regex "foo")');

或者用jsonb_path_query配合EXISTS：

SELECT * FROM T
WHERE EXISTS (
  SELECT 1 FROM jsonb_path_query(json, '$.values[*]') AS elem
  WHERE elem::text LIKE '%foo%'
);

方案2：新增生成列存文本数组 + 建索引 #

如果这个数组的查询频率很高，可以给表加一个生成列，把JSON数组转成文本数组，再给这个列建trigram索引：

-- 添加生成列，自动将JSON数组转为文本数组
ALTER TABLE T ADD COLUMN values_array TEXT[] 
GENERATED ALWAYS AS (jsonb_array_to_text(json -> 'values')) STORED;

-- 给文本数组建gin trigram索引
CREATE INDEX table_idx_values_array_trigram ON T USING gin (values_array gin_trgm_ops);

-- 查询语句
SELECT * FROM T
WHERE EXISTS (
  SELECT 1 FROM unnest(values_array) AS elem
  WHERE elem LIKE '%foo%'
);

这种方式下，PostgreSQL能直接利用values_array的索引快速筛选出符合条件的行。

方案3：复用现有索引，修改查询逻辑 #

如果你不想改索引，可以调整查询语句，直接匹配整个数组字符串的子串，这样就能用到你已经建好的table_idx_trigram索引：

-- 复用已有的table_idx_trigram索引
SELECT * FROM T
WHERE json ->> 'values' LIKE '%foo%';

注意：这种方式是匹配数组转成字符串后的任意位置，效果和遍历元素匹配子串一致——比如数组里的"foobar"也会被%foo%命中，完全满足你的需求。

验证索引生效 #

修改查询后，执行EXPLAIN ANALYZE看执行计划，如果出现Index Scan using ... on T，就说明索引已经被正常使用了。

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