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 8E5E63A592B; Wed, 26 Jan 2005 10:11:52 +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 96726-02; Wed, 26 Jan 2005 10:11:46 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id F01E53A5B14; Wed, 26 Jan 2005 10:11:47 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 2029EE3D0; Wed, 26 Jan 2005 12:06:52 +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 31983-02; Wed, 26 Jan 2005 12:06:49 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 6050BBB49; Wed, 26 Jan 2005 12:06:49 +0200 (EET) Message-ID: <015a01c5038f$73a70720$0b00a8c0@forge> From: "Andrei Bintintan" To: , "Greg Stark" Cc: "Richard Huxton" , , References: <019f01c4fee1$1b58d600$0b00a8c0@forge><41EF9FD3.5020400@archonet.com> <020d01c4fef6$595bb9d0$0b00a8c0@forge><41EFCC5B.5010302@archonet.com><33c6269f05012008391490448b@mail.gmail.com> <87y8eh1hy9.fsf@stark.xeocode.com> Subject: Re: [SQL] OFFSET impact on Performance??? Date: Wed, 26 Jan 2005 12:11:49 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original 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.014 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/495 X-Sequence-Number: 10185 The problems still stays open. The thing is that I have about 20 - 30 clients that are using that SQL query where the offset and limit are involved. So, I cannot create a temp table, because that means that I'll have to make a temp table for each session... which is a very bad ideea. Cursors somehow the same. In my application the Where conditions can be very different for each user(session) apart. The only solution that I see in the moment is to work at the query, or to write a more complex where function to limit the results output. So no replace for Offset/Limit. Best regards, Andy. ----- Original Message ----- From: "Greg Stark" To: Cc: "Richard Huxton" ; "Andrei Bintintan" ; ; Sent: Tuesday, January 25, 2005 8:28 PM Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? > > Alex Turner writes: > >> I am also very interesting in this very question.. Is there any way to >> declare a persistant cursor that remains open between pg sessions? >> This would be better than a temp table because you would not have to >> do the initial select and insert into a fresh table and incur those IO >> costs, which are often very heavy, and the reason why one would want >> to use a cursor. > > TANSTAAFL. How would such a persistent cursor be implemented if not by > building a temporary table somewhere behind the scenes? > > There could be some advantage if the data were stored in a temporary table > marked as not having to be WAL logged. Instead it could be automatically > cleared on every database start. > > -- > greg > >