Postgresql: Paginate and count in one query using window functions

When paginating records, we usually need to know the number of total records in order to render pagination links. Popular pagination libraries like will_paginate Archive or Kaminari Archive do this for us by simply issuing an extra query, like this:

SELECT post.* FROM posts LIMIT 20 OFFSET 100;

SELECT COUNT(*) FROM posts;   

This is fine most of the time. But rarely, you might have very complicated WHERE conditions or a subquery that takes time to run. In these cases, doing two queries is slower than it needs to be.

SELECT posts.* FROM (/* some complicated_subquery */) posts LIMIT 20 OFFSET 100;
SELECT COUNT(*) FROM (/* some complicated subquery */) posts;

Here the subquery had to run twice. (Although there is a chance PostgreSQL managed to optimize it for the COUNT query.)

Luckily, we can use PostgreSQL's window functions to speed things up. You can read more about window functions here Archive , but for our purposes, know that the following SQL does what we need:

SELECT posts.*, COUNT(*) OVER() AS full_count FROM (/* some complicated subquery */) posts LIMIT 20 OFFSET 100;

In Rails with will_paginate you might use it like this:

posts = scope_complicated_scope.select('posts.*, COUNT(*) OVER() AS full_count').paginate(page: page, total_entries: 0)
if (first_post = posts.first)
  posts.total_entries = first_post.full_count
end

There is one additional edge-case: When the OFFSET is so high that you get no results (i.e. a user managed to get to a pagination page that does not exist), the database will not return a record and so you will not get the count. To work around this, I use

scope = scope_complicated_scope.select('posts.*, COUNT(*) OVER() AS full_count')
posts = scope.paginate(page: page, total_entries: 0)
if (first_post = posts.first)
  posts.total_entries = first_post.full_count
elsif page && page > 1
  if (any_post = scope.limit(1).first)
    posts.total_entries = any_post.full_count
  end
end