时间戳

首页 > 解决方案 > 如何有效地按子查询的结果排序？

sql - 如何有效地按子查询的结果排序？

问题描述

假设我有一个像 Stackoverflow 这样的网站，其中包含人们可以回复的帖子，我想要一个界面来按回复计数对帖子进行排序

这最终将有无限滚动分页，因此一次显示 10 个结果。

这是一个示例查询：

SELECT *, (SELECT COUNT(*) 
           FROM post_reply pr 
           WHERE pr.convo_id = post.convo_id) as replies 
FROM post 
ORDER BY replies 
LIMIT 10;

这可行，但速度非常慢。我有数十万个帖子，这导致查询需要 30 多秒才能完成。

索引会提高速度，但我不知道如何在子查询上实现索引。

物化视图也可以工作，但每次有人回复帖子时更新物化视图似乎也非常慢。

这个问题有好的解决方案吗？

标签： sqlpostgresqlperformanceindexingsql-order-by

解决方案

您可以更改查询顺序并首先按回复计数生成帖子列表，然后获取post列。这应该使用主键（我假设post.convo_id是一个）并且可能会更快，但我不保证它会。

SELECT post.*, sub.replies
  FROM (SELECT pr.convo_id, COUNT(*) AS replies
          FROM post_reply pr
         GROUP BY pr.convo_id
         ORDER BY replies --maybe DESC if you want top reply count first
         LIMIT 10
       ) AS sub
  JOIN post USING(convo_id);

推荐阅读