Re: Prepared statements considered harmful

Peter Eisentraut wrote:
> Am Donnerstag, 31. August 2006 14:52 schrieb Csaba Nagy:
>> So for the like query case you could save 2 plans, one for the indexable
>> case, one for the not indexable case. Then at runtime you choose the
>> proper one based on the pattern value.
>
> OK, why don't you work out an example. Let's look at this query:
>
> SELECT * FROM t1 WHERE a LIKE $1;
>
> What two plans would you prepare?

Well I guess for the case that none of the "expected" plans fit you can
always fallback to generating a new plan on the fly.

Anyways it would of course be cool if pgsql could set an invalid flag if
it detects that a certain plan performed badly (maybe even automatically
cause a fresh table analysis) or some DDL/DML was executed that likely
invalidated the plan.

I am not sure if there is any "philosphie" that pgsql tries to adhere
to. Does it want to leave the job of tuning to the DBA or does it want
to do things automatically (which always means that in some situations
it will do the wrong thing).

tweak planner vs. planner hints
manually analyze vs. automatically analyze
manual vaccum vs autovaccum

Hmm actually its probably not a black and white thing and the ultimate
goal would be to offer both with maybe some installer checkbox to
default everything to "DBA-less" automode.

Anyways I never liked the idea of planner hints. I think it makes much
more sense to give people direct access to plans in that case. Meaning
they can "partially" hardcode (parameterized) plans they want. I have
mentioned before that Sybase seems to have such a feature (you can dump
plans, tweak them and remove pieces that should be done on the fly and
associate them with stored procedures - not sure if you also do that for
prepared statements).

regards,
Lukas