How can I optimize this postgres query? Social Media Feed (posts, comments, replies)

Viewed 31

I wrote this monster sql query (at least it's massive to me as someone who doesn't usually touch sql), and I feel like I'm almost 100% doing something inefficiently here. Would love any opinions on how I can improve this.

For context I've been working on a query that in the end should return a total number of posts, posts, comments for each post, total comments count for a post, the first reply to each comment if they have one, and the total number of replies to each comment. Only allowing comments to go one layer deep.

Here is the query:

   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 Answers

I don't know your table design structures, But I can write some recommendations for you, maybe it will be necessary.

  1. Pay attention to the field indexes that are used in grouping, ordering and conditions.
  2. If you have subqueries with the same results, you can use CTE (with materialized) to optimize these queries, so that these queries are executed once. Maybe your subqueries are not the same, but somehow you can find some identical small queries that are used in other joins or other subqueries.

Other things are fine, I think.

Related