Re: Prepared statements considered harmful

Peter Eisentraut 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'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.
>
Not to mention problems with outdated plans after schema changes. Using
views unplanned (replanned) when used in joins could lead to improved
resulting plans (e.g. if the view contains outer joins itself).

Regards,
Andreas