Re: Avoiding bad prepared-statement plans.

Robert Haas <robertmhaas@gmail.com> writes:
> I actually think there isn't any clean line.  Obscene is in the eye of
> the beholder.  Frankly, I think this discussion is getting off into
> the weeds.  It would be nice, perhaps, to have a feature that will
> detect when the generic plan is the suxxor and attempt to find a
> better one, but that's really, really hard for a whole bunch of
> reasons.  Bruce's suggestion that we should provide some user control
> over whether we plan at bind time or execute time seems likely to be
> (1) considerably simpler to implement, (2) considerably easier to get
> consensus on, and (3) capable of giving 90% of the benefit for an only
> higher inconvenience factor.

It's not going to be easier to implement.  Yeah, it would be easy to
provide a global switch via a GUC setting, but that's not going to be
helpful, because this is the sort of thing that really needs to be
managed per-query.  Almost any nontrivial application is going to have
some queries that really need the custom plan and many that don't.
If people just turn the GUC on we might as well throw away the plan
caching mechanism altogether.  But putting support for a per-query level
of control into the protocol (and then every client library) as well as
every PL is going to be painful to implement, and even more painful to
use.

I still like the idea of automatically replanning with the known
parameter values, and noting whether the result plan was estimated to be
noticeably cheaper than the generic plan, and giving up on generating
custom plans if we didn't observe any such win over N tries.
        regards, tom lane