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

Question

出于分页目的，我需要使用LIMIT和OFFSET子句运行查询。但是我还需要计算不带LIMITandOFFSET子句的查询将返回的行数。

我要跑步：

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

和：

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

同时。有没有办法做到这一点，特别是让Postgres优化它的方法，使其比单独运行它们都快？

Answer 1

是的。 具有简单的窗口功能：

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;

如果行太大，full_count则会得到一行NULL值，并附加OFFSET。否则，它会像第一个查询一样附加到每一行。

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

这仍然只执行一次基本查询。但这会增加查询的开销，并且只有在比重复进行基本查询的次数少的情况下才需要付费。

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