Re: generalizing the planner knobs

On Sun, 2005-12-04 at 12:49 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
>
> > ISTM we could do some of that with another GUC, lets call it
> > prepare_once = on. The system default is to have a prepared statement
> > bound to a plan on its first parameter bind. If we set this to "off",
> > then the statement will replan each time we bind. This would give us
> > both flexibility and predictability. (As ever, someone suggest a better
> > name?).
>
> Why would all statements behave the same?

They would be flexible and predictable, but not the same.

prepare_once = off
would reoptimize each statement, so each could have a potentially
different plan. Which, in the case I cited, is the only optimal
behaviour: sticking to any one plan, by any method, would be wrong.

The plans would be predictable because performance never exceeds the
worst case SeqScan; planning would be flexible because it will always
take the best plan.

> I think an important
> percentage of cases would require a fixed plan (thus planning at first
> sight is a good idea), while a limited number of cases would require
> planning every time the sentence is called.

Yes, that is exactly what I see. Hence a GUC with a default the same as
it is now: they would only be prepared once. You would only set the GUC
to another value when you have a statement that looks like it needs
"hinting" i.e. the plan flips from SeqScan to IndexScan and back
depending upon the input data.

> Your idea of qualifying it
> by table name does not make too much sense to me, because you can have
> both types of queries for each table, and further any query where this
> is necessary will involve more than one table anyway, so which one do
> you choose to make the decision?

That was a different idea later down my note, not a variation of the
same one: that had nothing to do with the prepare_once concept. Those
options were meant to be set on a per statement basis, not at the server
level.

I was trying to solve Neil's stated problem: How to force one part of a
query to avoid a SeqScan, yet without touching the others.

> So we would provide a protocol/libpq option to allow first-params-
> planning (the default and current behavior), and another to allow
> planning-every-time. The latter would tell the server to save only the
> parsetree of the query and replan each time it is invoked.

Or some function similar. I prefer the GUC because it does not imply a
protocol change.

Best Regards, Simon Riggs