Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id F3E4164FD2F for ; Mon, 29 Dec 2008 20:55:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79235-04 for ; Mon, 29 Dec 2008 20:55:52 -0400 (AST) X-Greylist: delayed 00:08:40.630937 by SQLgrey-1.7.6 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by mail.postgresql.org (Postfix) with ESMTP id 83DE264FD1F for ; Mon, 29 Dec 2008 20:55:52 -0400 (AST) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id 516421FCE4B; Mon, 29 Dec 2008 19:47:10 -0500 (EST) Received: from web5.messagingengine.com ([10.202.2.214]) by compute2.internal (MEProxy); Mon, 29 Dec 2008 19:47:10 -0500 Received: by web5.messagingengine.com (Postfix, from userid 99) id 2EF3A98C28; Mon, 29 Dec 2008 19:47:10 -0500 (EST) Message-Id: <1230598030.11642.1292231721@webmail.messagingengine.com> X-Sasl-Enc: qsdkZ6/lLjAhGtbRCk9GrTemiFZLUfiC/uXNoJUpDuup 1230598030 From: "V S P" To: pgdba@hush.com, pgsql-php@postgresql.org Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="KOI8-R" MIME-Version: 1.0 X-Mailer: MessagingEngine.com Webmail Interface References: <20081229221705.4464B20040@smtp.hushmail.com> Subject: Re: force re-planning of prepared statements? In-Reply-To: <20081229221705.4464B20040@smtp.hushmail.com> Date: Mon, 29 Dec 2008 19:47:10 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200812/12 X-Sequence-Number: 3946 Hi, I do not have an answer for you but, it is my understanding that a) PHP drops the DB connection for every HTTP request and then creates a new one (unless a proxy is used) That means that prepare statement has a perfromance benefit if the same SQL is used more than once per session b) if prepare by itself takes long, than may be analyzing tables/updating statistics/vaccuming at least the tables involved in the query might help c) if b) does not help -- personally I would think that the problem is somewhere outside the 'prepare' call (unless there is a PG bug in that functionality on that version of the server) On Mon, 29 Dec 2008 14:17:05 -0800, pgdba@hush.com said: > Hi all, I am experiencing some performance issues that I think are > stemming from the PDO prepared statements functions. > > I have a pretty simple query that runs: > > - sub-second when issued from the command line (not prepared) > > - takes 200+ seconds when run from the command line inside a > prepared statement (eg. > http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html) > > - takes over 200s when run from our application, within the pdo > prepared functions > > - runs sub-second from our application if I prepend the query with > "explain analyze" and looking at the resulting plan, it shows the > same plan as when it runs quickly from the command line. > > postgresql 8.2.11, php 5.2.1 > > What are my options here? I would like to continue to use bind > variables to prevent sql injection, but I'd like to force a plan re- > parse for every single query. > > Any ideas? > > > -- > Click to become a massage therapist and work for yourself. > http://tagline.hushmail.com/fc/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2S2RuRhpQuc9Grmy1V/ > > > -- > Sent via pgsql-php mailing list (pgsql-php@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-php -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - Access all of your messages and folders wherever you are