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.

well this sounds like being best done with the "central plan cache" idea
that is floating around(I think neilc once worked on that) - once we
have something like that I would expect we can easily
invalidate/regenerate plans there based on certain criteria (from
obvious things like DDL-changes to more subtile ones like maybe "age of
the plan" or "statistics changed significantly on table foo" or
"regenerate plan everytime when the table bla is involved")
Most of that is pure speculation - but something like that would be a
very powerful thing to have.

Stefan