优化基于LATERAL的JSONB列键值对去重查询

原查询慢的原因 #

你当前的查询需要全表扫描objects表，对每一行的tags列做jsonb_each_text展开，最后再通过DISTINCT去重。如果表数据量大、每个tags里的键值对多，这个过程会因为全表扫描+大量行展开操作变得很慢——完全没用到索引优化。

能不能给LATERAL jsonb_each_text的结果建索引？ #

不行，因为LATERAL是查询时动态生成的临时结果集，不是物理表，没法直接给它建索引。但可以通过预提取JSONB里的键值对，给这些预存的数据建索引，达到类似的加速效果：

1. GIN索引（适合特定场景，但对全局去重帮助有限） #

如果只是想快速找到包含某键值对的行，可以建GIN索引：

CREATE INDEX idx_objects_tags_gin ON objects USING GIN (tags);

但这个索引没法直接加速DISTINCT keys, values的查询，因为它不存储展开后的键值对。

2. 函数索引（针对特定键） #

如果你的查询只关注某个特定键的值，可以给这个键建函数索引，但对全局键值对去重没太大帮助：

-- 示例：给名为"category"的键的值建索引
CREATE INDEX idx_objects_tags_category ON objects ((tags ->> 'category'));

避免物化视图的加速方案 #

1. 用jsonb_path_query替代jsonb_each_text（部分场景更高效） #

如果你的tags结构比较简单，没有复杂嵌套，可以试试jsonb_path_query来提取键值对，某些情况下比jsonb_each_text效率更高：

SELECT DISTINCT
  jsonb_path_query_first(tags, '$.keyvalue() ? (@.key != null).key')::text AS keys,
  jsonb_path_query_first(tags, '$.keyvalue() ? (@.value != null).value')::text AS values
FROM objects;

具体效果得结合你的数据实际测试。

2. 临时表+定期刷新（轻量替代物化视图） #

既然不想用物化视图，可以手动维护一个临时表（或普通表），定期把tags展开后的键值对去重后存进去，查询时直接查这个表就行：

-- 创建临时表（如果是长期用可以换成普通表）
CREATE TEMP TABLE IF NOT EXISTS tags_key_values AS
SELECT DISTINCT keys, values
FROM objects, LATERAL jsonb_each_text(tags) AS each(keys, values);

-- 给临时表建索引，加速查询
CREATE INDEX idx_tags_key_values ON tags_key_values (keys, values);

-- 之后查询直接用这个表
SELECT * FROM tags_key_values;

你可以用定时任务或者触发器来刷新这个表，比每次全表扫描+展开高效太多。

3. 调整work_mem参数，让排序在内存中完成 #

如果慢是因为DISTINCT操作需要磁盘排序，可以临时调大work_mem，让排序在内存里进行：

-- 会话级临时调整，用完可以改回去
SET work_mem = '64MB';

注意别全局调太高，避免内存竞争。

4. 分区表优化（超大数据量场景） #

如果objects表数据量特别大，可以考虑按tags的某些特征（比如键的数量、特定键的值）做分区，这样查询时只需要扫描部分分区，减少数据量。

最后总结 #

• 没法直接给LATERAL jsonb_each_text的结果建索引，但可以通过预存键值对到物理表（临时表或物化视图）并建索引来加速。
• 不想用物化视图的话，优先试试临时表定期刷新+索引，或者调整查询语句和work_mem参数。
• 如果数据量不算特别大，调大work_mem让DISTINCT的排序在内存完成，就能明显提速。

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