如何优化社交媒体Feed的PostgreSQL查询？

需求说明 #

需要返回以下数据：

• 帖子总数
• 帖子详情（含作者信息、点赞状态、点赞数、评论总数）
• 每个帖子的前10条评论（含评论作者信息、点赞状态、点赞数、回复总数）
• 每条评论的最新1条回复（若存在，含回复作者信息、点赞状态、点赞数）
  评论仅支持一层嵌套。当前查询存在明显性能瓶颈，以下是具体优化方案：

原查询代码 #

SELECT
    COUNT(*) as "totalCount",
    array(
      SELECT
        jsonb_build_object(
          'postID', p.post_id,
          'userID', p.user_id,
          'textContent', p.text_content,
          'thumbnailImage',p.thumbnail_image,
          'title', p.title,
          'createdAt', p.created_at,
          'displayName', c.display_name,
          'profileImage', c.profile_image,
          'isLiked', (SELECT status FROM PostLike WHERE user_id = $1 AND post_id = p.post_id),
          'likeCount', COUNT(l.post_id) FILTER (WHERE l.status = TRUE),
          'commentCount', COUNT(DISTINCT co.comment_id),
          'comments', array(
            SELECT
              jsonb_build_object(
                'comment', jsonb_build_object(
                  'commentID', c.comment_id,
                  'createdAt', c.created_at,
                  'parentID', c.parent_id,
                  'postID', c.post_id,
                  'textContent', c.text_content,
                  'displayName', uc.display_name,
                  'profileImage', uc.profile_image,
                  'isLiked', (SELECT status FROM CommentLike WHERE user_id = $1 AND comment_id = c.comment_id),
                  'likeCount', COUNT(lc.comment_id) FILTER (WHERE lc.status = TRUE),
                  'userID', uc.user_id,
                  'replyCount', (SELECT COUNT(*) FROM Comments WHERE parent_id = c.comment_id)
                ),
                'replies', 
                  array(SELECT
                    jsonb_build_object(
                      'commentID', replies.comment_id,
                      'createdAt', replies.created_at,
                      'parentID', replies.parent_id,
                      'postID', replies.post_id,
                      'textContent', replies.text_content,
                      'displayName', uc2.display_name,
                      'profileImage', uc2.profile_image,
                      'userID', uc2.user_id,
                      'isLiked', (SELECT status FROM CommentLike WHERE user_id = $1 AND comment_id = replies.comment_id),
                      'likeCount', COUNT(lc2.comment_id) FILTER (WHERE lc2.status = TRUE)
                    ) AS replies
                    FROM Comments AS replies
                    LEFT JOIN Users uc2 ON uc2.user_id = replies.user_id
                    LEFT JOIN CommentLike lc2 ON lc2.comment_id = replies.comment_id
                    WHERE c.comment_id = replies.parent_id
                    GROUP BY parent_id, replies.comment_id, uc2.user_id
                    ORDER BY replies.created_at DESC
                    LIMIT 1
                  )
                )
              FROM Comments c
              LEFT JOIN Users uc ON uc.user_id = c.user_id
              LEFT JOIN CommentLike lc ON lc.comment_id = c.comment_id
              WHERE c.parent_id IS NULL AND c.post_id = p.post_id
              GROUP BY c.comment_id, uc.user_id
              ORDER BY c.created_at DESC
              LIMIT 10
              )
            )
          FROM POSTS p
          LEFT JOIN PostLike l ON l.post_id = p.post_id
          LEFT JOIN Subscriptions s ON s.page_url = p.page_url
          LEFT JOIN Communities c ON c.page_url = p.page_url
          LEFT JOIN Comments co ON co.post_id = p.post_id
          WHERE s.user_id = $1
          GROUP BY p.post_id, c.page_url
          ORDER BY p.created_at DESC
          LIMIT 10
          OFFSET (($2 -1) * 10)) as "posts"
      FROM Posts pCount
      LEFT JOIN Subscriptions sCount ON sCount.page_url = pCount.page_url
      WHERE sCount.user_id = $1

优化方案 #

1. 用CTE预聚合统计数据，消除冗余子查询

将重复查询的点赞状态、点赞数、回复数提前聚合，避免每行数据重复执行子查询：

• 聚合用户对帖子的点赞状态
• 聚合帖子的总点赞数
• 聚合用户对评论的点赞状态
• 聚合评论的总点赞数
• 聚合每条评论的回复总数

2. 用窗口函数筛选最新回复，避免嵌套查询

对回复数据按父评论分组，用ROW_NUMBER()标记最新的一条回复，直接关联到主评论查询中，无需为每条评论单独查询回复。

3. 减少JOIN后的数据膨胀

先对关联表（PostLike、Comments）做聚合，再与主表JOIN，避免一对多JOIN导致临时数据量暴增，降低GROUP BY的计算成本。

4. 添加针对性索引

创建以下索引提升查询效率：

-- 快速筛选用户订阅的帖子
CREATE INDEX idx_subscriptions_user_page ON Subscriptions(user_id, page_url);
-- 快速获取用户对帖子的点赞状态
CREATE INDEX idx_postlike_user_post ON PostLike(user_id, post_id) INCLUDE (status);
-- 快速聚合帖子总点赞数
CREATE INDEX idx_postlike_post_status ON PostLike(post_id, status);
-- 快速获取用户对评论的点赞状态
CREATE INDEX idx_commentlike_user_comment ON CommentLike(user_id, comment_id) INCLUDE (status);
-- 快速聚合评论总点赞数
CREATE INDEX idx_commentlike_comment_status ON CommentLike(comment_id, status);
-- 快速筛选帖子的顶级评论并排序
CREATE INDEX idx_comments_post_parent_created ON Comments(post_id, parent_id, created_at DESC);
-- 快速筛选评论的回复并排序
CREATE INDEX idx_comments_parent_created ON Comments(parent_id, created_at DESC);
-- 快速按订阅筛选帖子并排序
CREATE INDEX idx_posts_page_created ON Posts(page_url, created_at DESC);

优化后的查询示例 #

WITH user_post_likes AS (
    -- 用户对帖子的点赞状态
    SELECT post_id, status
    FROM PostLike
    WHERE user_id = $1
),
post_like_counts AS (
    -- 帖子总点赞数
    SELECT post_id, COUNT(*) AS like_count
    FROM PostLike
    WHERE status = TRUE
    GROUP BY post_id
),
user_comment_likes AS (
    -- 用户对评论的点赞状态
    SELECT comment_id, status
    FROM CommentLike
    WHERE user_id = $1
),
comment_like_counts AS (
    -- 评论总点赞数
    SELECT comment_id, COUNT(*) AS like_count
    FROM CommentLike
    WHERE status = TRUE
    GROUP BY comment_id
),
comment_reply_counts AS (
    -- 评论的回复总数
    SELECT parent_id, COUNT(*) AS reply_count
    FROM Comments
    WHERE parent_id IS NOT NULL
    GROUP BY parent_id
),
latest_replies AS (
    -- 每条评论的最新1条回复
    SELECT
        r.parent_id,
        r.comment_id,
        r.created_at,
        r.post_id,
        r.text_content,
        uc.display_name,
        uc.profile_image,
        uc.user_id,
        ucl.status AS is_liked,
        clc.like_count
    FROM Comments r
    LEFT JOIN Users uc ON uc.user_id = r.user_id
    LEFT JOIN user_comment_likes ucl ON ucl.comment_id = r.comment_id
    LEFT JOIN comment_like_counts clc ON clc.comment_id = r.comment_id
    WHERE r.parent_id IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY r.parent_id ORDER BY r.created_at DESC) = 1
)
SELECT
    (SELECT COUNT(*) FROM Posts pCount JOIN Subscriptions sCount ON sCount.page_url = pCount.page_url WHERE sCount.user_id = $1) AS "totalCount",
    array_agg(
        jsonb_build_object(
            'postID', p.post_id,
            'userID', p.user_id,
            'textContent', p.text_content,
            'thumbnailImage', p.thumbnail_image,
            'title', p.title,
            'createdAt', p.created_at,
            'displayName', c.display_name,
            'profileImage', c.profile_image,
            'isLiked', COALESCE(upl.status, FALSE),
            'likeCount', COALESCE(plc.like_count, 0),
            'commentCount', COALESCE(cc.comment_count, 0),
            'comments', (
                SELECT array_agg(
                    jsonb_build_object(
                        'comment', jsonb_build_object(
                            'commentID', com.comment_id,
                            'createdAt', com.created_at,
                            'parentID', com.parent_id,
                            'postID', com.post_id,
                            'textContent', com.text_content,
                            'displayName', uc.display_name,
                            'profileImage', uc.profile_image,
                            'isLiked', COALESCE(ucl.status, FALSE),
                            'likeCount', COALESCE(clc.like_count, 0),
                            'userID', uc.user_id,
                            'replyCount', COALESCE(crc.reply_count, 0)
                        ),
                        'replies', (
                            SELECT jsonb_build_object(
                                'commentID', lr.comment_id,
                                'createdAt', lr.created_at,
                                'parentID', lr.parent_id,
                                'postID', lr.post_id,
                                'textContent', lr.text_content,
                                'displayName', lr.display_name,
                                'profileImage', lr.profile_image,
                                'userID', lr.user_id,
                                'isLiked', COALESCE(lr.is_liked, FALSE),
                                'likeCount', COALESCE(lr.like_count, 0)
                            )
                            FROM latest_replies lr
                            WHERE lr.parent_id = com.comment_id
                        )
                    )
                )
                FROM Comments com
                LEFT JOIN Users uc ON uc.user_id = com.user_id
                LEFT JOIN user_comment_likes ucl ON ucl.comment_id = com.comment_id
                LEFT JOIN comment_like_counts clc ON clc.comment_id = com.comment_id
                LEFT JOIN comment_reply_counts crc ON crc.parent_id = com.comment_id
                WHERE com.parent_id IS NULL AND com.post_id = p.post_id
                ORDER BY com.created_at DESC
                LIMIT 10
            )
        )
        ORDER BY p.created_at DESC
        LIMIT 10
        OFFSET (($2 - 1) * 10)
    ) AS "posts"
FROM Posts p
JOIN Subscriptions s ON s.page_url = p.page_url
JOIN Communities c ON c.page_url = p.page_url
LEFT JOIN user_post_likes upl ON upl.post_id = p.post_id
LEFT JOIN post_like_counts plc ON plc.post_id = p.post_id
LEFT JOIN (
    SELECT post_id, COUNT(*) AS comment_count
    FROM Comments
    WHERE parent_id IS NULL
    GROUP BY post_id
) cc ON cc.post_id = p.post_id
WHERE s.user_id = $1
GROUP BY p.post_id, c.display_name, c.profile_image, upl.status, plc.like_count, cc.comment_count;

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