Using window functions to get the unpaginated count for paginated queries

From: Clemens Park <clemens(dot)park(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using window functions to get the unpaginated count for paginated queries
Date: 2012-11-13 16:51:50
Message-ID: CAH2gdAEMJ7hhtjVSEfckZjnEfT+022zPfs9rsM7R9zXYBrfBgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2012-11-13 18:31:24 Re: Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Previous Message Philippe Amelant 2012-11-13 16:19:31 Re: Understanding streaming replication