Re: [SQL] Good Optimization

wieck(at)debis(dot)com (Jan Wieck) writes:
> IMHO we're improving optimization more and more on the cost
> of query parse/rewrite/optimize/plan time. Thus performance
> of statements that EXECUTE fast slows down more and more.
> Isn't it time to think about some (maybe shared)
> "parsetree->plan" cache that provides ready to use plans if
> only Const values have changed?

Easier said than done, because the plan chosen by the optimizer may
well depend on the values of the constants. For example, in

SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t1.b < 12;

the optimizer will attempt to estimate the fraction of t1's rows that
will pass the restriction "t1.b < 12", and then it will choose the
type of join depending on how many rows it thinks there will be.
If we require plans to be chosen without dependence on the values of
constants, we will have to give up a great deal of optimization.

I do not object to letting the user specifically say PREPARE xyz...
and then using that prepared plan; there are plenty of times when
trading off planning time against getting a narrowly-tailored plan
is a useful thing to do. But we mustn't pre-empt the user's choice.
Note also that in a PREPARE context, it is known which items are
substitutable parameters and which are plain constants, so some
amount of optimization can still go on.

regards, tom lane