Postgres JSONB列GIN索引下LIKE模糊搜索性能优化问题

问题根因 #

默认JSONB GIN索引仅针对精确匹配、键存在性这类查询做优化，like_regex的部分匹配逻辑无法利用该索引做高效过滤：从执行计划可以看到，索引扫描返回了202208行中间结果，后续堆表Recheck阶段筛掉了198778行无效数据，绝大多数耗时都消耗在堆表读取和结果校验上。

优化方案 #

方案1：全文检索索引（适配关键词检索场景，对应Oracle section group能力） #

PostgreSQL内置的全文检索能力完全可以替代Oracle的section group功能，支持对JSONB内部不同字段做独立的标签化检索：

1. 创建带字段标签的tsvector生成列，可对不同字段设置检索权重：

ALTER TABLE book_ms.book_data ADD COLUMN book_search_tsv tsvector 
GENERATED ALWAYS AS (
    -- 权重A对应title，优先级最高
    setweight(to_tsvector('simple', COALESCE(book_details->'book_data'->>'title', '')), 'A') ||
    -- 权重B对应author
    setweight(to_tsvector('simple', COALESCE(book_details->'book_data'->>'author', '')), 'B') ||
    -- 权重C对应subject
    setweight(to_tsvector('simple', COALESCE(book_details->'book_data'->>'subject', '')), 'C')
) STORED;

2. 创建GIN索引加速全文检索：

CREATE INDEX idx_book_search_tsv ON book_ms.book_data USING GIN(book_search_tsv);

3. 改写查询语句：

SELECT * FROM book_ms.book_data a
WHERE 
    -- 精确匹配走原有JSONB GIN索引
    a.book_details @@ '$.book_data.author == "abcd"'
    -- title关键词匹配走全文检索索引
    AND a.book_search_tsv @@ to_tsquery('simple', 'literature');

如果需要前缀匹配，可将查询条件改为to_tsquery('simple', 'literature:*')，支持lit这类前缀检索。

方案2：pg_trgm索引（适配任意子串LIKE匹配场景） #

如果你需要的是任意位置子串的模糊匹配（不需要分词能力），可以使用pg_trgm扩展构建Trigram索引：

1. 开启pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建title提取的生成列：

ALTER TABLE book_ms.book_data ADD COLUMN book_title text 
GENERATED ALWAYS AS (book_details->'book_data'->>'title') STORED;

3. 创建Trigram GIN索引：

CREATE INDEX idx_book_title_trgm ON book_ms.book_data USING GIN(book_title gin_trgm_ops);

4. 改写查询语句：

SELECT * FROM book_ms.book_data a
WHERE 
    a.book_details @@ '$.book_data.author == "abcd"'
    AND a.book_title LIKE '%literature%';

Oracle section group等价实现说明 #

PostgreSQL原生支持对JSONB字段生成带字段标识的tsvector，你可以使用jsonb_to_tsvector函数直接指定需要检索的JSON字段，生成的检索向量会自带字段标签，查询时可指定仅匹配某个字段内的关键词，和Oracle section group的分字段检索能力完全等价。

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