Skip site navigation (1) Skip section navigation (2)

Re: Indexing problem with OFFSET LIMIT

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Oliver Weichhold" <oliver(at)weichhold(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Indexing problem with OFFSET LIMIT
Date: 2008-08-30 02:11:12
Message-ID: b42b73150808291911j3aad986fu151a7c516ab5afb7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Bill ToddDate: 2008-08-30 02:46:28
Subject: SELECT INTO returns incorrect values
Previous:From: David RowleyDate: 2008-08-30 01:14:15
Subject: Re: Indexing problem with OFFSET LIMIT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group