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 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 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
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 |
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
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
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,
22 matches
Mail list logo
