Re: Poor OFFSET performance in PostgreSQL 9.1.6

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: fburgess(at)radiantblue(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor OFFSET performance in PostgreSQL 9.1.6
Date: 2013-08-29 11:00:22
Message-ID: 20130829110022.GA27417@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Aug 28, 2013 at 01:39:46PM -0700, fburgess(at)radiantblue(dot)com wrote:
> Can anyone offer suggestions on how I can optimize a query that contains the LIMIT OFFSET clause?
> The explain plan of the query is included in the notepad attachment.
> thanks

large offsets are slow, and there is no real escape from it.

You can change your paging solution, though, to something that will be
faster.

Possible solutions/optimizations:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/

or

http://www.depesz.com/2011/05/20/pagination-with-fixed-order/

Best regards,

depesz

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message bsreejithin 2013-08-29 12:14:03 How clustering for scale out works in PostgreSQL
Previous Message David Rowley 2013-08-29 04:43:50 Re: Poor OFFSET performance in PostgreSQL 9.1.6