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
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 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:
WHERE clause (and
JOIN conditions, but not here) filter qualifying rows from the base table(s).
GROUP BY and aggregate functions would go here.)
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 ON would go here.)
OFFSET are applied based on the established order to select rows to return.
OFFSET becomes increasingly inefficient with a growing number of rows in the table. Consider alternative approaches if you need better performance: