Skip site navigation (1) Skip section navigation (2)

Re: generalizing the planner knobs

From: Trent Shipley <tshipley(at)deru(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: generalizing the planner knobs
Date: 2005-12-02 07:06:28
Message-ID: 200512020006.28307.tshipley@deru.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thursday 2005-12-01 19:01, Gregory Maxwell wrote:
> On 12/1/05, Pollard, Mike <mpollard(at)cincom(dot)com> wrote:
> > Optimizer hints were added because some databases just don't have a very
> > smart optimizer.  But you are much better served tracking down cases in
> > which the optimizer makes a bad choice, and teaching the optimizer how
> > to make a better one.  That way, all users get the benefit of the fix.
> > Remember, the purpose of SQL is to isolate the end user from having to
> > care about how the data is retrieved; that is the RDBMS' problem.  (the
> > other thing forgotten was that it was supposed to be a natural language.
> > NVL.  Bah.)
>
> The flipside there is that a good set of hinting options  may increase
> the amount of detailed feedback we get from users on improvements
> needed in the optimizer.  The current knobs are pretty blunt and don't
> do as much as I'd like when trying to track down exactly where the
> optimiser has gone wrong.
>
> If we'd really like to avoid people using the knobs to rig queries,
> how about making them only  work with explain analyze, useful for
> debugging but not so useful for actual queries.

I'm all in favor of sticking to the declarative language ideal.

Also, I'm much in favor of protecting people from themselves.


On the other hand, if folks insist on engaging in extreme sports (like second 
guessing the optimizer) I'm against regulating their freedom.  I think 
exposing planner variables would be a good thing, on net.  Naturally, you 
would warn everyone not to touch them.  (Safety and freedom are both 
necessary.)

If you can play with the knobs, you should let them be used to return real 
result sets.  That way, when you get feedback, you will be able to tell if 
the cost estimator is "broken".  Just returning a modified plan won't 
challenge costing assumptions.

In response to

pgsql-hackers by date

Next:From: Greg StarkDate: 2005-12-02 07:09:49
Subject: Re: generalizing the planner knobs
Previous:From: Greg StarkDate: 2005-12-02 06:44:08
Subject: Re: Reducing relation locking overhead

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group