От: Bruce Momjian
Тема: Re: Hints proposal
Дата: ,
Msg-id: 200610121519.k9CFJA416544@momjian.us
(см: обсуждение, исходный текст)
Ответ на: Hints proposal  ("Jim C. Nasby")
Ответы: Re: Hints proposal  (Heikki Linnakangas)
Список: pgsql-performance

Скрыть дерево обсуждения

Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  (Bruce Momjian, )
  Re: Hints proposal  (Heikki Linnakangas, )
   Re: Hints proposal  (Tom Lane, )
   Re: Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  ("Joshua Marsh", )
  Re: Hints proposal  ("Bucky Jordan", )
   Re: Hints proposal  (Robert Treat, )
    Re: Hints proposal  (Tom Lane, )
     Re: Hints proposal  (Robert Treat, )
  Re: Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  (Tom Lane, )
  Re: Hints proposal  ("Merlin Moncure", )
  Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
   Re: [HACKERS] Hints proposal  (Josh Berkus, )
    Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
     Re: [HACKERS] Hints proposal  (Josh Berkus, )
      Re: [HACKERS] Hints proposal  ("Bucky Jordan", )
       Re: [HACKERS] Hints proposal  (Alvaro Herrera, )
      Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
       Re: [HACKERS] Hints proposal  (Tom Lane, )
       Re: [HACKERS] Hints proposal  ("Bucky Jordan", )
        Re: Hints proposal  ("Craig A. James", )
         Re: Hints proposal  (, )
         Re: Hints proposal  (Mark Kirkwood, )
          Re: Hints proposal  (Csaba Nagy, )
           Re: Hints proposal  (Brian Hurt, )
            Re: Hints proposal  (Bruce Momjian, )
            Re: Hints proposal  (Shaun Thomas, )
           Re: Hints proposal  (Mark Kirkwood, )
       Re: [HACKERS] Hints proposal  (Josh Berkus, )
        Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
         Re: [HACKERS] Hints proposal  (Alvaro Herrera, )
         Re: [HACKERS] Hints proposal  (Tom Lane, )
      Re: [HACKERS] Hints proposal  (Gregory Stark, )
    Re: Hints proposal  ("Craig A. James", )
    Re: [HACKERS] Hints proposal  ("Zeugswetter Andreas ADI SD", )
  Re: Hints proposal  (Csaba Nagy, )
   Re: [HACKERS] Hints proposal  (Josh Berkus, )
    Re: [HACKERS] Hints proposal  (Csaba Nagy, )
   Re: [HACKERS] Hints proposal  (Tom Lane, )
 Re: Hints proposal  (Jeff Davis, )
  Re: Hints proposal  (Csaba Nagy, )
   Re: Hints proposal  (Jeff Davis, )
    Re: Hints proposal  (Arjen van der Meijden, )
     Re: Hints proposal  (Tom Lane, )
      Re: Hints proposal  (Jeff Davis, )
     Re: Hints proposal  (Richard Broersma Jr, )
     Re: Hints proposal  (Christopher Browne, )
  Re: Hints proposal  ("Jim C. Nasby", )
   Re: Hints proposal  (Jeff Davis, )
 Re: [HACKERS] Hints proposal  (Andrew Sullivan, )
 Re: [HACKERS] Hints proposal  (Robert Treat, )

Because DB2 doesn't like hints, and the fact that they have gotten to a
point where they feel they do not need them, I feel we too can get to a
point where we don't need them either.  The question is whether we can
get there quickly enough for our userbase.

I perfer attacking the problem at the table definition level, like
something like "volatile", or adding to the existing table statistics.

---------------------------------------------------------------------------

Jim C. Nasby 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.
>
> The syntax these hints is something arbitrary. I'm borrowing Oracle's
> idea of embedding hints in comments, but we can use some other method if
> desired. Right now I'm more concerned with getting the general idea
> across.
>
> Since this is such a controversial topic, I've left this at a 'rough
> draft' stage - it's meant more as a framework for discussion than a
> final proposal for implementation.
>
> Forcing a Plan
> --------------
> These hints would outright force the planner to do things a certain way.
>
> ... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */
>
> This would force the planner to access table via a seqscan or
> index_name. For the index case, you can also specify if the access must
> or must not be via a bitmap scan. If neither is specified, the planner
> is free to choose either one.
>
> Theoretically, we could also allow "ACCESS INDEX" without an index name,
> which would simply enforce that a seqscan not be used, but I'm not sure
> how useful that would be.
>
> ... FROM a JOIN b /* {HASH|NESTED LOOP|MERGE} JOIN */ ON (...)
> ... FROM a JOIN b ON (...) /* [HASH|NESTED LOOP|MERGE] JOIN */
>
> Force the specified join mechanism on the join. The first form would not
> enforce a join order, it would only force table b to be joined to the
> rest of the relations using the specified join type. The second form
> would specify that a joins to b in that order, and optionally specify
> what type of join to use.
>
> ... GROUP BY ... /* {HASH|SORT} AGGREGATE */
>
> Specify how aggregation should be handled.
>
> Cost Tweaking
> -------------
> It would also be useful to allow tweaking of planner cost estimates.
> This would take the general form of
>
> node operator value
>
> where node would be a planner node/hint (ie: ACCESS INDEX), operator
> would be +, -, *, /, and value would be the amount to change the
> estimate by. So "ACCESS INDEX my_index / 2" would tell the planner to
> cut the estimated cost of any index scan on a given table in half.
>
> (I realize the syntax will probably need to change to avoid pain in the
> grammar code.)
>
> Unlike the hints above that are ment to force a certain behavior on an
> operation, you could potentially have multiple cost hints in a single
> location, ie:
>
> FROM a /* HASH JOIN * 1.1 NESTED LOOP JOIN * 2 MERGE JOIN + 5000 */
>     JOIN b ON (...) /* NESTED LOOP JOIN - 5000 */
>
> The first comment block would apply to any joins against a, while the
> second one would apply only to joins between a and b. The effects would
> be cumulative, so this example means that any merge join against a gets
> an added cost of 5000, unless it's a join with b (because +5000 + -5000
> = 0). I think you could end up with odd cases if the second form just
> over-rode the first, which is why it should be cummulative.
> --
> Jim Nasby                                            
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
  Bruce Momjian   
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +


В списке pgsql-performance по дате сообщения:

От: Arjen van der Meijden
Дата:
Сообщение: Re: Hints proposal
От: "Bucky Jordan"
Дата:
Сообщение: Re: [HACKERS] Hints proposal