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
Jim C. Nasby wrote:
Ok, now that I've actually looked at the release notes, I take that back
and apologize. But while there's a lot of improvements that have been
made, there's still some seriously tough problems that have been talked
about for a long time and there's still no "light at the end
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
Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
If someone's going to commit to putting effort into improving the
planner then that's wonderful. But I can't recall any significant
planner improvements since min/max (which I'd argue was more of a bug
fix than an improvement).
Hmph.
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
Brendan Curran <[EMAIL PROTECTED]> writes:
> I'll tack up a note to the online documentation letting people know so
> that it's a little more explicitly clear that when you choose IN on
> data that isn't explicitly unique (to the planner i.e. post-analyze)
> you get the baggage of a forced unique
Well, IN != EXISTS != JOIN. Exists just stops as soon as it finds a
record. For some cases, it's equivalent to IN, but not all. IN has to
de-duplicate it's list in some fashion. For small IN lists, you can do
this with an OR, but at some point you need to switch to an actual
unique (actually, I
On Thu, 2006-10-12 at 09:44, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > If someone's going to commit to putting effort into improving the
> > planner then that's wonderful. But I can't recall any significant
> > planner improvements since min/max (which I'd argue was more of a
> 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,
On Thu, Oct 12, 2006 at 10:44:20AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > If someone's going to commit to putting effort into improving the
> > planner then that's wonderful. But I can't recall any significant
> > planner improvements since min/max (which I'd argue
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
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> If someone's going to commit to putting effort into improving the
> planner then that's wonderful. But I can't recall any significant
> planner improvements since min/max (which I'd argue was more of a bug
> fix than an improvement).
Hmph. Apparently I
On Thu, Oct 12, 2006 at 10:59:23PM +1300, Mark Kirkwood wrote:
> H.J. Sanders wrote:
>
> > why not just like in some other (commercial) databases:
> >
> > a statement to say: use index
> >
> > I know this is against all though but if even the big ones can not resist
> > the pressure
H.J. Sanders wrote:
why not just like in some other (commercial) databases:
a statement to say: use index
I know this is against all though but if even the big ones can not resist
the pressure of their users, why not?
Yeah - some could not (e.g. Oracle), but some did (e
Hello.
Simply jumping on the bandwagon, just my 2 cents:
why not just like in some other (commercial) databases:
a statement to say: use index
I know this is against all though but if even the big ones can not resist
the pressure of their users, why not?
Henk Sander
41 matches
Mail list logo
