Re: [SQL] OFFSET impact on Performance??? - Mailing list pgsql-performance

From Ragnar Hafstað
Subject Re: [SQL] OFFSET impact on Performance???
Date
Msg-id 1106248326.22416.22.camel@localhost.localdomain
Whole thread Raw
In response to Re: [SQL] OFFSET impact on Performance???  (Greg Stark <gsstark@mit.edu>)
Responses Re: [SQL] OFFSET impact on Performance???  (Ragnar Hafstað <gnari@simnet.is>)
List pgsql-performance
On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:

> The best way to do pages for is not to use offset or cursors but to use an
> index. This only works if you can enumerate all the sort orders the
> application might be using and can have an index on each of them.
>
> To do this the query would look something like:
>
> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
>
> Then you take note of the last value used on a given page and if the user
> selects "next" you pass that as the starting point for the next page.

this will only work unchanged if the index is unique. imagine , for
example if you have more than 50 rows with the same value of col.

one way to fix this is to use ORDER BY col,oid

gnari



pgsql-performance by date:

Previous
From: Mitch Pirtle
Date:
Subject: Re: PostgreSQL clustering VS MySQL clustering
Next
From: Ragnar Hafstað
Date:
Subject: Re: [SQL] OFFSET impact on Performance???