Re: Performance problems with prepared statements

On 2007-10-10, Theo Kramer <theo(at)flame(dot)co(dot)za> wrote:
> When doing a 'manual' prepare and explain analyze I get the following
>
> rascal=# prepare cq (char(12), smallint, integer) as SELECT oid,
> calllog_mainteng, calllog_phase, calllog_self FROM calllog
> WHERE calllog_mainteng = $1
> AND calllog_phase = $2
> AND calllog_self < $3
> OR calllog_mainteng = $1
> AND calllog_phase < $2
> ORDER BY calllog_mainteng DESC,
> calllog_phase DESC,
> calllog_self DESC limit 25;
> PREPARE

When you do this from the application, are you passing it 3 parameters,
or 5? The plan is clearly taking advantage of the fact that the two
occurrences of $1 and $2 are known to be the same value; if your app is
using some interface that uses ? placeholders rather than numbered
parameters, then the planner will not be able to make this assumption.

Also, from the application, is the LIMIT 25 passed as a constant or is that
also a parameter?

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services