PostgreSQL JSONB数组匹配查询加order by后性能低下优化问题

1. 改写低效查询逻辑 #

你当前用inner join jsonb_array_elements的匹配方式会展开入参数组生成大量临时行，不仅会产生重复结果需要加distinct去重，还会导致GIN索引无法被高效调用。可以直接用JSONB的匹配逻辑简化：
对于“JSONB数组包含入参数组任意元素”的需求，用@> ANY语法实现，既避免了关联产生的冗余计算，也能直接适配GIN索引：

-- 示例：category_ids匹配[68,126,370]的写法
category_ids @> ANY (array(select jsonb_build_array(x) from unnest(array[68,126,370]) x))

简化后可以完全去掉多余的子查询嵌套和distinct关键字，查询结构如下：

select id, is_classified
from ads
where 
    -- ad_type_ids任意匹配入参数组
    ad_type_ids @> ANY (array(select jsonb_build_array(x) from unnest(array[14,5]) x))
    -- category_ids任意匹配入参数组，入参提前去重
    and category_ids @> ANY (array(select jsonb_build_array(x) from unnest(array[68,126,370,371,372,373,12135,12134]) x))
    and market @> '[59]'
    and status = true
    and coalesce(approved, true) = true
    and expire_at > '2021-08-29 10:44:55'
    and main_category_id = 60
    and is_classified = false
order by refreshed_at desc
limit 4 offset 0;

2. 新建适配排序的联合索引 #

你单独创建单字段索引无效的核心原因是：加了order by之后，PostgreSQL需要先把所有符合过滤条件的行查询出来再做排序，如果匹配行数多，排序成本极高。而你只需要取前4条，最优方案是让查询可以直接从索引中按refreshed_at降序取数，边扫边校验过滤条件，找到4条符合要求的就终止执行。
推荐创建如下包含常用等值过滤条件、排序字段，以及覆盖所有查询所需字段的联合索引，避免回表：

CREATE INDEX ads_sort_filter_idx ON ads (main_category_id, is_classified, status, refreshed_at desc) 
INCLUDE (approved, expire_at, category_ids, ad_type_ids, market);

如果你日常查询中符合main_category_id=60、is_classified=false、status=true的行占比很低，可以额外创建GIN复合索引加速JSONB字段过滤：

CREATE INDEX ads_jsonb_filter_idx ON ads USING gin (category_ids, ad_type_ids, market)
WHERE main_category_id = 60 AND is_classified = false AND status = true;

3. 辅助优化点 #

• 入参数组提前去重，比如示例中的重复值68提前删掉，减少无效匹配计算
• 如果PG版本 >= 12，可以为常用的过滤条件创建生成列，把JSONB中需要频繁匹配的字段提取为普通字段，用B树索引查询效率更高

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