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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings