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 B7B5B329E45 for ; Thu, 23 Sep 2004 03:14:50 +0100 (BST) 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 11479-10 for ; Thu, 23 Sep 2004 02:14:39 +0000 (GMT) Received: from sue.samurai.com (sue.samurai.com [205.207.28.74]) by svr1.postgresql.org (Postfix) with ESMTP id 5DF6D329E43 for ; Thu, 23 Sep 2004 03:14:42 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by sue.samurai.com (Postfix) with ESMTP id 7252F3F17; Wed, 22 Sep 2004 22:14:43 -0400 (EDT) Received: from sue.samurai.com ([127.0.0.1]) by localhost (sue.samurai.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 49757-01-9; Wed, 22 Sep 2004 22:14:42 -0400 (EDT) Received: from localhost (unknown [61.88.101.19]) by sue.samurai.com (Postfix) with ESMTP id 85B9B3F00; Wed, 22 Sep 2004 22:14:40 -0400 (EDT) Subject: Re: Caching of Queries From: Neil Conway To: Tom Lane Cc: Scott Kirkwood , pgsql-performance@postgresql.org In-Reply-To: <18410.1095883147@sss.pgh.pa.us> References: <18410.1095883147@sss.pgh.pa.us> Content-Type: text/plain Message-Id: <1095905663.22485.294.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 23 Sep 2004 12:14:24 +1000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/229 X-Sequence-Number: 8357 On Thu, 2004-09-23 at 05:59, Tom Lane wrote: > I think this would allow the problems of cached plans to bite > applications that were previously not subject to them :-(. > An app that wants plan re-use can use PREPARE to identify the > queries that are going to be re-executed. I agree; if you want to do some work in this area, making improvements to PREPARE would IMHO be the best bet. For example, some people have talked about having PREPARE store queries in shared memory. Another idea would be to improve the quality of the plan we generate at PREPARE time: for instance you could generate 'n' plans for various combinations of input parameters, and then choose the best query plan at EXECUTE time. It's a difficult problem to solve, however (consider multiple parameters to PREPARE, for example). -Neil