Re: BUG #14411: Issue with using OFFSET

Hi Kevin,

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.

However, how would you implement jumping ahead to a specific range?
I.e. Picture a web site with pages 1 2 3 4 5 … 10 11 and the user jumping to page 11.

Thanks,
Jamie

On 11/3/16, 3:52 PM, "Kevin Grittner" <kgrittn(at)gmail(dot)com> wrote:

>On Thu, Nov 3, 2016 at 3:29 PM, <jkoceniak(at)mediamath(dot)com> wrote:
>
>> Query performance decreases as the OFFSET increases.
>
>Not a bug, but a fact of life. It must generate the OFFSET number
>of rows and continue to generated the next LIMIT rows (or continue
>processing until there are no rows). If matching rows are scarce
>after OFFSET rows are found, it can take a while to get enough or
>to find out that LIMIT rows don't exist.
>
>Personally, I never use OFFSET and LIMIT for pagination; there are
>better ways for most situations.
>
>--
>Kevin Grittner
>EDB: http://www.enterprisedb.com
>The Enterprise PostgreSQL Company