Re: [HACKERS] Hints proposal - Mailing list pgsql-performance

From Josh Berkus
Subject Re: [HACKERS] Hints proposal
Date
Msg-id 452E6FFE.7010504@agliodbs.com
Whole thread Raw
In response to Re: [HACKERS] Hints proposal  ("Jim C. Nasby" <jim@nasby.net>)
Responses Re: [HACKERS] Hints proposal
Re: Hints proposal
Re: [HACKERS] Hints proposal
List pgsql-performance
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.

> 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!

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?

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.

--Josh Berkus





pgsql-performance by date:

Previous
From: "Bucky Jordan"
Date:
Subject: Re: Hints proposal
Next
From: Jeff Davis
Date:
Subject: Re: Hints proposal