Re: Prepared statements considered harmful

On 8/31/06, Peter Eisentraut <peter_e@gmx.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. 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.
For example one of my favorite tricks is to paramterize the limit
clause in a query which creates a sliding window over the table for
progressive readahead.  Unfortunately the planner assumes 10% which
borks the plan. My work around is to turn off bitmap, seqscan before
plan and turn them on after the prepare.

The proposal to supply hints to statements and functions has been
voted down several times due to the argument that it is better to fix
the planner.  I think supplying hints does fix the planner, and is a
balanced solution.

merlin