PostgreSQL如何为非结构化JSON列创建表达式索引优化查询？

一、针对包含匹配（j.key ilike '%some_key%'）的优化 #

要解决模糊包含查询的性能问题，需借助pg_trgm扩展创建表达式索引，具体步骤如下：

1. 确保安装pg_trgm扩展（未安装则执行）：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建针对JSONB键的trgm索引：

CREATE INDEX idx_tasks_tags_keys_trgm ON tasks USING gin (jsonb_object_keys(tags) gin_trgm_ops);

该索引会提取每行tags字段的所有键，基于trgm算法建立索引，完美适配ILIKE '%xxx%'的模糊包含查询。

3. 优化查询语句（用DISTINCT替代GROUP BY，逻辑一致且性能更优）：

SELECT DISTINCT key AS value
FROM tasks, jsonb_object_keys(tags) AS key
WHERE key ILIKE '%some_key%'
ORDER BY key;

二、针对前缀匹配（j.key ilike 'some_prefix%'）的优化 #

前缀匹配无需trgm索引，使用btree表达式索引效率更高：

1. 创建btree前缀索引：

CREATE INDEX idx_tasks_tags_keys_prefix ON tasks USING btree (jsonb_object_keys(tags) text_pattern_ops);

text_pattern_ops用于适配非C locale环境下的前缀匹配，确保ILIKE 'xxx%'能命中索引；若数据库为C locale，可省略该参数直接创建btree索引。

2. 优化后的查询语句：

SELECT DISTINCT key AS value
FROM tasks, jsonb_object_keys(tags) AS key
WHERE key ILIKE 'some_prefix%'
ORDER BY key;

额外优化建议 #

• 减少不必要开销：用jsonb_object_keys替代jsonb_each_text，仅提取键而非键值对，降低计算开销。
• 更新统计信息：创建索引后执行ANALYZE tasks;，让PostgreSQL优化器获得最新数据分布，选择更优执行计划。

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