PostgreSQL中如何基于返回多行文本的函数创建索引，实现JSONB数组键的前缀LIKE查询优化？

首先得指出你当前方案里的几个关键问题：

1. 你的aggregate_tags函数定义为返回text，但实际通过jsonb_each_text返回了多行结果——这在PostgreSQL里是会报错的（标量函数只能返回单个值），就算你改成returns setof text，也没法直接用它创建索引，因为索引列必须是单个标量值，不能是多行集合。这也是为什么你的索引只能匹配到第一个键，完全没覆盖其他键的原因。

接下来给你几个可行的优化方案，根据你的数据更新频率来选：

方案一：用生成列存储所有键的数组+GIN trigram索引 #

这个方案适合数据更新不频繁，或者能接受生成列维护开销的场景：

1. 首先给tasks表添加一个生成列，自动提取tags里的所有键并存储为数组：

ALTER TABLE tasks 
ADD COLUMN tags_keys text[] 
GENERATED ALWAYS AS (ARRAY(SELECT j.key FROM jsonb_each_text(tags) j)) STORED;

2. 创建支持前缀匹配的GIN trigram索引：

CREATE INDEX idx_tasks_tags_keys_trgm ON tasks USING GIN (tags_keys gin_trgm_ops);

3. 查询的时候，先用索引快速过滤出包含目标前缀键的行，再提取对应的键：

SELECT j.key
FROM tasks t, lateral jsonb_each_text(t.tags) j
WHERE j.key LIKE 'my_awesome_value34%'
AND EXISTS (
  SELECT 1 FROM unnest(t.tags_keys) k 
  WHERE k LIKE 'my_awesome_value34%'
);

这里的EXISTS子句会利用GIN trigram索引快速缩小范围，避免全表扫描，之后再展开JSONB提取匹配的键，效率会高很多。

方案二：物化视图+单列前缀索引 #

如果你的tags字段更新频率很低，物化视图是更高效的选择：

1. 创建物化视图，把每个JSONB键拆成单独的行：

CREATE MATERIALIZED VIEW task_tags AS
SELECT t.id, j.key
FROM tasks t, lateral jsonb_each_text(t.tags) j;

2. 在物化视图的key列上创建text_pattern_ops索引，专门优化前缀LIKE查询：

CREATE INDEX idx_task_tags_key_pattern ON task_tags (key text_pattern_ops);

3. 查询直接从物化视图获取结果：

SELECT key FROM task_tags WHERE key LIKE 'my_awesome_value34%';

注意：如果tasks表的数据有更新，你需要手动刷新物化视图：REFRESH MATERIALIZED VIEW task_tags;，如果需要实时同步，可以搭配触发器来维护。

方案三：用函数生成tsvector+全文索引 #

如果你的前缀查询需求更灵活（比如支持多个前缀），可以用tsvector来存储所有键，然后用全文索引：

1. 创建函数把JSONB键转换成tsvector：

CREATE FUNCTION tags_to_tsvector(jsonb) RETURNS tsvector LANGUAGE sql IMMUTABLE AS $$
SELECT to_tsvector('simple', string_agg(j.key, ' ')) FROM jsonb_each_text($1) j;
$$;

2. 创建基于这个函数的GIN索引：

CREATE INDEX idx_tasks_tags_tsv ON tasks USING GIN (tags_to_tsvector(tags));

3. 查询时用前缀匹配的tsquery：

SELECT j.key
FROM tasks t, lateral jsonb_each_text(t.tags) j
WHERE tags_to_tsvector(t.tags) @@ to_tsquery('simple', 'my_awesome_value34:*')
AND j.key LIKE 'my_awesome_value34%';

这里的:*表示前缀匹配，simple配置避免分词干扰，确保精确匹配键的前缀。

最后再提醒你：之前的索引之所以失效，是因为你试图用返回多行的函数建索引，但PostgreSQL的索引不支持这种场景——索引必须基于单个值。上面的方案都是把多行的键转换成单个可索引的结构（数组、tsvector），或者拆成单独的行来索引，这样才能优化你的前缀查询。

备注：内容来源于stack exchange，提问作者Дима Шестаев