PostgreSQL大子集查询优化：商品评论分类筛选性能提升问询

现有一张包含2亿行数据的商品评论表，需按分类筛选评论，单个分类可能包含20万唯一product_id，查询需从大表中处理20万行的大子集。

查询示例 #

explain analyze
select r.*
from (select distinct on (r.product_id) r.*
      from reviews as r
               join review_product_info as pi on pi.product_id = r.product_id
      where (pi.categories @> array['4572']::varchar(256)[] and r.publication_status in ('PUBLISHED'::review_publication_status_enum))
      order by r.product_id, r.likes desc, r.created desc) as r
order by r.created desc
offset 0 rows fetch next 21 rows only;

执行计划 #

Limit  (cost=309701.99..309702.04 rows=21 width=899) (actual time=1594.890..1743.813 rows=21 loops=1)
->  Sort  (cost=309701.99..309785.83 rows=33534 width=899) (actual time=1577.723..1726.642 rows=21 loops=1)
    Sort Key: r.created DESC
    Sort Method: top-N heapsort  Memory: 39kB
    ->  Subquery Scan on r  (cost=1003.71..308797.86 rows=33534 width=899) (actual time=42.000..1686.791 rows=76098 loops=1)
          ->  Unique  (cost=1003.71..308462.52 rows=33534 width=3644) (actual time=41.995..1660.933 rows=76098 loops=1)
                ->  Gather Merge  (cost=1003.71..306869.56 rows=637184 width=3644) (actual time=41.991..1596.437 rows=389237 loops=1)
                      Workers Planned: 4
                      Workers Launched: 4
                      ->  Incremental Sort  (cost=3.65..229974.75 rows=159296 width=3644) (actual time=17.795..677.632 rows=77847 loops=5)
                            Sort Key: r_1.product_id, r_1.likes DESC, r_1.created DESC
                            Presorted Key: r_1.product_id
                            Full-sort Groups: 1071  Sort Method: quicksort  Average Memory: 55kB  Peak Memory: 58kB
                            Pre-sorted Groups: 208  Sort Method: quicksort  Average Memory: 106kB  Peak Memory: 167kB
                            Worker 0:  Full-sort Groups: 2096  Sort Method: quicksort  Average Memory: 58kB  Peak Memory: 68kB
                              Pre-sorted Groups: 437  Sort Method: quicksort  Average Memory: 140kB  Peak Memory: 173kB
                            Worker 1:  Full-sort Groups: 2080  Sort Method: quicksort  Average Memory: 63kB  Peak Memory: 65kB
                              Pre-sorted Groups: 350  Sort Method: quicksort  Average Memory: 101kB  Peak Memory: 109kB
                            Worker 2:  Full-sort Groups: 2047  Sort Method: quicksort  Average Memory: 59kB  Peak Memory: 68kB
                              Pre-sorted Groups: 357  Sort Method: quicksort  Average Memory: 217kB  Peak Memory: 240kB
                            Worker 3:  Full-sort Groups: 2038  Sort Method: quicksort  Average Memory: 65kB  Peak Memory: 74kB
                              Pre-sorted Groups: 396  Sort Method: quicksort  Average Memory: 104kB  Peak Memory: 113kB
                            ->  Nested Loop  (cost=0.85..224299.36 rows=159296 width=3644) (actual time=10.717..508.580 rows=77847 loops=5)
                                  ->  Parallel Index Only Scan using reviews_categories_idx_test5 on review_product_info pi  (cost=0.42..25169.46 rows=20157 width=7) (actual time=10.606..130.585 rows=16227 loops=5)
                                        Filter: (categories @> '{4572}'::character varying(256)[])
                                        Rows Removed by Filter: 24063
                                        Heap Fetches: 39028
                                  ->  Index Scan using reviews_product_id_published_idx on reviews r_1  (cost=0.43..9.41 rows=47 width=899) (actual time=0.008..0.021 rows=5 loops=81134)
                                        Index Cond: ((product_id)::text = (pi.product_id)::text)
Planning Time: 0.697 ms
JIT:
  Functions: 36
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 7.050 ms, Inlining 0.000 ms, Optimization 3.928 ms, Emission 56.984 ms, Total 67.961 ms
Execution Time: 1745.852 ms

当前无论采用独立分类表还是在评论表中内嵌数组类型的分类列，均需先扫描大表的大子集，再通过distinct on、排序、分页进行数据缩减。请问该类查询是否存在优化空间？

当然存在优化空间，以下是几种实用的优化思路：

1. 预计算热门评论（物化视图/汇总表） #

针对每个分类，提前计算出每个商品的最热门评论（按likes desc, created desc排序的第一条），并按created desc维护排序。可以用物化视图实现：

CREATE MATERIALIZED VIEW category_top_reviews AS
SELECT DISTINCT ON (pi.categories, r.product_id)
       pi.categories, r.*
FROM reviews r
JOIN review_product_info pi ON r.product_id = pi.product_id
WHERE r.publication_status = 'PUBLISHED'
ORDER BY pi.categories, r.product_id, r.likes DESC, r.created DESC;

给物化视图添加针对性索引：

-- 支持分类快速筛选
CREATE INDEX idx_category_top_reviews_category ON category_top_reviews USING GIN (categories);
-- 支持按创建时间快速排序取topN
CREATE INDEX idx_category_top_reviews_created ON category_top_reviews (created DESC);

查询时直接从物化视图获取数据：

SELECT *
FROM category_top_reviews
WHERE categories @> array['4572']::varchar(256)[]
ORDER BY created DESC
LIMIT 21;

这种方式把大量聚合排序工作提前完成，查询时仅需快速筛选和取topN，性能会大幅提升。可以通过REFRESH MATERIALIZED VIEW CONCURRENTLY定时刷新视图，保证数据新鲜度，适合对实时性要求不是极高的场景。

2. 优化索引策略 #

优化review_product_info表的索引 #

当前索引在筛选分类时需要过滤大量数据且存在Heap Fetches，可创建包含product_id的GIN索引，避免回表：

CREATE INDEX idx_review_product_info_categories_product ON review_product_info USING GIN (categories) INCLUDE (product_id);

优化reviews表的索引 #

扩展现有索引为覆盖索引，包含排序和返回所需的字段，避免访问主表：

CREATE INDEX idx_reviews_product_published_likes_created ON reviews (product_id, publication_status) INCLUDE (likes, created, /* 其他查询需要的字段 */);

3. 调整查询逻辑，避免全局排序 #

当前查询需先获取所有符合条件的商品热门评论，再全局排序取top21。可尝试反向遍历：按created desc顺序扫描评论，筛选属于目标分类且是该商品第一条热门评论的记录，直到凑够21条：

WITH ranked_reviews AS (
    SELECT r.*,
           ROW_NUMBER() OVER (PARTITION BY r.product_id ORDER BY r.likes DESC, r.created DESC) AS rn,
           pi.categories
    FROM reviews r
    JOIN review_product_info pi ON r.product_id = pi.product_id
    WHERE r.publication_status = 'PUBLISHED'
    ORDER BY r.created DESC
)
SELECT *
FROM ranked_reviews
WHERE rn = 1 AND categories @> array['4572']::varchar(256)[]
LIMIT 21;

这种方式可能提前终止扫描，无需处理所有符合条件的评论，尤其当最新的21条热门评论分布在不同商品时，能大幅减少数据处理量。需测试执行计划，确保PostgreSQL能利用created desc索引做顺序扫描。

4. 分区表优化 #

针对2亿行的评论表，可按created字段（如按月份）做分区，查询时仅扫描最近的几个分区，减少数据处理量。结合上述索引和预计算策略，能进一步提升性能。

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