On Thu, 2006-08-31 at 14:32, Peter Eisentraut wrote: > Am Donnerstag, 31. August 2006 14:11 schrieb Csaba Nagy: > > How about "prepared" means really "prepared"... in the sense of parsed, > > analyzed all sensible plans, and save a meta-plan which based on current > > statistics and parameter values chooses one of the considered (and > > cached) plans ? > > I don't think this could solve one particularly frequent problem which is > that > pattern matching queries don't get along with prepared plans if the search > pattern isn't known at planning time.
Why not ? I specifically said you would prepare a few sensible plans based on statistics/expected variations of the statistics, and parameter value ranges which would trigger different plans. 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. The meta-plan I mentioned would be a collection of plans with rules to choose the right one at run time based on parameter values and perhaps the current statistics. This of course would need a lot more preparation time than just prepare one plan, but that's why you want to do it upfront and then cache the results. A central plan repository mentioned in other posts would fit nicely here... and you could use prepared plans for non-parameterized queries too by simply considering the constants as parameters, to increase the chances for a prepared plan reuse - this of course for complex enough queries. Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
