X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F3CAC3A47B7; Thu, 20 Jan 2005 13:45:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96539-08; Thu, 20 Jan 2005 13:45:34 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 06E373A4798; Thu, 20 Jan 2005 13:45:35 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id DE6B61E9B4; Thu, 20 Jan 2005 15:41:11 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06198-05; Thu, 20 Jan 2005 15:41:11 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id AC88B1DDF2; Thu, 20 Jan 2005 15:41:10 +0200 (EET) Message-ID: <020d01c4fef6$595bb9d0$0b00a8c0@forge> From: "Andrei Bintintan" To: "Richard Huxton" Cc: , References: <019f01c4fee1$1b58d600$0b00a8c0@forge> <41EF9FD3.5020400@archonet.com> Subject: Re: [SQL] OFFSET impact on Performance??? Date: Thu, 20 Jan 2005 15:45:47 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.022 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/266 X-Sequence-Number: 9956 > If you're using this to provide "pages" of results, could you use a > cursor? What do you mean by that? Cursor? Yes I'm using this to provide "pages", but If I jump to the last pages it goes very slow. Andy. ----- Original Message ----- From: "Richard Huxton" To: "Andrei Bintintan" Cc: ; Sent: Thursday, January 20, 2005 2:10 PM Subject: Re: [SQL] OFFSET impact on Performance??? > Andrei Bintintan wrote: >> Hi to all, >> >> I have the following 2 examples. Now, regarding on the offset if it >> is small(10) or big(>50000) what is the impact on the performance of >> the query?? I noticed that if I return more data's(columns) or if I >> make more joins then the query runs even slower if the OFFSET is >> bigger. How can I somehow improve the performance on this? > > There's really only one way to do an offset of 1000 and that's to fetch > 1000 rows and then some and discard the first 1000. > > If you're using this to provide "pages" of results, could you use a > cursor? > > -- > Richard Huxton > Archonet Ltd >