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
"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
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
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
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 o
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 thos
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 f
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
> 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 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 plann
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, b
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 hint
> -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
>
&g
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 sele
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
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
"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
> > 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 p
> 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 fr
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 exactl
> 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
> 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
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 1
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,
[ 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
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: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, 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 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 b
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
ex
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
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
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
"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
fe
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
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
[ 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
45 matches
Mail list logo
