使用 LIMIT/OffSET 运行一个查询，并获取总行数

出于分页的目的，我需要使用 LIMIT和 OFFSET子句运行一个查询。但是我还需要计算该查询在没有 LIMIT和 OFFSET子句的情况下返回的行数。

我想逃跑:

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

还有:

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

同时。有没有一种方法可以做到这一点，特别是一种方法，让 Postgres 优化它，以便它的速度比运行两个单独？

115346 次浏览
小开

没有。

从理论上讲，如果有足够复杂的引擎，单独运行它们可能会获得一些小小的收益。但是，如果您想知道有多少行匹配一个条件，那么您必须对它们进行计数，而不仅仅是一个有限的子集。

小开
最佳答案

是的。 使用一个简单的窗口函数:

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

请注意，成本将大大高于没有总数，但通常仍然比两个单独的查询便宜。Postgres 必须实际上是 清点所有行，这会根据合格行的总数而增加成本。详情:

但是 ，正如 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太大，就会得到一行 NULL 值，并附加 full_count。否则，它会像第一个查询一样被附加到每一行。

如果具有所有 NULL 值的行可能是有效的结果，则必须检查 offset >= full_count以消除空行的原点的歧义。

这仍然只执行一次基本查询。但是它会给查询增加更多的开销，并且只有在少于为计数重复基本查询的情况下才会付费。

如果支持最终排序顺序的索引是可用的，那么在 CTE 中包含 ORDER BY(多余的)可能是值得的。

小开

编辑: 这个答案在检索未筛选的表时是有效的。我会让它帮助别人，但它可能不完全回答最初的问题。

如果你需要一个精确的数值，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 ?
小开

虽然 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

提交答案