【转】Best way to get result count before LIMIT was applied
怎么样才能只执行一条sql语句,就返回某一页的数据,同时返回总条数?
窗口函数 count(*) over()
-------------------------------
When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls.
Currently I do that by running the query twice, once wrapped in a count()
to determine the total results, and a second time with a limit applied to get back just the results I need for the current page.
This seems inefficient. Is there a better way to determine how many results would have been returned before LIMIT
was applied?
I am using PHP and Postgres.
Pure SQL
Things have changed since 2008. You can use a window function to get the full count and the limited result in one query. Introduced with PostgreSQL 8.4 in 2009.
SELECT foo
, count(*) OVER() AS full_count
FROM bar
WHERE <some condition>
ORDER BY <some col>
LIMIT <pagesize>
OFFSET <offset>;
Note that this can be considerably more expensive than without the total count. All rows have to be counted, and a possible shortcut taking just the top rows from a matching index may not be helpful any more.
Doesn't matter much with small tables or full_count
<= OFFSET
+ LIMIT
. Matters for a substantially bigger full_count
.
Corner case: when OFFSET
is at least as great as the number of rows from the base query, no row is returned. So you also get no full_count
. Possible alternative:
Sequence of events in a SELECT
query
( 0. CTEs are evaluated and materialized separately. In Postgres 12 or later the planner may inline those like subqueries before going to work.) Not here.
WHERE
clause (andJOIN
conditions, though none in your example) filter qualifying rows from the base table(s). The rest is based on the filtered subset.
( 2. GROUP BY
and aggregate functions would go here.) Not here.
( 3. Other SELECT
list expressions are evaluated, based on grouped / aggregated columns.) Not here.
-
Window functions are applied depending on the
OVER
clause and the frame specification of the function. The simplecount(*) OVER()
is based on all qualifying rows. -
ORDER BY
( 6. DISTINCT
or DISTINCT ON
would go here.) Not here.
LIMIT
/OFFSET
are applied based on the established order to select rows to return.
LIMIT
/ OFFSET
becomes increasingly inefficient with a growing number of rows in the table. Consider alternative approaches if you need better performance:
Alternatives to get final count
There are completely different approaches to get the count of affected rows (not the full count before OFFSET
& LIMIT
were applied). Postgres has internal bookkeeping how many rows where affected by the last SQL command. Some clients can access that information or count rows themselves (like psql).
For instance, you can retrieve the number of affected rows in plpgsql immediately after executing an SQL command with:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Or you can use pg_num_rows
in PHP. Or similar functions in other clients.
Related:
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 25岁的心里话
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现