From: Jim C. Nasby <jim@nasby.net>
To: Joshua Marsh <icub3d@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Date: Thu, 12 Oct 2006 11:46:07 -0500
Message-ID: <20061012164607.GH28647@nasby.net> (raw)
In-Reply-To: <38242de90610120826l5328759bt41468644e54badae@mail.gmail.com>
References: <20061012151439.GT28647@nasby.net>
<38242de90610120826l5328759bt41468644e54badae@mail.gmail.com>
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)
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, icub3d@gmail.com
Subject: Re: Hints proposal
In-Reply-To: <20061012164607.GH28647@nasby.net>
* 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