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=jJIT1WVB1pwuefpfkGxIvSQ6kelmg1PbVIL1Qku50kCNj088xRAyUAtXGDK1/dIgVE
	w1rkEg2QGN1NZg+L/dq7EwltEnnZ7Uy5rmvWJvvKurdU/OK0YFcWVZqecks+IAHZfQ7E
	K4FreYgmbiG/5tiLguW5dNuHUBJ37E4wXpy6Y=
Message-ID: <33b743250812301214k7a0ec4fby614d693ec7915d3@mail.gmail.com>
Date: Tue, 30 Dec 2008 12:14:34 -0800
From: bricklen <bricklen@gmail.com>
To: "Scott Marlowe" <scott.marlowe@gmail.com>
Subject: Re: Poor plan choice in prepared statement
Cc: "Merlin Moncure" <mmoncure@gmail.com>, pgsql-performance@postgresql.org
In-Reply-To: <dcc563d10812301209i59bf612eidc42fa408d4441ab@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com>
	<b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>
	<dcc563d10812301209i59bf612eidc42fa408d4441ab@mail.gmail.com>

Hi Scott,

On Tue, Dec 30, 2008 at 12:09 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
>>> Hi, I am re-posting my question here after trying to find a solution
>>> in the PHP pgsql list with no luck.
>>>
>>> I am experiencing some performance issues that I think are stemming
>>> from prepared statements. I have a pretty simple query:
>>> -- bad plan, from prepared statement
>>> --
>>> dev=# prepare fooplan (date,date,int,int) as
>>> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
>>> cl.idCreative AS creative, cl.subid, cl.datetime
>>> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
>>> = co.clickGenerated
>>> dev-# WHERE cl."date" >= $1
>>> dev-# AND cl."date" <= $2
>>> dev-# AND cl.idAffiliate = $3
>>> dev-# LIMIT $4;
>>
>> Your problem is that the query as written is hard to plan.  The
>> database has no idea what you pass in, it has to guess.  (IMO, It
>> almost always guesses wrong...I think it should assume 1 row
>> returned).  Also, the db has no idea what you want to pass in at plan
>> time for date.
>
> One of the things you can try here is to build your query then execute
> it so it has to be planned each time.
>

Yeah, I've tested that in the application itself and it worked
correctly. I am trying to discover a way to use bind variables in PHP
without using the prepare function (to block sql injection), or if I
must use the prepare function, then force it to replan each time
somehow. That's part of where I'm stuck (and I'm no php guy).