Prepared statements considered harmful

[Peter Eisentraut <peter_e(at)gmx(dot)net>]

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'd wish that we reconsider when and how prepared statements are used.  The 
JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the 
problem is really all over the place.

A couple of actions to consider:

- Never use prepared statements unless the user has turned them on.  (This is 
the opposite of the current behavior.)

- Transparently invalidate and regenerate prepared plans more often.  This 
could be tied to the transaction count, update activity obtained from the 
statistics collector, etc.

- Redefine "prepared" to mean "parsed" rather than "parsed and planned".

Each of these or similar changes would only solve a subset of the possible 
problems.  Possibly, we need more knobs to adjust these things.  But 
something needs to be done.

Comments?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/