Hi Scott, On Tue, Dec 30, 2008 at 12:09 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@xxxxxxxxx> 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). -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
