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
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
-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
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, mostly
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
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),
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
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
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 had
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 and
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
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
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
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
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
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
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
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
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
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 one
[ 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
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
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
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 |
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, and
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 fixing the
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
made
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
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
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
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 it
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
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 much
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 we can
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] (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 and a
52 matches
Mail list logo
