EvilPuppetMaster EvilPuppetMaster - 1 year ago 60
SQL Question

Best way to get result count before LIMIT was applied

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

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
was applied?

I am using PHP and Postgres.

Answer Source

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).

      ,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 shortcut taking just the top rows from a matching index is not possible.
Doesn't matter much with small tables, matters with big tables.

Consider the sequence of events:

  1. WHERE clause (and JOIN conditions, but not here) filter qualifying rows from the base table(s).

    (GROUP BY and aggregate functions would go here.)

  2. Window functions are applied considering all qualifying rows (depending on the OVER clause and the frame specification of the function). The simple count(*) OVER() is based on all rows.


    (DISTINCT or DISTINCT ON would go here.)

  4. LIMIT / OFFSET are applied based on the established order to select rows to return.

Note that LIMIT / OFFSET becomes increasingly inefficient with a growing number of rows in the table. Consider alternative approaches if you need better performance:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download