pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: jwieck@debis.com
Cc: kudo@partitur.se
Cc: maillist@candle.pha.pa.us
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Good Optimization
Date: Thu, 08 Jul 1999 11:01:17 -0400
Message-ID: <29053.931446077@sss.pgh.pa.us> (raw)
In-Reply-To: <m112A6U-0003ktC@orion.SAPserv.Hamburg.dsh.de>

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



view thread (27+ messages)  latest in thread

Message-ID: <29053.931446077@sss.pgh.pa.us>
Permalink:  ../29053.931446077@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29053.931446077@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jwieck@debis.com, kudo@partitur.se, maillist@candle.pha.pa.us
  Subject: Re: [SQL] Good Optimization
  In-Reply-To: <29053.931446077@sss.pgh.pa.us>
  References: <m112A6U-0003ktC@orion.SAPserv.Hamburg.dsh.de> <29053.931446077@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox