For pagination purposes, I need a run a query with the LIMIT
and OFFSET
clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT
and OFFSET
clauses.
I want to run:
SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?
And:
SELECT COUNT(*) FROM table WHERE /* whatever */
At the same time. Is there a way to do that, particularly a way that lets Postgres optimize it, so that it's faster than running both individually?
Yes. With a simple window function:
SELECT *, count(*) OVER() AS full_count
FROM tbl
WHERE /* whatever */
ORDER BY col1
OFFSET ?
LIMIT ?
Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rows either way, which imposes a cost depending on the total number of qualifying rows. Details:
However, as Dani pointed out, when OFFSET
is at least as great as the number of rows returned from the base query, no rows are returned. So we also don't get full_count
.
If that's not acceptable, a possible workaround to always return the full count would be with a CTE and an 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;
You get one row of NULL values with the full_count
appended if OFFSET
is too big. Else, it's appended to every row like in the first query.
If a row with all NULL values is a possible valid result you have to check offset >= full_count
to disambiguate the origin of the empty row.
This still executes the base query only once. But it adds more overhead to the query and only pays if that's less than repeating the base query for the count.
If indexes supporting the final sort order are available, it might pay to include the ORDER BY
in the CTE (redundantly).