Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9871664FD22 for ; Tue, 30 Dec 2008 19:11:52 -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 10318-09 for ; Tue, 30 Dec 2008 19:11:49 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.169]) by mail.postgresql.org (Postfix) with ESMTP id 5D76F64FD18 for ; Tue, 30 Dec 2008 19:11:49 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 25so5342104wfc.28 for ; Tue, 30 Dec 2008 15:11:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=IwYtzDIXcZ2idHYUz0StrcEohHYY65AnYCYTUy0gX8k=; b=jII3wBA5Ptd4O/o5QoGvixNMqIqNPpDJ86ul/QA74QGFLANDj/BgGiDq95wip0ai1A JLa6bSQ5mfA2NqLFxPZk7BpU1Tz367F2Jm8Orej2GF6J3RenYzUF28ho27inAJMRbpzu 5y/0GBdq31/NbxWYgK1LOLcQKFGtMGukW9VHg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=V5BhDVWij7k4uxHoK1o8hBKduxNswNTilJ7iBbnV9XU28iknXxhW+0w45J2ZBquOcJ 9lNID/ckSygCyGVkUDVuxYBLwV63fAT1xkapRd+MG2dD17MuUuVD6VWLwLe61lXAgeQ0 iFaqXDPqxr+X+xgoW8wK5OP6VgBizN4qq8P7A= Received: by 10.142.90.15 with SMTP id n15mr4624945wfb.115.1230678707684; Tue, 30 Dec 2008 15:11:47 -0800 (PST) Received: by 10.142.255.15 with HTTP; Tue, 30 Dec 2008 15:11:47 -0800 (PST) Message-ID: <33b743250812301511x30f1b377uf55fdca82d243c37@mail.gmail.com> Date: Tue, 30 Dec 2008 15:11:47 -0800 From: bricklen To: "Tom Lane" Subject: Re: Poor plan choice in prepared statement Cc: "pgsql-performance@postgresql.org" In-Reply-To: <2796.1230678149@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com> <33b743250812301214k7a0ec4fby614d693ec7915d3@mail.gmail.com> <2796.1230678149@sss.pgh.pa.us> 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/238 X-Sequence-Number: 32274 Hi Tom, On Tue, Dec 30, 2008 at 3:02 PM, Tom Lane wrote: > The point of a prepared statement IMHO is to do the planning only once. > There's necessarily a tradeoff between that and having a plan that's > perfectly adapted to specific parameter values. I agree, and normally it wouldn't be an issue. In this particular case, we are seeing response time to go from sub-second with non-prepared queries, to over 200 seconds w/ prepared queries. Note that is not an isolated case in our application, merely the numbers from this particular example. > > Reasonable client-side APIs should provide the option to use out-of-line > parameters, which is what you want to prevent SQL injection, without > hard-wiring that to the orthogonal concept of statements whose plan is > prepared in advance. In libpq, for instance, PQexecParams() does that. > > regards, tom lane > Again, I agree completely. What I am after I guess are some pointers on where to look for that, with regards to PHP. Whatever I turn up, I will turn over to our developers, but before I do that I want to be sure I am giving them the correct advice. Thanks, Bricklen