Re: [SQL] Good Optimization

wieck@debis.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