下面给出可直接替换的查询改写、索引与参数调优建议，并逐条说明优化原因，聚焦于执行计划、资源消耗、查询效率、索引使用。
一、SQL改写（去掉CTE屏障、让规划器可并行/内联、使用可命中GIN的JSONB谓词）
-- 目标：在一次聚合内完成，尽量让优化器自由重排/并行，减少不必要的物化与I/O
SELECT category_id, product_id, total_qty, total_amount
FROM (
SELECT p.category_id,
s.product_id,
SUM(s.qty) AS total_qty,
SUM(s.amount) AS total_amount,
ROW_NUMBER() OVER (
PARTITION BY p.category_id
ORDER BY SUM(s.amount) DESC
) AS rn
FROM sales s
JOIN (
SELECT id, category_id
FROM products
WHERE status = 'active'
AND attrs @> '{"season":"winter"}' -- 利用GIN的@>包含查询
) p ON p.id = s.product_id
WHERE s.sold_at >= now() - interval '90 day'
GROUP BY p.category_id, s.product_id
) x
WHERE rn <= 10
ORDER BY category_id, total_amount DESC;
与原SQL相比的关键点
- 去掉了WITH CTE分段：在PG12+中CTE通常会被内联，但在存在复杂成本估计或低版本时可能物化。改成子查询/内联能让优化器做并行、谓词下推与更优连接顺序。
- JSONB过滤改为attrs @> '{"season":"winter"}'：现有的GIN_products_attrs (jsonb_ops)对@>非常友好；原来的attrs->>'season' = 'winter'通常不能直接命中该GIN（除非额外有函数索引）。
- 将产品筛选提到JOIN子查询中：帮助优化器优先使用较小的“活跃+冬季”产品集驱动连接，再借助sales(product_id, sold_at)索引做回表，显著降低sales的扫描量。
二、索引优化建议（按收益从高到低）
- 覆盖式索引：sales(product_id, sold_at) INCLUDE (qty, amount)
- 语句：
CREATE INDEX CONCURRENTLY idx_sales_prod_time_cover
ON sales (product_id, sold_at) INCLUDE (qty, amount);
- 原因与效果：
- 你已有(product_id, sold_at)索引，但聚合还需要qty/amount；INCLUDE可以显著增加“索引仅扫描”的概率（可减少大量Heap访问与随机I/O），尤其在90天窗口内数据经常被VACUUM标记为all-visible时收益明显。
- 该索引完全匹配“按product_id定位后按时间过滤”的访问模式，并支撑聚合所需列。
- 产品侧“活跃+冬季”部分覆盖索引（减少回表，提升Join与取category_id成本）
- 语句（PG11+，利用INCLUDE）：
CREATE INDEX CONCURRENTLY idx_products_active_winter_id_cat
ON products (id) INCLUDE (category_id)
WHERE status = 'active' AND (attrs->>'season') = 'winter';
- 原因与效果：
- 连接键是p.id，聚合要用p.category_id。该“部分覆盖索引”使连接获取category_id时无需回表（Index Only Scan），且仅覆盖到真正需要的子集（active+winter），I/O大幅下降。
- 比“(category_id, id)”更对口，因为Join按id等值匹配。
- 让JSONB过滤稳定命中索引的两种选择（二选一）
- 如果你更倾向写attrs->>'season' = 'winter'：
CREATE INDEX CONCURRENTLY idx_products_season_expr
ON products ((attrs->>'season'));
并配合WHERE status='active'多用：
CREATE INDEX CONCURRENTLY idx_products_active_season_expr
ON products ((attrs->>'season'))
WHERE status='active';
- 如果更偏向@>（推荐，已在SQL改用@>）：
现有GIN（jsonb_ops）可用；若“季节”字段查询非常高频，也可做一个“活跃”部分GIN：
CREATE INDEX CONCURRENTLY gin_products_attrs_active
ON products USING GIN (attrs jsonb_path_ops)
WHERE status='active';
注：jsonb_path_ops对@>更紧凑，内存和匹配效率更好，但功能略窄；jsonb_ops已足够则不必新增。
三、执行计划预期与验证要点
- 期望的计划轮廓
- 先扫描小集合的products（Bitmap/Index Scan）得到active+winter的id与category_id；
- Hash/Bitmap/Nested Loop Join到sales：
- 若驱动为products，访问sales时走 idx_sales_prod_time_cover(product_id, sold_at) 做索引范围扫描（sold_at >= now() - 90d）；
- 对sales侧触发并行扫描与并行聚合（Parallel HashAggregate）。
- HashAggregate按(category_id, product_id)汇总；
- Window（ROW_NUMBER）在聚合结果上做分区排序，结果集量级已被大幅收缩；
- 外层过滤rn <= 10，再排序输出（小结果，代价很低）。
- 用EXPLAIN (ANALYZE, BUFFERS) 验证
- 关注点：
- 没有“CTE Scan”/“Materialize”导致的大量I/O；
- products使用索引（Bitmap/Index Scan）且行数估计接近；
- sales侧使用 idx_sales_prod_time_cover，堆块(Hits)较少、索引块(Hits)为主；
- HashAggregate/Sort 没有发生磁盘溢出（注意Sort Method: external/ spilled）；
- 并行度（Workers Planned/Launched）> 0。
四、运行参数与统计建议
- work_mem：窗口排序与分组建议每个worker至少64–128MB（根据并发适当权衡）。若EXPLAIN显示溢出到磁盘，增大到不溢出为宜。
- max_parallel_workers_per_gather：2–4（视硬件与并发），确保并行聚合能生效。
- effective_cache_size：按机器内存合理设置（如内存的50–75%），帮助成本估计器更激进地使用索引。
- 针对表达式的统计（帮助选择合适计划，尤其是JSONB键与status的联合选择度）
CREATE STATISTICS stats_products_season_status (mcv)
ON (attrs->>'season'), status FROM products;
ANALYZE products;
- 定期ANALYZE sales, products；大型表设置更高统计目标：
ALTER TABLE products ALTER COLUMN attrs SET STATISTICS 1000;
五、进一步的结构性优化（若该查询高频）
- 预聚合/物化明细
- 建每日级别的增量汇总表 sales_agg_daily(product_id, day, qty_sum, amount_sum)，查询聚合只需汇总近90天的<=90行/产品，大幅降低CPU与I/O；
- 可用触发器/定时任务或流式ETL增量维护。
- 分区
- sales按月份/天范围分区；谓词s.sold_at >= now()-90d会触发分区裁剪，只扫描近3个月分区。现有索引在每个分区建立小一些即可。
- 模型调整
- 若season查询极其高频，可将season升维为显式列并建常规BTREE/部分索引，避免JSONB解析与GIN代价。
六、为什么这些优化有效（对应你的关切点）
- 执行计划：去掉CTE屏障、把产品筛选内联，给优化器更大自由度选择“先小后大”的连接顺序与并行聚合；使用@>让GIN生效。
- 资源消耗：覆盖索引减少大量Heap访问；部分索引将扫描范围缩到“活跃+冬季”；并行聚合降低单核CPU与内存压力。
- 查询效率：驱动小集合、索引范围扫描、减少排序/窗口输入规模；避免CTE物化的中间大结果集读写。
- 索引使用：将谓词改写为@>命中现有GIN；为连接与聚合补齐覆盖列，最大化索引仅扫描；必要时加表达式/部分索引以提升选择性。
七、落地执行顺序（建议）
- 先改SQL为上面的版本并EXPLAIN (ANALYZE, BUFFERS) 验证计划形态；
- 创建 idx_sales_prod_time_cover 索引；ANALYZE sales；
- 如仍有明显Heap访问或CPU瓶颈，再创建 idx_products_active_winter_id_cat（或选择表达式/部分GIN方案之一）；ANALYZE products；
- 调整work_mem与并行参数，消除聚合/排序磁盘溢出；
- 若该查询高频，再评估日级物化/分区方案。
如你能提供EXPLAIN (ANALYZE, BUFFERS) 的实际输出，我可以进一步对节点与行数估计偏差、是否并行、是否溢出等进行针对性微调。
