On Fri, Aug 29, 2008 at 4:38 PM, Oliver Weichhold <oliver@xxxxxxxxxxxxx> 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
