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/
