pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Why we don't want hints
5+ messages / 4 participants
[nested] [flat]
* Re: Why we don't want hints
@ 2011-02-10 17:25 Chris Browne <cbbrowne@acm.org>
2011-02-13 20:40 ` Re: Why we don't want hints Rob Wultsch <wultsch@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Browne @ 2011-02-10 17:25 UTC (permalink / raw)
To: pgsql-performance
robertmhaas@gmail.com (Robert Haas) writes:
> On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Well, I'm comfortable digging in my heels against doing *lame* hints
>> just because "it's what all the other kids are doing," which I think
>> is the only thing which would have satisfied the OP on this thread.
>> From both on-list posts and ones exchanged off-list with me, it
>> seems he was stubbornly resistant to properly tuning the server to
>> see if any problems remained, or posting particular problems to see
>> how they would be most effectively handled in PostgreSQL. We
>> obviously can't be drawn into dumb approaches because of
>> ill-informed demands like that.
>
> Nor was I proposing any such thing. But that doesn't make "we don't
> want hints" an accurate statement. Despite the impression that OP
> went away with, the real situation is a lot more nuanced than that,
> and the statement on the Todo list gives the wrong impression, IMHO.
I have added the following comment to the ToDo:
We are not interested to implement hints in ways they are commonly
implemented on other databases, and proposals based on "because
they've got them" will not be welcomed. If you have an idea that
avoids the problems that have been observed with other hint systems,
that could lead to valuable discussion.
That seems to me to characterize the nuance.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/languages.html
If only women came with pull-down menus and online help.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why we don't want hints
2011-02-10 17:25 Re: Why we don't want hints Chris Browne <cbbrowne@acm.org>
@ 2011-02-13 20:40 ` Rob Wultsch <wultsch@gmail.com>
2011-02-13 22:29 ` Re: Why we don't want hints Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rob Wultsch @ 2011-02-13 20:40 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
On Thu, Feb 10, 2011 at 9:25 AM, Chris Browne <cbbrowne@acm.org> wrote:
> robertmhaas@gmail.com (Robert Haas) writes:
>> On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> Well, I'm comfortable digging in my heels against doing *lame* hints
>>> just because "it's what all the other kids are doing," which I think
>>> is the only thing which would have satisfied the OP on this thread.
>>> From both on-list posts and ones exchanged off-list with me, it
>>> seems he was stubbornly resistant to properly tuning the server to
>>> see if any problems remained, or posting particular problems to see
>>> how they would be most effectively handled in PostgreSQL. We
>>> obviously can't be drawn into dumb approaches because of
>>> ill-informed demands like that.
>>
>> Nor was I proposing any such thing. But that doesn't make "we don't
>> want hints" an accurate statement. Despite the impression that OP
>> went away with, the real situation is a lot more nuanced than that,
>> and the statement on the Todo list gives the wrong impression, IMHO.
>
> I have added the following comment to the ToDo:
>
> We are not interested to implement hints in ways they are commonly
> implemented on other databases, and proposals based on "because
> they've got them" will not be welcomed. If you have an idea that
> avoids the problems that have been observed with other hint systems,
> that could lead to valuable discussion.
>
> That seems to me to characterize the nuance.
Where exactly are the problems with other systems noted? Most other
systems have this option so saying "They have problems" is a giant cop
out.
--
Rob Wultsch
wultsch@gmail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why we don't want hints
2011-02-10 17:25 Re: Why we don't want hints Chris Browne <cbbrowne@acm.org>
2011-02-13 20:40 ` Re: Why we don't want hints Rob Wultsch <wultsch@gmail.com>
@ 2011-02-13 22:29 ` Josh Berkus <josh@agliodbs.com>
2011-02-13 22:52 ` Re: Why we don't want hints Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2011-02-13 22:29 UTC (permalink / raw)
To: pgsql-performance
I've wordsmithed Chris's changes some, and then spun off a completely
separate page for Hints discussion, since the NotToDo item was becoming
too long.
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
Please, NO!
This is exactly the kind of hint that I regard as a last resort if we
run out of implementation alternatives. Any hint which gets coded into
the actual queries becomes a *massive* maintenance and upgrade headache
thereafter. If we're implementing a hint alternative, we should look at
stuff in this priority order:
1. Useful tuning of additional cost parameters by GUC (i.e.
cursor_tuple_fraction)
2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500")
3. Adding new parameters to modify on database objects (i.e.
"distribution=normal(1.5,17)","new_rows=0.1")
4. Query hints (if all of the above fails to give fixes for some tested
problem)
> Where exactly are the problems with other systems noted? Most other
> systems have this option so saying "They have problems" is a giant cop
> out.
I've put my list down:
http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion#Problems_with_existing_Hint_stystems
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why we don't want hints
2011-02-10 17:25 Re: Why we don't want hints Chris Browne <cbbrowne@acm.org>
2011-02-13 20:40 ` Re: Why we don't want hints Rob Wultsch <wultsch@gmail.com>
2011-02-13 22:29 ` Re: Why we don't want hints Josh Berkus <josh@agliodbs.com>
@ 2011-02-13 22:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-02-14 07:01 ` Re: Why we don't want hints Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2011-02-13 22:52 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Sun, Feb 13, 2011 at 3:29 PM, Josh Berkus <josh@agliodbs.com> wrote:
> I've wordsmithed Chris's changes some, and then spun off a completely
> separate page for Hints discussion, since the NotToDo item was becoming
> too long.
>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>
> Please, NO!
>
> This is exactly the kind of hint that I regard as a last resort if we
> run out of implementation alternatives. Any hint which gets coded into
> the actual queries becomes a *massive* maintenance and upgrade headache
> thereafter. If we're implementing a hint alternative, we should look at
> stuff in this priority order:
>
> 1. Useful tuning of additional cost parameters by GUC (i.e.
> cursor_tuple_fraction)
> 2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500")
> 3. Adding new parameters to modify on database objects (i.e.
> "distribution=normal(1.5,17)","new_rows=0.1")
> 4. Query hints (if all of the above fails to give fixes for some tested
> problem)
I fail to see how 1 through 3 can tell the planner the correlation
between two fields in two separate tables.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why we don't want hints
2011-02-10 17:25 Re: Why we don't want hints Chris Browne <cbbrowne@acm.org>
2011-02-13 20:40 ` Re: Why we don't want hints Rob Wultsch <wultsch@gmail.com>
2011-02-13 22:29 ` Re: Why we don't want hints Josh Berkus <josh@agliodbs.com>
2011-02-13 22:52 ` Re: Why we don't want hints Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-02-14 07:01 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2011-02-14 07:01 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Sun, Feb 13, 2011 at 10:49 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> I fail to see how 1 through 3 can tell the planner the correlation
>> between two fields in two separate tables.
>
> CREATE CORRELATION_ESTIMATE ( table1.colA ) = ( table2.colB ) IS 0.3
>
> ... and then it fixes the correlation for *every* query in the database, not
> just that one. And is easy to fix if the correlation changes.
I like that. Even better, could we setup some kind of simple command
to tell analyze to collect stats for the two columns together?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-02-14 07:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-10 17:25 Re: Why we don't want hints Chris Browne <cbbrowne@acm.org>
2011-02-13 20:40 ` Rob Wultsch <wultsch@gmail.com>
2011-02-13 22:29 ` Josh Berkus <josh@agliodbs.com>
2011-02-13 22:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-02-14 07:01 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox