使用 LIMIT/OFFSET 运行查询并获取总行数 [英] Run a query with a LIMIT/OFFSET and also get the total number of rows

问题描述

出于分页目的，我需要使用 LIMIT 和 OFFSET 子句运行查询.但我还需要一个没有 LIMIT 和 OFFSET 子句的查询将返回的行数的计数.

For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET clauses.

我想跑:

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

还有:

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

同时.有没有办法做到这一点，特别是一种让 Postgres 对其进行优化的方法，使其比单独运行两者更快?

At the same time. Is there a way to do that, particularly a way that lets Postgres optimize it, so that it's faster than running both individually?

推荐答案

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

Yes. With a simple window function:

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

请注意，与没有总数的情况相比，成本会高得多，但通常仍比两个单独的查询便宜.Postgres 必须实际计算所有行，这取决于符合条件的行的总数.详情:

Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rows either way, which imposes a cost depending on the total number of qualifying rows. Details:

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

但是，正如 Dani 指出的，当 OFFSET 至少与从基数返回的行数一样大时查询，不返回任何行.所以我们也没有得到 full_count.

However, as Dani pointed out, when OFFSET is at least as great as the number of rows returned from the base query, no rows are returned. So we also don't get full_count.

如果这是不可接受的，一个可能的总是返回完整计数的解决方法是使用 CTE 和 OUTER JOIN:

If that's not acceptable, a possible workaround to always return the full count would be with a CTE and an 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.否则，它会像第一个查询一样附加到每一行.

You get one row of NULL values with the full_count appended if OFFSET is too big. Else, it's appended to every row like in the first query.

如果包含所有 NULL 值的行是一个可能的有效结果，您必须检查 offset >= full_count 以消除空行的来源.

If a row with all NULL values is a possible valid result you have to check offset >= full_count to disambiguate the origin of the empty row.

这仍然只执行一次基本查询.但它会增加查询的开销，并且仅在少于重复基本查询计数时才付费.

This still executes the base query only once. But it adds more overhead to the query and only pays if that's less than repeating the base query for the count.

如果支持最终排序顺序的索引可用，则可能需要在 CTE 中包含 ORDER BY(冗余).

If indexes supporting the final sort order are available, it might pay to include the ORDER BY in the CTE (redundantly).

这篇关于使用 LIMIT/OFFSET 运行查询并获取总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！