Re: Prepared statements considered harmful

Merlin Moncure wrote:
> On 8/31/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> With time, it becomes ever clearer to me that prepared SQL statements
>> are just
>> a really bad idea. On some days, it seems like half the performance
>> problems
>> in PostgreSQL-using systems are because a bad plan was cached
>> somewhere. I'd
>> say, in the majority of cases the time you save parsing and planning is
>> irrelevant compared to the possibly disastrous effects of wrong or
>> suboptimal
>> plans. I wonder if other people have similar experiences.
>
> I have to respectfully disagree. I have used them to great effect in
> many of my projects.
Peter doesn't propose to remove prepared statements as such. They are
certainly of great value, if used carefully and specifically, as in your
case. The problems he's addressing stem from plans _implicitly_ created
and stored.
> In the most extreme case, prepared statements can
> provide a 50% reduction or greater in overall query time...this is too
> good a benefit to simply discard. I worked on converted isam projects
> which would not have been possbile to make efficient without prepared
> statements. However you are correct that the planner does often
> create wacky plans which can cause disasterous results in some cases.
>
> My major issue is that you cannot supply hints to the query engine.
I don't believe extending this thread to the we-need-hints issue is a
good idea.

Regards,
Andreas