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

Re: generalizing the planner knobs

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: generalizing the planner knobs
Date: 2005-12-01 15:55:40
Message-ID: 36e682920512010755l44de5eb8t6e8151258c457ad0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hey Neil,

In the last couple weeks I too have been thinking about planner hints.
Assuming I have read your post correctly, the issue I see with this idea is
that, in most cases, there won't be much of a difference between adding an
arbitrary cost value to each type of node and disabling it completely.
Also, by fiddling with an arbitrary cost the user may introduce a lot of
variation into the planner which may actually result in worse query plans.

While Tom's done a great job with the planner, there are certain cases where
a user knows exactly what type of join or index they want to use for a
query.  In that case I'd favor run-time hints from the user similar to
Oracle.  I've read about seven papers on query optimization and planning in
the last few weeks and have a lot of ideas... I'm just not sure when I may
get time to work on them :(

-Jonah

On 12/1/05, Neil Conway <neilc(at)samurai(dot)com> wrote:
>
> There are currently some rather crude knobs for persuading the planner
> to favour certain kinds of query plans: the enable_XXX GUC variables.
> Several people have asked for a more flexible way to give hints to the
> planner. I'm not interested in implementing fully-general planner hints
> at the moment, but ISTM that a simple improvement to what we have now
> would allow for a wider range of planner hints with only minor changes:
> we could replace the enable_XXX variables with a set of variables that
> would add an arbitrary constant to the estimated cost of each type of
> query node. (Alternatively, an arbitrary multiplier could be specified;
> I'm not sure which would be better.)
>
> This would also be useful when diagnosing bad query plans: for example,
> setting enable_seqscan=false often causes the planner to disregard the
> use of *any* sequential scan, anywhere in the plan. The ability to
> slightly bump up the cost of particular operations would allow more
> alternative plans to be examined.
>
> On the other hand, the whole mechanism is still a hack. It also means
> that applications using this will be more dependent on the actual
> costing values produced by the planner, which is not good. However, if
> you're in the sort of desperate straights where this sort of hackery is
> required, perhaps that's acceptable.
>
> Comments?
>
> -Neil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-12-01 16:49:42
Subject: Re: generalizing the planner knobs
Previous:From: Tom LaneDate: 2005-12-01 15:21:16
Subject: Re: Another way to reduce pg_subtrans lookup overhead

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