Re: BUG #14411: Issue with using OFFSET

On Thu, Nov 3, 2016 at 7:05 PM, Jamie Koceniak <jkoceniak(at)mediamath(dot)com> wrote:
> On 11/3/16, 3:52 PM, "Kevin Grittner" <kgrittn(at)gmail(dot)com> wrote:

>> Personally, I never use OFFSET and LIMIT for pagination; there
>> are better ways for most situations.

> I would be interested in hearing about alternate solutions to
> using OFFSET.
> We have explored using btree index approach (ordering by id
> desc), storing last id and then grabbing next set of rows < last
> id. That works great for fetching next group of rows.

That works. If you also save the starting key value, you can use
it to page backward by reversing your ORDER BY.

> However, how would you implement jumping ahead to a specific
> range?

Range (as in database values) or page (as in count of matching rows)?

> I.e. Picture a web site with pages 1 2 3 4 5 … 10 11 and the user
> jumping to page 11.

You can't know what's on page 11 without reading pages 1 to 10.
You can either track that as you move forward, or just return all
the rows on the initial query and write everything to working
storage somewhere, navigating through this result when the user
chooses a new page. Normally when using this technique you set
some overall limit of rows for the query.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company