Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 546642E01D0 for ; Thu, 14 Feb 2008 15:08:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24377-03 for ; Thu, 14 Feb 2008 15:08:14 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 2663E2E01F6 for ; Thu, 14 Feb 2008 15:08:17 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1EJ8Fx8017160; Thu, 14 Feb 2008 14:08:15 -0500 (EST) To: Michael Lorenz cc: pgsql-performance@postgresql.org Subject: Re: Query slows after offset of 100K In-reply-to: References: Comments: In-reply-to Michael Lorenz message dated "Thu, 14 Feb 2008 18:28:13 +0000" Date: Thu, 14 Feb 2008 14:08:15 -0500 Message-ID: <17159.1203016095@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/165 X-Sequence-Number: 28707 Michael Lorenz writes: > My query is as follows: > SELECT o.objectid, o.objectname, o.isactive, o.modificationtime > FROM object o > WHERE ( o.deleted = false OR o.deleted IS NULL ) > AND o.accountid = 111 > ORDER BY 2 > LIMIT 20 OFFSET 10000; This is guaranteed to lose --- huge OFFSET values are never a good idea (hint: the database still has to fetch those rows it's skipping over). A saner way to do pagination is to remember the last key you displayed and do something like "WHERE key > $lastkey ORDER BY key LIMIT 20", which will allow the database to go directly to the desired rows, as long as you have an index on the key. You do need a unique ordering key for this to work, though. regards, tom lane