Re: Indexing problem with OFFSET LIMIT

On Fri, Aug 29, 2008 at 4:38 PM, Oliver Weichhold <oliver(at)weichhold(dot)com> wrote:
> Hello
>
> I have problem in my applications and don't know how to fix it.
>
> This is the table and one of the indexes:
>
> CREATE TABLE foo
> (
> id serial NOT NULL,
> foo_name character varying(100),
> realm_id integer
>
> ... and about 50 other columns
> )
>
> CREATE INDEX idx_foo_name_realm
> ON foo
> USING btree
> (realm_id, foo_name);
>
> Table foo contains about 8 Million Rows.
>
>
> The problem:
>
> Consider this query:
>
> SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET
> 15000

try this:
SELECT * FROM foo WHERE realm_id = 228 order by realm_id, foo_name
LIMIT 200 OFFSET
15000

Or even better don't use 'offset' at all. It's simply lousy. If
you want to skip ahead 200 rows at a time, save off the previous last
extracted rows in the app:
1st time:
select * from foo order by realm_id, foo_name limit 200;
times after that:
select * from foo where (realm_id, foo_name) > (last_realm_id,
last_foo_name) order by realm_id, foo_name limit 200;

you should be pleasantly surprised :-). This is also a little bit
more graceful if other sessions are deleting/inserting rows while you
are browsing.

merlin