Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA58942 for ; Thu, 8 Jul 1999 11:03:35 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA29055; Thu, 8 Jul 1999 11:01:17 -0400 (EDT) To: jwieck@debis.com (Jan Wieck) cc: kudo@partitur.se (Patrik Kudo), maillist@candle.pha.pa.us, pgsql-sql@postgreSQL.org Subject: Re: [SQL] Good Optimization In-reply-to: Your message of Thu, 8 Jul 1999 11:04:26 +0200 (MET DST) Date: Thu, 08 Jul 1999 11:01:17 -0400 Message-ID: <29053.931446077@sss.pgh.pa.us> From: Tom Lane 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