postgresql - 检索相关数据行 : subquery, 或使用 GROUP BY 单独查询的最有效方法？

Question

我有一个非常简单的 PostgreSQL 查询来检索最新的 50 篇新闻文章:

SELECT id, headline, author_name, body 
 FROM news 
 ORDER BY publish_date DESC 
 LIMIT 50

现在我还想检索每篇文章的最新 10 条评论。我可以想到两种方法来完成检索它们，但我不确定哪种方法最适合 PostgreSQL:

---

选项 1:

直接对原查询中的评论进行子查询，并将结果转换为数组:

SELECT headline, author_name, body,
  ARRAY(
   SELECT id, message, author_name,
     FROM news_comments
     WHERE news_id = n.id
     ORDER BY DATE DESC
     LIMIT 10
  ) AS comments
 FROM news n
 ORDER BY publish_date DESC 
 LIMIT 50

显然，在这种情况下，应用程序逻辑需要知道数组中的哪个索引是哪一列，这没问题。

我发现该方法存在的一个问题是不知道查询规划器将如何执行它。这会有效地变成 51 个查询吗？

---

选项 2:

使用原来非常简单的查询:

SELECT id, headline, author_name, body 
 FROM news 
 ORDER BY publish_date DESC 
 LIMIT 50

然后通过应用程序逻辑，收集所有新闻 ID 并在单独的查询中使用它们，必须在此处使用 row_number() 以限制每篇新闻文章的结果数量:

SELECT *
FROM (
    SELECT *,
        row_number() OVER(
            PARTITION BY author_id
            ORDER BY author_id DESC
        ) AS rn
    FROM (
        SELECT *
          FROM news_comment
          WHERE news_id IN(123, 456, 789)
    ) s
) s
where rn <= 10

这种方法显然更复杂，我不确定这是否必须首先检索范围新闻文章的所有评论，然后砍掉行数大于的评论10.

---

哪个选项最好？还是我忽略了更好的解决方案？

就上下文而言，这是我自己开发的新闻聚合网站，目前我有大约 40,000 篇新闻文章，涵盖多个类别，评论大约有 500,000 条，因此我正在寻找帮助我不断成长的最佳解决方案。

Answer 1

您应该至少使用 EXPLAIN ANALYZE 调查语句的执行计划.这将为您提供优化器在执行语句本身时选择的计划，并返回实际运行时间和其他统计信息。

另一种解决方案是使用 LATERAL 子查询在单独的行中为每个新闻检索 10 条评论，但话又说回来——您需要调查和比较计划以选择最适合您的方法:

SELECT 
  n.id, n.headline, n.uathor_name, n.body, 
  c.id, c.message, c.author_name
FROM news n
LEFT JOIN LATERAL ( 
  SELECT id, message, author_name
  FROM news_comments nc
  WHERE n.id = nc.news_id
  ORDER BY nc.date DESC
  LIMIT 10
  ) c ON TRUE
ORDER BY publish_date DESC
LIMIT 50

当您的查询包含 LATERAL 时从 news 检索到的每一行的交叉引用 LATERAL 使用 WHERE 中的连接进行评估条款。因此使其重复执行并加入从源表 news 的每一行检索的信息。

这种方法可以节省您的应用程序逻辑处理来自选项 1 的数组所需的时间，同时不必像选项 2 为您(在这种情况下)节省打开单独事务、建立连接、检索行等所需的时间...

最好通过创建索引并查看规划器成本常量和规划器方法配置参数来寻求性能改进，您可以试验这些参数以了解规划器做出的选择。有关该主题的更多信息 here .