On Friday 13 October 2006 12:46, Gregory Stark wrote:
Josh Berkus josh@agliodbs.com 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
The _other_ way it's going to be used is as a stealthy
alteration to regular behaviour, to solve a particular nasty
performance problem that happens to result on a given day.
And every single time I've seen anything like that done, the
long term effect is always monstrous.
Funny, I
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
On Fri, Oct 13, 2006 at 10:41:36AM +0200, Zeugswetter Andreas ADI SD wrote:
Can you give us an example that had such a monstrous effect in Oracle,
other than that the hint was a mistake in the first place ?
Of course the hint was a mistake in the first place; the little story
I told was exactly
And remember, the places where PostgreSQL is getting used most
heavily are still the sort of environments where people will take a
lot of short cuts to achieve an immediate result, and be annoyed when
that short cut later turns out to have been expensive. Postgres will
get a black eye from
Can you give us an example that had such a monstrous effect in
Oracle,
other than that the hint was a mistake in the first place ?
Of course the hint was a mistake in the first place; the
little story I told was exactly an example of such a case.
The hint shouldn't have been put in
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 to
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 problems
On Thu, 2006-10-12 at 23:12 -0400, Christopher Browne wrote:
No, I don't have any idea, except that it would be less push-back
than changing a language that's embedded in client code. Also, I see
no reason to think that a hint would not be obsolete upon a new
release anyway.
I see
Josh Berkus josh@agliodbs.com 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
On Thu, 2006-10-12 at 18:02 -0400, Alvaro Herrera wrote:
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.
-Original Message-
From: Jeff Davis [mailto:[EMAIL PROTECTED]
Sent: Friday, October 13, 2006 1:00 PM
To: Alvaro Herrera
Cc: Bucky Jordan; josh@agliodbs.com; Jim C. Nasby; pgsql-
[EMAIL PROTECTED]
Subject: Re: [HACKERS] [PERFORM] Hints proposal
On Thu, 2006-10-12 at 18:02 -0400
On Fri, 2006-10-13 at 13:08 -0400, Bucky Jordan wrote:
It sounds horrible to me too, and I'm the one that thought of it (or
at
least I'm the one that introduced it to this thread).
However, everything is relative. Since the other idea floating around
is
to put the same hinting
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate cost
corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait for those?
It's great that the planner is continually improving, but
Jim C. Nasby wrote:
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate cost
corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait for those?
It's great that the planner is
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 up
On Fri, Oct 13, 2006 at 12:30:24PM -0500, Jim C. Nasby wrote:
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate
cost corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait
On Fri, 2006-10-13 at 12:30 -0500, Jim C. Nasby wrote:
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate cost
corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait for
Joshua D. Drake wrote:
Jim C. Nasby wrote:
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate cost
corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait for
Jim C. Nasby wrote:
On Fri, Oct 13, 2006 at 10:23:31AM -0700, Jeff Davis wrote:
Right. And I think the sane ideas are along the lines of estimate cost
corrections (like Tom is saying).
Let me ask this... how long do you (and others) want to wait for those?
well - we waited and got other
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
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 distribution
Csaba,
And upgrades are always causing breakage, I didn't have one upgrade
without some things to fix, so I would expect people is expecting that.
And that's true for Oracle too, our oracle guys always have something to
fix after an upgrade. And I repeat, I always had something to fix for
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
said I
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
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 side, I
[ 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
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
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 {SEQSCAN |
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
made
Jim C. Nasby [EMAIL PROTECTED] writes:
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.
Sorry, but that is not anywhere on my list of criteria for an important
feature.
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
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
On Thu, Oct 12, 2006 at 11:25:25AM -0500, Jim C. Nasby wrote:
Yes, but it does one key thing: allows DBAs to fix problems *NOW*. See
also my comment below.
If I may argue in the other direction, speaking as one whose career
(if we may be generous enough to call it that) has been pretty much
On 10/12/06, Andrew Sullivan [EMAIL PROTECTED] wrote:
On Thu, Oct 12, 2006 at 11:25:25AM -0500, Jim C. Nasby wrote:
Yes, but it does one key thing: allows DBAs to fix problems *NOW*. See
also my comment below.
If I may argue in the other direction, speaking as one whose career
(if we may be
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
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 very
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
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
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 really rocky
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
[ 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 HINT
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,
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 formalize the
Quoth [EMAIL PROTECTED] (Jeff Davis):
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,
45 matches
Mail list logo
