Re: [PERFORM] Hints proposal

От: Jim C. Nasby
Тема: Re: [PERFORM] Hints proposal
Дата: ,
Msg-id: 20061012192410.GU28647@nasby.net
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Hints proposal  (Josh Berkus)
Ответы: Re: [PERFORM] Hints proposal  (Josh Berkus)
Список: pgsql-hackers

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

Re: [PERFORM] Hints proposal  (Tom Lane, )
 Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
 Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
  Re: [PERFORM] Hints proposal  (Tom Lane, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
       Re: [PERFORM] Hints proposal  (Jeff Davis, )
        Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
         Re: [PERFORM] Hints proposal  (Jeff Davis, )
          Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
           Re: [PERFORM] Hints proposal  ("Joshua D. Drake", )
            Re: [PERFORM] Hints proposal  (Andrew Dunstan, )
           Re: [PERFORM] Hints proposal  (David Fetter, )
           Re: [PERFORM] Hints proposal  (Jeff Davis, )
           Re: [PERFORM] Hints proposal  (Stefan Kaltenbrunner, )
     Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
      Re: [PERFORM] Hints proposal  (Tom Lane, )
      Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
       Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
        Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
        Re: [PERFORM] Hints proposal  (Tom Lane, )
     Re: [PERFORM] Hints proposal  (Gregory Stark, )
      Re: [PERFORM] Hints proposal  (Robert Treat, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
  Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
     Re: [PERFORM] Hints proposal  (Csaba Nagy, )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
 Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Tom Lane, )

On Thu, Oct 12, 2006 at 09:40:30AM -0700, Josh Berkus wrote:
> Jim,
>
> >>>These hints would outright force the planner to do things a certain way.
> >>>... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */
> >>This proposal seems to deliberately ignore every point that has been
> >>made *against* doing things that way.  It doesn't separate the hints
> >>from the queries, it doesn't focus on fixing the statistical or cost
> >>misestimates that are at the heart of the issue, and it takes no account
> >>of the problem of hints being obsoleted by system improvements.
> >
> >Yes, but it does one key thing: allows DBAs to fix problems *NOW*. See
> >also my comment below.
>
> I don't see how adding extra tags to queries is easier to implement than
> an ability to modify the system catalogs.  Quite the opposite, really.
>
> And, as I said, if you're going to push for a feature that will be
> obsolesced in one version, then you're going to have a really rocky row
> to hoe.

Unless you've got a time machine or a team of coders in your back
pocket, I don't see how the planner will suddenly become perfect in
8.4...

> >Yes, but as I mentioned the idea here was to come up with something that
> >is (hopefully) easy to define and implement. In other words, something
> >that should be doable for 8.3. Because this proposal essentially amounts
> >to limiting plans the planner will consider and tweaking it's cost
> >estimates, I'm hoping that it should be (relatively) easy to implement.
>
> Even I, the chief marketing geek, am more concerned with getting a
> feature that we will still be proud of in 5 years than getting one in
> the next nine months.  Keep your pants on!

Hey, I wrote that email while dressed! :P

We've been seeing the same kinds of problems that are very difficult (or
impossible) to fix cropping up for literally years... it'd be really
good to at least be able to force the planner to do the sane thing even
if we don't have the manpower to fix it right now...

> I actually think the way to attack this issue is to discuss the kinds of
> errors the planner makes, and what tweaks we could do to correct them.
> Here's the ones I'm aware of:
>
> -- Incorrect selectivity of WHERE clause
> -- Incorrect selectivity of JOIN
> -- Wrong estimate of rows returned from SRF
> -- Incorrect cost estimate for index use
>
> Can you think of any others?

There's a range of correlations where the planner will incorrectly
choose a seqscan over an indexscan.

Function problems aren't limited to SRFs... we have 0 statistics ability
for functions.

There's the whole issue of multi-column statistics.

> I also feel that a tenet of the design of the "planner tweaks" system
> ought to be that the tweaks are collectible and analyzable in some form.
>  This would allow DBAs to mail in their tweaks to -performance or
> -hackers, and then allow us to continue improving the planner.

Well, one nice thing about the per-query method is you can post before
and after EXPLAIN ANALYZE along with the hints. But yes, as we move
towards a per-table/index/function solution, there should be an easy way
to see how those hints are affecting the system and to report that data
back to the community.
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


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

От: Tom Lane
Дата:
Сообщение: Re: Hints (Was: Index Tuning Features)
От: "Andrew Dunstan"
Дата:
Сообщение: Re: Hints (Was: Index Tuning Features)