X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C59169DC848 for ; Tue, 17 Jan 2006 17:13:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21310-03 for ; Tue, 17 Jan 2006 17:13:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.envyfinancial.com (unknown [206.248.142.186]) by postgresql.org (Postfix) with ESMTP id 031649DC841 for ; Tue, 17 Jan 2006 17:13:52 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.envyfinancial.com (Postfix) with ESMTP id D338C1CB35; Tue, 17 Jan 2006 16:12:59 -0500 (EST) Received: from mail.envyfinancial.com ([127.0.0.1]) by localhost (mark.mielke.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15196-07; Tue, 17 Jan 2006 16:12:59 -0500 (EST) Received: by mail.envyfinancial.com (Postfix, from userid 500) id B395D1CB4B; Tue, 17 Jan 2006 16:12:59 -0500 (EST) Date: Tue, 17 Jan 2006 16:12:59 -0500 From: mark@mark.mielke.cc To: Alessandro Baretta Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Suspending SELECTs Message-ID: <20060117211259.GA13926@mark.mielke.cc> References: <43CB71AC.20203@barettadeit.com> <9766.1137433907@sss.pgh.pa.us> <43CD4BD0.9090807@barettadeit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43CD4BD0.9090807@barettadeit.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: amavisd-new at mail.envyfinancial.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.64 required=5 tests=[AWL=0.090, NO_REAL_NAME=0.55] X-Spam-Score: 0.64 X-Spam-Level: X-Archive-Number: 200601/266 X-Sequence-Number: 16744 On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote: > I understand most of these issues, and expected this kind of reply. Please, > allow me to insist that we reason on this problem and try to find a > solution. My reason for doing so is that the future software industry is > likely to see more and more web applications retrieving data from virtually > endless databases, and in such contexts, it is sensible to ask the final > client--the web client--to store the "cursor state", because web > interaction is intrinsically asynchronous, and you cannot count on users > logging out when they're done, releasing resources allocated to them. Think > of Google. What is wrong with LIMIT and OFFSET? I assume your results are ordered in some manner. Especially with web users, who become bored if the page doesn't flicker in a way that appeals to them, how could one have any expectation that the cursor would ever be useful at all? As a 'general' solution, I think optimizing the case where the same query is executed multiple times, with only the LIMIT and OFFSET parameters changing, would be a better bang for the buck. I'm thinking along the lines of materialized views, for queries executed more than a dozen times in a short length of time... :-) In the mean time, I successfully use LIMIT and OFFSET without such an optimization, and things have been fine for me. Cheers, mark -- mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/