Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5345464FC27 for ; Tue, 30 Dec 2008 11:24:46 -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 24245-01 for ; Tue, 30 Dec 2008 11:24:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp12.hushmail.com (smtp12.hushmail.com [65.39.178.135]) by mail.postgresql.org (Postfix) with ESMTP id 407C764FD7D for ; Tue, 30 Dec 2008 11:24:41 -0400 (AST) Received: from smtp12.hushmail.com (localhost.localdomain [127.0.0.1]) by smtp12.hushmail.com (Postfix) with SMTP id 5784C700E5 for ; Tue, 30 Dec 2008 15:24:38 +0000 (UTC) Received: from smtp.hushmail.com (mailserver7.hushmail.com [65.39.178.62]) by smtp12.hushmail.com (Postfix) with ESMTP; Tue, 30 Dec 2008 15:24:37 +0000 (UTC) Received: by smtp.hushmail.com (Postfix, from userid 99) id DE3D720040; Tue, 30 Dec 2008 15:24:36 +0000 (UTC) MIME-Version: 1.0 Date: Tue, 30 Dec 2008 07:24:36 -0800 To: pgsql-php@postgresql.org, toreason@fastmail.fm Subject: Re: force re-planning of prepared statements? From: pgdba@hush.com Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="UTF-8" Message-Id: <20081230152436.DE3D720040@smtp.hushmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/14 X-Sequence-Number: 3948 All my data has been fully vacuumed and analyzed, so that isn't the problem. The problem is specifically that the incorrect plan is being selected, and I think that that is due to the re-use of a sub- optimal plan. On Mon, 29 Dec 2008 16:47:10 -0800 V S P wrote: >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/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2 >S2RuRhpQuc9Grmy1V/ >> >> >> -- >> 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 -- Click for free info on getting an MBA, $200K/ year potential. http://tagline.hushmail.com/fc/PnY6qxsZwTcf7Oemn5WzFssWfYRzs4nJk5s2I9IYZS8jYesUJITCb/