От: Jim C. Nasby
Тема: Re: Hints proposal
Дата: ,
Msg-id: 20061012165326.GI28647@nasby.net
(см: обсуждение, исходный текст)
Ответ на: 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, )

On Thu, Oct 12, 2006 at 04:55:17PM +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> >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.
>
> In all fairness, when I used to work with DB2 we often had to rewrite
> queries to persuade the planner to choose a different plan. Often it was
> more of an issue of plan stability; a query would suddenly become
> horribly slow in production because a table had grown slowly to the
> point that it chose a different plan than before. Then we had to modify
> the query again, or manually set the statistics. In extreme cases we had
> to split a query to multiple parts and use temporary tables and move
> logic to the application to get a query to perform consistently and fast
> enough. I really really missed hints.

Oracle has an interesting way to deal with this, in that you can store a
plan that the optimizer generates and tell it to always use it for that
query. There's some other management tools built on top of that. I don't
know how commonly it's used, though...

Also, on the DB2 argument... I'm wondering what happens when people end
up with a query that they can't get to execute the way it should? Is the
planner *that* good that it never happens? Do you have to wait for a
fixpack when it does happen? I'm all for having a super-smart planner,
but I'm highly doubtful it will always know exactly what to do.

> That said, I really don't like the idea of hints like "use index X"
> embedded in a query. I do like the idea of hints that give the planner
> more information about the data. I don't have a concrete proposal, but

Which is part of the problem... there's nothing to indicate we'll have
support for these improved hints anytime soon, especially if a number of
them depend on plan invalidation.

> here's some examples of hints I'd like to see:
>
> "table X sometimes has millions of records and sometimes it's empty"
> "Expression (table.foo = table2.bar * 2) has selectivity 0.99"
> "if foo.bar = 5 then foo.field2 IS NULL"
> "Column X is unique"
> "function foobar() always returns either 1 or 2, and it returns 2 90% of
> the time."
> "if it's Monday, then table NEW_ORDERS has a cardinality of 100000,
> otherwise 10."
>
> BTW: Do we make use of CHECK constraints in the planner? In DB2, that
> was one nice and clean way of hinting the planner about things. If I
> remember correctly, you could even define CHECK constraints that weren't
> actually checked at run-time, but were used by the planner.

I think you're right... and it is an elegant way to hint the planner.
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


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

От: Jeff Davis
Дата:
Сообщение: Re: Hints proposal
От: Christopher Browne
Дата:
Сообщение: Re: Hints proposal