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

纯 SQL

自2008年以来，情况发生了变化。可以使用窗口函数在一个查询中获取完整计数和有限结果。2009 年随 PostgreSQL 8.4 一起推出。

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

请注意，这可能比没有总数要昂贵得多。必须对所有行进行计数，并且可能快捷方式仅从匹配索引中获取顶部行可能不再有用。
小桌子或<= + 并不重要。对于一个更大的问题。full_countOFFSETLIMITfull_count

角例：当至少与基本查询中的行数一样大时，不返回任何行。所以你也得到没有.可能的替代方案：OFFSETfull_count

• 运行具有限制/偏移量的查询，并获取总行数

查询中的事件序列SELECT

（ 0. CTE 分别进行评估和实现。在 Postgres 12 或更高版本中，规划器可以在上班之前内联那些像子查询一样的内容。不在这里。

1. WHERE子句（和条件，尽管在您的示例中没有）从基表中筛选限定行。其余部分基于筛选的子集。JOIN

（ 2. 聚合函数将转到此处。不在这里。GROUP BY

（ 3. 根据分组/聚合列计算其他列表表达式。不在这里。SELECT

4. 窗口函数的应用取决于子句和函数的框架规范。简单基于所有符合条件的行。OVERcount(*) OVER()

5. ORDER BY

（ 6. 或者会去这里。不在这里。DISTINCTDISTINCT ON

7. LIMIT / OFFSET根据已建立的顺序应用以选择要返回的行。

LIMIT / OFFSET随着表中行数的增加，效率变得越来越低下。如果您需要更好的性能，请考虑其他方法：

• 使用大型表上的偏移量优化查询

获取最终计数的替代方案

有完全不同的方法来获取受影响行的计数（而不是应用之前的完整计数）。Postgres具有内部簿记有多少行受到最后一个SQL命令的影响。某些客户端可以访问该信息或自己计算行数（如 psql）。OFFSETLIMIT

例如，您可以在执行 SQL 命令后立即在 plpgsql 中检索受影响的行数：

GET DIAGNOSTICS integer_var = ROW_COUNT;

手册中的详细信息。

或者你可以在 PHP 中使用pg_num_rows。或其他客户端中的类似函数。

相关：

• 在 PostgreSQL 中计算受批处理查询影响的行数

正如我在博客上所描述的那样，MySQL有一个名为SQL_CALC_FOUND_ROWS的功能。这消除了执行查询两次的需要，但它仍然需要完整地执行查询，即使 limit 子句允许它提前停止。

据我所知，PostgreSQL没有类似的功能。在进行分页时要注意的一件事（恕我直言，这是使用LIMIT的最常见事情）：做“OFFSET 1000 LIMIT 10”意味着数据库必须至少获取1010行，即使它只给你10行。一种更高性能的方法是记住您正在为前一行排序的行的值（在本例中为第 1000 行），并像这样重写查询：“...order_row > value_of_1000_th限制为10”。优点是“order_row”最有可能被索引（如果没有，你就出了问题）。缺点是，如果在页面视图之间添加新元素，这可能会有点不同步（但话说回来，访问者可能无法观察到它，并且可能是一个很大的性能提升）。