PostgreSQL中如何为jsonb列顶层大量键创建索引以加速查询

针对你的场景，有几种可行的方案，可根据数据更新频率和查询需求选择：

方案一：重构为EAV实体-属性-值表结构（推荐长期方案） #

如果数据更新频率适中，重构表结构是最彻底的解决办法，能最大化查询性能：

1. 创建子表存储键值对

CREATE TABLE my_data_attributes (
    data_id INT REFERENCES my_data_table(id), -- 关联原表主键
    top_key TEXT,
    value_str TEXT,
    PRIMARY KEY (data_id, top_key) -- 避免同一数据重复存储相同键
);

2. 导入现有数据

INSERT INTO my_data_attributes (data_id, top_key, value_str)
SELECT 
    id,
    jsonb_object_keys(json_struct),
    json_struct -> jsonb_object_keys(json_struct) ->> 'value'
FROM my_data_table;

3. 创建联合索引

CREATE INDEX idx_attr_key_value ON my_data_attributes (top_key, value_str);

4. 查询示例

SELECT t.*
FROM my_data_table t
JOIN my_data_attributes a1 ON t.id = a1.data_id
JOIN my_data_attributes a2 ON t.id = a2.data_id
WHERE a1.top_key = 'key1' AND a1.value_str LIKE 'foo%'
AND a2.top_key = 'key2' AND a2.value_str LIKE 'bar%';

适用场景：数据更新频率适中，可通过触发器维护子表数据（比如原表json_struct更新时同步更新子表），适合长期使用。

方案二：使用物化视图+联合索引（适合静态/准静态数据） #

如果数据很少更新，物化视图是低成本的快速实现方案：

1. 创建物化视图展开键值对

CREATE MATERIALIZED VIEW my_data_key_values AS
SELECT 
    id, -- 原表主键
    jsonb_object_keys(json_struct) AS top_key,
    (json_struct -> top_key ->> 'value') AS value_str
FROM my_data_table;

2. 创建联合索引

CREATE INDEX idx_mdv_key_value ON my_data_key_values (top_key, value_str);

3. 查询示例（通过INTERSECT筛选同时满足多个条件的记录）

SELECT t.*
FROM my_data_table t
JOIN (
    SELECT id FROM my_data_key_values WHERE top_key = 'key1' AND value_str LIKE 'foo%'
    INTERSECT
    SELECT id FROM my_data_key_values WHERE top_key = 'key2' AND value_str LIKE 'bar%'
) filtered ON t.id = filtered.id;

注意：数据更新后需要手动刷新物化视图：REFRESH MATERIALIZED VIEW my_data_key_values;

适用场景：数据静态或更新频率极低，不想修改原表结构的场景。

方案三：基于trgm的GIST函数索引（无需改结构，适合快速实现） #

如果无法修改原表结构，可通过提取键值对文本数组，结合GIST的trgm索引支持模糊查询：

1. 创建提取键值对的函数

CREATE OR REPLACE FUNCTION extract_key_value_pairs(jsonb) RETURNS text[] AS $$
SELECT array_agg(concat(k, ':', v ->> 'value'))
FROM jsonb_each($1) AS t(k, v);
$$ LANGUAGE sql IMMUTABLE;

2. 创建GIST索引（使用gist_trgm_ops支持模糊匹配）

CREATE INDEX idx_key_value_trgm ON my_data_table USING GIST (extract_key_value_pairs(json_struct) gist_trgm_ops);

3. 查询示例

SELECT * FROM my_data_table
WHERE extract_key_value_pairs(json_struct) @> array['key1:foo%']::text[]
AND extract_key_value_pairs(json_struct) @> array['key2:bar%']::text[];

适用场景：无法修改原表结构，需要快速实现索引支持模糊查询的场景，性能略逊于前两种方案，但远优于全表扫描。

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