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

问题描述

出于分页目的，我需要使用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之前获得结果计数的最佳方法

但是，，当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.

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

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

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