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 931213A1CF7 for ; Thu, 20 Jan 2005 21:35:24 +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 82945-03 for ; Thu, 20 Jan 2005 21:35:12 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.206]) by svr1.postgresql.org (Postfix) with ESMTP id EC3283A4811 for ; Thu, 20 Jan 2005 21:35:12 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so19307wra for ; Thu, 20 Jan 2005 13:35:12 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=qabnD1A9RiESpOTyYHcfYsTCZCHz/EKBEJ1kp4BVuKnl5v9b3QY4AKxJrf+S11rKpojcmsAsQLoK9BNK9sqa0rSuZ9a8g4k1lTLp8D5Rpou8nmmpcubuWxjCqZkSnTPnIqTJAPoGZQIUsfPf4PFrp6RYonPEXLt0d1MHDQXRj7I= Received: by 10.54.30.58 with SMTP id d58mr273131wrd; Thu, 20 Jan 2005 13:35:12 -0800 (PST) Received: by 10.54.18.72 with HTTP; Thu, 20 Jan 2005 13:35:12 -0800 (PST) Message-ID: <33c6269f05012013354ebf8bd8@mail.gmail.com> Date: Thu, 20 Jan 2005 16:35:12 -0500 From: Alex Turner Reply-To: alex@neteconomist.com To: Merlin Moncure Subject: Re: [SQL] OFFSET impact on Performance??? Cc: pgsql-performance@postgresql.org, Richard Huxton In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A75C6@Herge.rcsinc.local> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <6EE64EF3AB31D5448D0007DD34EEB3412A75C6@Herge.rcsinc.local> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/345 X-Sequence-Number: 10035 How do you create a temporary view that has only a small subset of the data from the DB init? (Links to docs are fine - I can read ;). My query isn't all that complex, and my number of records might be from 10 to 2k depending on how I implement it. Alex Turner NetEconomist On Thu, 20 Jan 2005 12:00:06 -0500, Merlin Moncure wrote: > > 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. > > Yes, it's called a 'view' :-) > > Everything you can do with cursors you can do with a view, including > selecting records in blocks in a reasonably efficient way. As long as > your # records fetched is not real small (> 10) and your query is not > super complex, you can slide your view just like a cursor with zero real > impact on performance. > > If the query in question does not scale in time complexity with the > amount of data returned (there is a fix processing step which can't be > avoided), then it's materialized view time, such that they can be done > in PostgreSQL. > > Now, cursors can be passed around in pl/pgsql functions which makes them > very useful in that context. However, for normal data processing via > queries, they have some limitations that makes them hard to use in a > general sense. > > Merlin > >