Re: [PERFORM] Hints proposal

Поиск
Список
Период
Сортировка
От Jim C. Nasby
Тема Re: [PERFORM] Hints proposal
Дата
Msg-id 20061012192410.GU28647@nasby.net
обсуждение исходный текст
Ответ на Re: [PERFORM] Hints proposal  (Josh Berkus <josh@agliodbs.com>)
Ответы Re: [PERFORM] Hints proposal  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-hackers
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                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Neil Conway
Дата:
Сообщение: Re: [PATCHES] Documentation fix for --with-ldap
Следующее
От: David Fetter
Дата:
Сообщение: Re: create temp table .. on commit delete rows