Date: Thu, 12 Oct 2006 11:46:07 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Joshua Marsh <icub3d@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Message-ID: <20061012164607.GH28647@nasby.net>
References: <20061012151439.GT28647@nasby.net>
	<38242de90610120826l5328759bt41468644e54badae@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <38242de90610120826l5328759bt41468644e54badae@mail.gmail.com>
User-Agent: Mutt/1.5.11

On Thu, Oct 12, 2006 at 09:26:24AM -0600, Joshua Marsh wrote:
> On 10/12/06, Jim C. Nasby <jim@nasby.net> wrote:
> >
> >Posting here instead of hackers since this is where the thread got
> >started...
> >
> >The argument has been made that producing a hints system will be as hard
> >as actually fixing the optimizer. There's also been clamoring for an
> >actual proposal, so here's one that (I hope) wouldn't be very difficult
> >to implemen.
> >
> >My goal with this is to keep the coding aspect as simple as possible, so
> >that implementation and maintenance of this isn't a big burden. Towards
> >that end, these hints either tell the planner specifically how to handle
> >some aspect of a query, or they tell it to modify specific cost
> >estimates. My hope is that this information could be added to the
> >internal representation of a query without much pain, and that the
> >planner can then use that information when generating plans.
> 
> 
> I've been following the last thread with a bit of interest.  I like the
> proposal.  It seems simple and easy to use.  What is it about hinting that
> makes it so easily breakable with new versions?  I don't have any experience
> with Oracle, so I'm not sure how they screwed logic like this up.  Hinting
> to use a specific merge or scan seems fairly straight forward; if the query
> requests to use an index on a join, I don't see how hard it is to go with
> the suggestion.  It will become painfully obvious to the developer if his
> hinting is broken.

The problem is that when you 'hint' (which is actually not a great name
for the first part of my proposal, since it's really forcing the planner
to do something), you're tying the planner's hands. As the planner
improves in newer versions, it's very possible to end up with forced
query plans that are much less optimal than what the newer planner could
come up with. This is especially true as new query execution nodes are
created, such as hashaggregate.

The other downside is that it's per-query. It would certainly be useful
to be able to nudge the planner in the right direction on a per-table
level, but it's just not clear how to accomplish that. Like I said, the
idea behind my proposal is to have something that can be done soon, like
for 8.3.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)