使用LIMIT/OFFSET运行查询并获取总行数

Question

为了分页需要，我需要运行一个带有 LIMIT 和 OFFSET 子句的查询。但是我也需要一个不带 LIMIT 和 OFFSET 子句的查询所返回的行数计数。

我想要运行：

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

并且：

SELECT COUNT(*) FROM table WHERE /* whatever */

同时，有没有一种方法可以实现这个目标，特别是一种让Postgres进行优化以使其比单独运行更快的方法？

- Tim

---

这个回答解决了你的问题吗？在应用LIMIT之前获取结果计数的最佳方法 - Marty Neal

Answer 1

是的。 通过一个简单的窗口函数：

SELECT *, <b>count(*) OVER() AS full_count</b>
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

请注意，包含总行数的查询成本会大幅上升，但通常仍然比两个单独的查询便宜。无论哪种方式，Postgres都必须实际计算所有行，这取决于符合条件的行的总数而带来一定的成本。详见：

• 在应用LIMIT之前获取结果计数的最佳方法

然而，正如Dani所指出的，当OFFSET至少与基础查询返回的行数一样多时，将不返回任何行。因此我们也无法得到full_count。

如果这种情况不可接受，则可能的解决方法是使用CTE和OUTER JOIN始终返回完整计数。

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

如果OFFSET太大，你会得到一个包含full_count的NULL值行。否则，它将像第一次查询中一样附加到每一行。

如果所有NULL值的行是可能的有效结果，则必须检查offset >= full_count以消除空行的来源不明确性。

这仍然只对基本查询执行一次。但它增加了查询的开销，只有在比重复计算基本查询更划算时才使用。

如果支持最终排序顺序的索引可用，则将ORDER BY包含在CTE中（冗余）可能会更划算。

- Erwin Brandstetter

---

根据限制和条件，我们需要返回行，但是给定的偏移量不会返回任何结果。在这种情况下，我们如何获取行数？ - Dani Mathew

@julealgon：请开一个新问题并提供详细信息。您可以随时链接到这个问题以提供背景，并在此处留下评论以链接回来（并引起我的注意）。 - Erwin Brandstetter

对于任何想知道的人；如果您还想限制视图上执行的COUNT(*)，例如当您有一个巨大的表并希望防止计算超出某个特定数量的所有内容时，则可以使用：COUNT(*) OVER(ROWS BETWEEN CURRENT ROW AND 1000 FOLLOWING)，其中1000是计数将停止的数字，无论您的查询（不带LIMIT）是否会返回更多行。 - Arthur

@JustinL.：增加的开销只对相对较便宜的基本查询有显著影响。此外，Postgres 12 在多个方面改进了CTE性能。（尽管此CTE默认情况下仍为MATERIALIZED，被引用两次。） - Erwin Brandstetter

关于 TABLE，请参见 https://dev59.com/cF0a5IYBdhLWcg3wfIp-#30276023 - Erwin Brandstetter

Answer 2

虽然Erwin Brandstetter的答案很出色，但它会像下面这样在每一行返回总行数:

col1 - col2 - col3 - total
--------------------------
aaaa - aaaa - aaaa - count
bbbb - bbbb - bbbb - count
cccc - cccc - cccc - count

您可能希望考虑使用一种仅返回总计数一次的方法，如下所示：

total - rows
------------
count - [{col1: 'aaaa'},{col2: 'aaaa'},{col3: 'aaaa'}
         {col1: 'bbbb'},{col2: 'bbbb'},{col3: 'bbbb'}
         {col1: 'cccc'},{col2: 'cccc'},{col3: 'cccc'}]

SQL查询：

SELECT
    (SELECT COUNT(*) 
     FROM table
     WHERE /* sth */
    ) as count, 
    (SELECT json_agg(t.*) FROM (
        SELECT * FROM table
        WHERE /* sth */
        ORDER BY col1
        OFFSET ?
        LIMIT ?
    ) AS t) AS rows 

- treecon

---

你还需要在 count(*) 子查询中加上 WHERE，否则你只会得到整个表的计数，不是吗？ - Ben Neill

@BenNeill 你是对的，我编辑了答案以包含你的修复。 - treecon

Answer 3

编辑：此答案适用于检索未过滤的表。 我将保留它以防有助于某人，但可能并不完全回答最初的问题。

Erwin Brandstetter 的答案非常准确。 然而，在大型表上，您通常只需要相对准确的估计值。 Postgres 提供了这个功能，并且速度会更快，因为它无需评估每一行：

SELECT *
FROM (
    SELECT *
    FROM tbl
    WHERE /* something */
    ORDER BY /* something */
    OFFSET ?
    LIMIT ?
    ) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;

我其实不确定将 RIGHT JOIN 外置是否有优势，或者将其保留在标准查询中是否更好。这需要进行一些测试。

SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?

- François Gueguen

---

关于快速计数估算（Fast Count Estimate）：https://dev59.com/wGsz5IYBdhLWcg3wYGkz#7945274 就像你所说的：只有在检索整个表格时才有效——这与你的查询中的WHERE子句相矛盾。第二个查询在逻辑上是错误的（检索数据库中每个表的一行），并且在纠正后更昂贵。 - Erwin Brandstetter

Answer 4

不。

理论上，如果在底层使用足够复杂的机制，或许可以在运行它们各自时获得一些微小的收益。但是，如果你想知道有多少行符合某个条件，你必须对它们进行计数，而不仅仅是限制在一个子集中。

- Richard Huxton