Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8997D2E01ED for ; Thu, 14 Feb 2008 15:49:28 -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 28335-08 for ; Thu, 14 Feb 2008 15:49:21 -0400 (AST) Received: from bay0-omc3-s38.bay0.hotmail.com (bay0-omc3-s38.bay0.hotmail.com [65.54.246.238]) by postgresql.org (Postfix) with ESMTP id 006FC2E01DD for ; Thu, 14 Feb 2008 15:49:23 -0400 (AST) Received: from BAY111-W37 ([64.4.17.137]) by bay0-omc3-s38.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Thu, 14 Feb 2008 11:49:22 -0800 Message-ID: X-Originating-IP: [76.230.35.86] From: Michael Lorenz To: Tom Lane CC: Subject: Re: Query slows after offset of 100K Date: Thu, 14 Feb 2008 19:49:22 +0000 Importance: Normal In-Reply-To: <17159.1203016095@sss.pgh.pa.us> References: <17159.1203016095@sss.pgh.pa.us> Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginalArrivalTime: 14 Feb 2008 19:49:22.0663 (UTC) FILETIME=[B219F770:01C86F42] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/166 X-Sequence-Number: 28708 Fair enough, and I did think of this as well. However, I didn't think this= was a viable option in my case, since we're currently allowing the user to= randomly access the pages (so $lastkey wouldn't really have any meaning). = The user can choose to sort on object ID, name or modification time, and t= hen go straight to any page in the list. With 750K records, that's around = 37K pages. Maybe a better way to phrase my question is: how can I paginate my data on= 3 different keys which allow random access to any given page, and still ge= t reasonable performance? Should I just force the user to limit their resu= lt set to some given number of records before allowing any paginated access= ? Or is it just not practical, period? Thanks, Michael Lorenz ---------------------------------------- > To: mlorenz1@hotmail.com > CC: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Query slows after offset of 100K=20 > Date: Thu, 14 Feb 2008 14:08:15 -0500 > From: tgl@sss.pgh.pa.us >=20 > Michael Lorenz writes: >> My query is as follows: >> SELECT o.objectid, o.objectname, o.isactive, o.modificationtime=20 >> FROM object o=20 >> WHERE ( o.deleted =3D false OR o.deleted IS NULL )=20 >> AND o.accountid =3D 111=20 >> ORDER BY 2=20 >> LIMIT 20 OFFSET 10000; >=20 > 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). >=20 > 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. >=20 > regards, tom lane >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly _________________________________________________________________ Your Future Starts Here. Dream it? Then be it! Find it at www.seek.com.au http://a.ninemsn.com.au/b.aspx?URL=3Dhttp%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau= %2F%3Ftracking%3Dsk%3Ahet%3Ask%3Anine%3A0%3Ahot%3Atext&_t=3D764565661&_r=3D= OCT07_endtext_Future&_m=3DEXT=