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
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
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
"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
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
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
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
[ 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
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,
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
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 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 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
>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, 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
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
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
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
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
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
> 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 ver
> 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
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
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
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
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, 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 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 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 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
> 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
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.
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 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,
> > 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
> > 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
[ 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
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 formali
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
41 matches
Mail list logo
