Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 40F56477147; Fri, 3 Jan 2003 17:16:47 -0500 (EST) Received: from clearmetrix.com (unknown [209.92.142.67]) by postgresql.org (Postfix) with ESMTP id 9D830477142; Fri, 3 Jan 2003 17:16:44 -0500 (EST) Received: from clearmetrix.com (chw.muvpn.clearmetrix.com [172.16.1.3]) by clearmetrix.com (8.11.6/linuxconf) with ESMTP id h03MGig26985; Fri, 3 Jan 2003 17:16:44 -0500 Message-ID: <3E160BC0.3020900@clearmetrix.com> Date: Fri, 03 Jan 2003 17:16:32 -0500 From: "Charles H. Woloszynski" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Hilmar Lapp Cc: Jeff , "pgsql-performance@postgresql.org" Subject: Re: [PERFORM] join over 12 tables takes 3 secs to plan References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/43 X-Sequence-Number: 5893 I have been asking (and learning) about this same thing on the PGSQL-JDBC mailing list. Apparently, there is a new driver for 7.3 that can store the plan on the server (aka, preparing it on the server) and re-use it. However, you need to set the PreparedStatement to do this for each statement. So, yes, you can retain the plan but it looks like you need to do some work to make it stick. [Also, you need to retain the PreparedStatement, it is not cached based based on the text of the statement, but associated with the PreparedStatement itself]. I think the functionality is starting to become real, but it looks like it is starting with some limitations that might restricts its use from be maximally realized until 7.4 (or beyond). Charlie Hilmar Lapp wrote: > > On Friday, January 3, 2003, at 09:12 AM, Jeff wrote: > >> Hmm.. This won't fix the fact the planner takes three seconds, but since >> it is a web application have you tried using PREPARE/EXECUTE so it only >> needs to be planned once? > > > Interesting point. I'd have to look into the source code whether the > guy who wrote it actually uses JDBC PreparedStatements. I understand > that PostgreSQL from 7.3 onwards supports prepared statements (cool!). > Would the JDBC driver accompanying the dist. exploit that feature for > its PreparedStatement implementation? > > -hilmar -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com