-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bucky
Jordan
Sent: Thursday, October 12, 2006 2:19 PM
To: josh@agliodbs.com; Jim C. Nasby
Cc: pgsql-hackers@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [HACKERS] [PERFORM] Hints proposal
Josh Berkus writes:
>> > 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
> 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 f
On Tuesday 17 October 2006 22:55, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > When it happens it tends to look something like this:
> > http://archives.postgresql.org/pgsql-performance/2006-01/msg00154.php
> >
> > Funny that for all the people who claim that improving the planner
Robert Treat <[EMAIL PROTECTED]> writes:
> When it happens it tends to look something like this:
> http://archives.postgresql.org/pgsql-performance/2006-01/msg00154.php
> Funny that for all the people who claim that improving the planner should be
> the primary goal that no one ever took interest
On Friday 13 October 2006 12:46, Gregory Stark wrote:
> Josh Berkus writes:
> >> > 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
On Thursday 12 October 2006 12:40, Bucky Jordan wrote:
> >What is it about hinting that makes it so easily breakable with new
> > versions? I >don't have any experience with Oracle, so I'm not sure how
> > they screwed logic like >this up.
>
> I don't have a ton of experience with oracle either,
Csaba Nagy wrote:
2d) Hints will damage the ongoing development of the optimizer by
reducing or eliminating test cases for its improvement.
You have no evidence for this.
My evidence (which I think I've mentioned in a couple of previous
postings), is the experience with the optimizer of tha
On Monday 16 October 2006 10:36, Brian Hurt wrote:
> ... Therefor, any hints feature *will* be used widely
> and in "inappropriate" circumstances. Protestations that
> this wasn't what the feature was meant for will fall on
> deaf ears.
I don't really care about this topic, as I've used Oracle
Brian Hurt wrote:
> Or, in shorter forms:
> 1) If you make it convient to use, expect it to be used a lot. If it
> shouldn't be used a lot, don't make it convient.
> 2) Breaking features means that people won't upgrade.
> 3) Programmers are idiots- design accordingly.
The PostgreSQL project has
I haven't weighed in on this because 1) I'm not a postgresql developer,
and am firmly of the opinion that they who are doing the work get to
decide how the work gets done (especially when you aren't paying them
for the work), and 2) I don't have any experience as a developer with
hints, and thu
> 2d) Hints will damage the ongoing development of the optimizer by
> reducing or eliminating test cases for its improvement.
You have no evidence for this. The mindset of the postgres community you
cite further below usually mandates that you say things if you have
evidence for them... and this
Craig A. James wrote:
2. Hints
a) On a aesthetic/theoretical level, hints suck. They're ugly and rude
b) On a practical level, introducing hints will cause short- and
long-term problems
c) Hints would help DBAs solve urgent problems for which there is no
other solution
Pretty good su
On Sun, Oct 15, 2006 at 05:25:31PM -0700, Craig A. James wrote:
> So my question is: Is there any argument that can be made to persuade those
> of you who are volunteering your time on the optimizer to even consider a
> HINTS proposal? Has all this discussion changed your perspective on 2(c),
>
So let's cut to the bone: If someone thinks a proposal is a bad idea, and
they're volunteering their time on an open-source project, why would they
implement the proposal?
In all the heat and smoke, I believe there are two basic conclusions we all
agree on.
1. Optimizer:
a) A perfect optimi
Josh Berkus wrote:
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 r
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Let me clarify, because that's not what I meant. Right now, there's not
> even a shadow of a design for anything else, and this is a tough nut to
> crack.
I think you are not exactly measuring on a level playing field. On the
textually-embedded-hints s
Jim C. Nasby wrote:
> So, does anyone out there have a plan for how we could give user's the
> ability to control the planner at a per-table level in 8.3 or even 8.4?
Per-table level? Some of the problems that have been put forward have
to do with table combinations (for example selectivity of j
On Fri, Oct 13, 2006 at 03:57:23PM -0700, Josh Berkus wrote:
> Jim,
>
> > Well, that's not what I said (my point being that until the planner and
> > stats are perfect you need a way to over-ride them)... but I've also
> > never said hints would be faster or easier than stats modification (I
> > s
Andreas,
> I think we need to more precisely define the problems of our system with
> point in time statistics
>
> -- no reaction to degree of other concurrent activity
> -- no way to react to abnormal skew that only persists for a very short
> duration
> -- too late reaction to changing distribut
Jim,
> Well, that's not what I said (my point being that until the planner and
> stats are perfect you need a way to over-ride them)... but I've also
> never said hints would be faster or easier than stats modification (I
> said I hope they would).
Yes, you did. Repeatedly. On this and other th
> I completely agree that it's much better *in the long run* to improve
> the planner and the statistics system so that we don't need hints. But
> there's been no plan put forward for how to do that, which means we
also
> have no idea when some of these problems will be resolved. If someone
> comes
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I completely agree that it's much better *in the long run* to improve
> the planner and the statistics system so that we don't need hints. But
> there's been no plan put forward for how to do that, which means we also
> have no idea when some of these pr
On Thu, Oct 12, 2006 at 01:58:22PM -0700, Josh Berkus wrote:
> > 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...
>
> Since you're not a core code contributor, I really don't see why you
> continue
Quoth [EMAIL PROTECTED] (Richard Broersma Jr):
>> By the way, wouldn't it be possible if the planner learned from a query
>> execution, so it would know if a choice for a specific plan or estimate
>> was actually correct or not for future reference? Or is that in the line
>> of DB2's complexity
On Thu, 2006-10-12 at 14:34 -0500, Jim C. Nasby wrote:
> On Thu, Oct 12, 2006 at 09:42:55AM -0700, Jeff Davis wrote:
> > On Thu, 2006-10-12 at 10:14 -0500, Jim C. Nasby wrote:
> > > The syntax these hints is something arbitrary. I'm borrowing Oracle's
> > > idea of embedding hints in comments, but
On Thu, 2006-10-12 at 17:28 -0400, Tom Lane wrote:
> [ trying once again to push this thread over to -hackers where it belongs ]
>
> Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> > On 12-10-2006 21:07 Jeff Davis wrote:
> >> On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
> >> To formali
Bucky Jordan wrote:
> What about using regular expressions, plus, if you have a function
> (views, or any other statement that is stored), you can assign a rule to
> that particular function. So you get matching, plus explicit selection.
> This way it's easy to find all your hints, turn them off,
> By the way, wouldn't it be possible if the planner learned from a query
> execution, so it would know if a choice for a specific plan or estimate
> was actually correct or not for future reference? Or is that in the line
> of DB2's complexity and a very hard problem and/or would it add too muc
[ trying once again to push this thread over to -hackers where it belongs ]
Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> On 12-10-2006 21:07 Jeff Davis wrote:
>> On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
>> To formalize the proposal a litte, you could have syntax like:
>> CREATE
> > Well, one nice thing about the per-query method is you can post
before
> > and after EXPLAIN ANALYZE along with the hints.
>
> One bad thing is that application designers will tend to use the hint,
fix
> the immediate issue, and never report a problem at all. And query
hints
> would not be co
On 12-10-2006 21:07 Jeff Davis wrote:
On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
To formalize the proposal a litte, you could have syntax like:
CREATE HINT [FOR USER username] MATCHES regex APPLY HINT some_hint;
Where "some_hint" would be a hinting language perhaps like Jim's, except
Jim,
> > 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 rea
On Thu, Oct 12, 2006 at 09:42:55AM -0700, Jeff Davis wrote:
> On Thu, 2006-10-12 at 10:14 -0500, Jim C. Nasby wrote:
> > The syntax these hints is something arbitrary. I'm borrowing Oracle's
> > idea of embedding hints in comments, but we can use some other method if
> > desired. Right now I'm more
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
>
On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
> > I'm not suggesting that we do that, but it seems better then embedding
> > the hints in the queries themselves.
>
> OK, what about this: if I execute the same query from a web client, I
> want the not-so-optimal-but-safe plan, if I execute i
On Thu, Oct 12, 2006 at 02:21:55PM -0400, Merlin Moncure wrote:
> third way: to solve the problem of data (especially constants) not
> being available to the planner at the time the plan was generated.
> this happens most often with prepared statements and sql udfs. note
> that changes to the plan
> I'm not suggesting that we do that, but it seems better then embedding
> the hints in the queries themselves.
OK, what about this: if I execute the same query from a web client, I
want the not-so-optimal-but-safe plan, if I execute it asynchronously, I
let the planner choose the
best-overall-per
Csaba Nagy <[EMAIL PROTECTED]> writes:
> Until that point is achieved, the above proposal is one of the simplest
> to understand for the tweaking DBA, and the fastest to deploy when faced
> with catastrophic plans. And I would guess it is one of the simplest to
> be implemented and probably not ver
> Hmmm, if you already understand Visual Basic syntax, should we support
> that too? Or maybe we should support MySQL's use of '-00-00' as the
> "zero" date because people "understand" that?
You completely misunderstood me... I have no idea about oracle hints,
never used Oracle in fact. My
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
On Thu, Oct 12, 2006 at 09:26:24AM -0600, Joshua Marsh wrote:
> On 10/12/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >Posting here instead of hackers since this is where the thread got
> >started...
> >
> >The argument has been made that producing a hints system will be as hard
> >as actually
Csaba,
I guess the angle is: I, as a practicing DBA would like to be able to
experiment and get most out of the imperfect tool I have, and you, the
developers, want to make the tool perfect... I don't care about perfect
tools, it just have to do the job... hints or anything else, if I can
make i
On Thu, 2006-10-12 at 10:14 -0500, Jim C. Nasby wrote:
> The syntax these hints is something arbitrary. I'm borrowing Oracle's
> idea of embedding hints in comments, but we can use some other method if
> desired. Right now I'm more concerned with getting the general idea
> across.
>
Is there any
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
>What is it about hinting that makes it so easily breakable with new versions?
>I >don't have any experience with Oracle, so I'm not sure how they screwed
>logic like >this up.
I don't have a ton of experience with oracle either, mostly DB2, MSSQL and PG.
So, I thought I'd do some googling,
OK, I just have to comment...
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > 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
On Thu, Oct 12, 2006 at 11:42:32AM -0400, Tom Lane wrote:
> [ This is off-topic for -performance, please continue the thread in
> -hackers ]
>
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > These hints would outright force the planner to do things a certain way.
> > ... FROM table /* ACCESS {SEQ
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> BTW: Do we make use of CHECK constraints in the planner?
Only for "constraint exclusion", and at the moment that's off by default.
The gating problem here is that if the planner relies on a CHECK
constraint, and then you drop the constraint, the pr
On 10/12/06, Tom Lane <[EMAIL PROTECTED]> wrote:
[ This is off-topic for -performance, please continue the thread in
-hackers ]
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
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,
[ This is off-topic for -performance, please continue the thread in
-hackers ]
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> 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 deli
On 10/12/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
Posting here instead of hackers since this is where the thread gotstarted...The argument has been made that producing a hints system will be as hard
as actually fixing the optimizer. There's also been clamoring for anactual proposal, so here's on
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.
I perfer attacking the problem at the t
Posting here instead of hackers since this is where the thread got
started...
The argument has been made that producing a hints system will be as hard
as actually fixing the optimizer. There's also been clamoring for an
actual proposal, so here's one that (I hope) wouldn't be very difficult
to imp
55 matches
Mail list logo
