Recently, during a performance improvement sweep for an application at my company, one of the hotspots that was discovered was pagination.
In order to display the correct pagination links on the page, the pagination library we used (most pagination libraries for that matter) ran the query with OFFSET and LIMIT to get the paginated results, and then re-ran the query without the OFFSET and LIMIT and wrapped them in a SELECT COUNT(*) FROM main_query to get the total number of rows.
In an attempt to optimize this, we used a window function as follows:
Given a query that looked as follows:
SELECT a,b,c
FROM table
WHERE clauses
OFFSET x LIMIT y;
add total_entries_count column as follows:
SELECT COUNT(table.id) OVER () AS total_entries_count, a,b,c
FROM table
WHERE clauses
OFFSET x LIMIT y;
This calculates the total number of unpaginated rows correctly, without affecting the runtime of the query. At least as far as I can tell.
The questions I have are:
1) Are there any adverse effects that the above window function can have?
2) Are there any cases where the count would return incorrectly?
3) In general, is this an appropriate use-case for using window functions?
Thanks,
Clemens
