BTW, there's another end to the 'enable_seqscan=false' problem... it
sometimes doesn't work! Last I looked, enable_seqscan=false only added a
fixed overhead cost to a seqscan (100 IIRC). The problem is, some
queries will produce estimates for other methodes that are more
expensive than a
Jaime Casanova [EMAIL PROTECTED] writes:
BTW, there's another end to the 'enable_seqscan=false' problem... it
sometimes doesn't work!
I have often considered that this is an indication that seq scan is
actually the better plan...
There are cases where it is the *only* plan, eg, you have no
On Thu, Dec 08, 2005 at 01:07:10PM -0500, Tom Lane wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
BTW, there's another end to the 'enable_seqscan=false' problem... it
sometimes doesn't work!
I have often considered that this is an indication that seq scan is
actually the better plan...
On Thu, Dec 01, 2005 at 12:32:12PM -0500, Qingqing Zhou wrote:
Neil Conway [EMAIL PROTECTED] wrote
This would also be useful when diagnosing bad query plans: for example,
setting enable_seqscan=false often causes the planner to disregard the
use of *any* sequential scan, anywhere in the
On Fri, 2005-12-02 at 15:49 -0500, Greg Stark wrote:
Rod Taylor [EMAIL PROTECTED] writes:
In the extreme, no amount of added intelligence in the optimizer is going
to
help it come up with any sane selectivity estimate for something like
WHERE radius_authenticate(user) = 'OK'
On Sun, 2005-12-04 at 12:49 -0300, Alvaro Herrera wrote:
Simon Riggs wrote:
ISTM we could do some of that with another GUC, lets call it
prepare_once = on. The system default is to have a prepared statement
bound to a plan on its first parameter bind. If we set this to off,
then the
On Sun, 2005-12-04 at 13:47 -0500, Pollard, Mike wrote:
Simon Riggs wrote
The system default is to have a prepared statement
bound to a plan on its first parameter bind.
We call it deferred optimization.
Do you really stop at the first parameter?
The first bind of parameters to the
On Mon, 2005-12-05 at 01:53 -0500, Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
There is no such thing as a plan
that is good for every case --- outlying data values can make a
usually-good plan blow out your performance guarantee anyway.
But outlying data is something the user
On Dec 5, 2005, at 4:17 AM, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
Plan stability is also an important feature, especially for OLTP
systems which have hard real-time requirements. OLTP systems
typically
don't care about getting the best plan for a query, only a plan
that
is
Hans-Juergen Schoenig [EMAIL PROTECTED] writes:
I think I know what Greg is trying to say: I think in this plan stability
does not mean that the plan has to be completely fixed - usually it is all
about indexing.
Usually problems occur because someone hasn't run analyze at all. That's not
[snip]
I want to be sure my existing queries keep using the plans they've been using
until I allow them to change.
I don't want to sit down and type select count(*) from users and have it not
work correctly (ie, use a sequential scan) because the system is so single
mindedly tuned for the
On Fri, 2005-12-02 at 11:07 +0100, Csaba Nagy wrote:
On Thu, 2005-12-01 at 22:01, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
On the other hand the type I would prefer to see are hints that feed
directly
into filling in information the planner lacks. This only requires that
Simon Riggs wrote:
ISTM we could do some of that with another GUC, lets call it
prepare_once = on. The system default is to have a prepared statement
bound to a plan on its first parameter bind. If we set this to off,
then the statement will replan each time we bind. This would give us
both
Simon Riggs wrote
ISTM we could do some of that with another GUC, lets call it
prepare_once = on. The system default is to have a prepared statement
bound to a plan on its first parameter bind. If we set this to off,
then the statement will replan each time we bind. This would give us
both
Simon Riggs [EMAIL PROTECTED] writes:
Csaba raises a good point here. Many people say they want hints when
what they actually require the plan to be both stable and predictable.
Plan stability is also an important feature, especially for OLTP systems which
have hard real-time requirements.
Greg Stark [EMAIL PROTECTED] writes:
Plan stability is also an important feature, especially for OLTP
systems which have hard real-time requirements. OLTP systems typically
don't care about getting the best plan for a query, only a plan that
is good enough.
Good enough means it can keep up
Tom Lane [EMAIL PROTECTED] writes:
Is it worth pointing out that using the same plan all the time is *no*
recipe for guaranteeing response time? There is no such thing as a plan
that is good for every case --- outlying data values can make a
usually-good plan blow out your performance
wouldn't it be more flexible to define a multiplicator or some sort of bool flag on a per object level?oracle hints are a total overkill and i agree with tom that usually people will abuse this feature.if we had a per object flag the actual planner hint can be decoupled from the actual query (i
On Thu, 2005-12-01 at 22:01, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
On the other hand the type I would prefer to see are hints that feed
directly
into filling in information the planner lacks. This only requires that the
user understand his own data and still lets the
On Fri, Dec 02, 2005 at 11:07:06AM +0100, Csaba Nagy wrote:
So for me the hint mechanism is good for telling the server that I'm
not interested at all in the BEST plan but which risks getting very bad
on occasions, but in a good enough plan which is safe.
I'm wondering if long term another
Greg Stark [EMAIL PROTECTED] writes:
You more or less missed my entire point.
Only because I am still getting used to how powerful and flexible
Postgres is; but I am working on expanding my horizons.
In the extreme, no amount of added intelligence in the optimizer is
going
to
help it come up
In the extreme, no amount of added intelligence in the optimizer is going to
help it come up with any sane selectivity estimate for something like
WHERE radius_authenticate(user) = 'OK'
Why not?
The missing capability in this case is to be able to provide or generate
(self learning?)
Rod Taylor [EMAIL PROTECTED] writes:
In the extreme, no amount of added intelligence in the optimizer is going to
help it come up with any sane selectivity estimate for something like
WHERE radius_authenticate(user) = 'OK'
Why not?
The missing capability in this case is to be
On 02 Dec 2005 15:49:02 -0500, Greg Stark [EMAIL PROTECTED] wrote:
Rod Taylor [EMAIL PROTECTED] writes:
The missing capability in this case is to be able to provide or generate
(self learning?) statistics for a function that describe a typical result
and the cost of getting that result.
On Fri, 2005-12-02 at 11:53 +0100, Martijn van Oosterhout wrote:
On Fri, Dec 02, 2005 at 11:07:06AM +0100, Csaba Nagy wrote:
So for me the hint mechanism is good for telling the server that I'm
not interested at all in the BEST plan but which risks getting very bad
on occasions, but in a
Is it possible to submit a hand written or arbitrary execution plan to the
retrieval engine? (That is, can one bypass the SQL parser and planner or
optimizer and just provide instructions to nested loop join table a to table
b ...)
---(end of
There are currently some rather crude knobs for persuading the planner
to favour certain kinds of query plans: the enable_XXX GUC variables.
Several people have asked for a more flexible way to give hints to the
planner. I'm not interested in implementing fully-general planner hints
at the moment,
Hey Neil,
In the last couple weeks I too have been thinking about planner
hints. Assuming I have read your post correctly, the issue I see
with this idea is that, in most cases, there won't be much of a
difference between adding an arbitrary cost value to each type of node
and disabling it
Neil Conway [EMAIL PROTECTED] writes:
... ISTM that a simple improvement to what we have now
would allow for a wider range of planner hints with only minor changes:
we could replace the enable_XXX variables with a set of variables that
would add an arbitrary constant to the estimated cost of
Neil Conway [EMAIL PROTECTED] wrote
This would also be useful when diagnosing bad query plans: for example,
setting enable_seqscan=false often causes the planner to disregard the
use of *any* sequential scan, anywhere in the plan. The ability to
slightly bump up the cost of particular
Jonah H. Harris [EMAIL PROTECTED] writes:
In the last couple weeks I too have been thinking about planner hints.
Assuming I have read your post correctly, the issue I see with this idea is
that, in most cases, there won't be much of a difference between adding an
arbitrary cost value to each
Tom,
Don't get me wrong, I agree with you completely. I would rather
put effort into enhancing the planner than in developing
work-arounds. In 99% of all cases the planner works correctly,
but I know people who actually have to disable planning options
(mergejoin) in production applications
Jonah H. Harris [EMAIL PROTECTED] writes:
Tom,
Don't get me wrong, I agree with you completely. I would rather put effort
into enhancing the planner than in developing work-arounds. In 99% of all
cases the planner works correctly, but I know people who actually have to
disable planning
Greg Stark [EMAIL PROTECTED] writes:
On the other hand the type I would prefer to see are hints that feed directly
into filling in information the planner lacks. This only requires that the
user understand his own data and still lets the planner pick the best plan
based on the provided
Greg Stark [EMAIL PROTECTED] writes:
On the other hand the type I would prefer to see are hints that feed
directly
into filling in information the planner lacks. This only requires that
the
user understand his own data and still lets the planner pick the best
plan
based on the provided
On 12/1/05, Pollard, Mike [EMAIL PROTECTED] wrote:
Optimizer hints were added because some databases just don't have a very
smart optimizer. But you are much better served tracking down cases in
which the optimizer makes a bad choice, and teaching the optimizer how
to make a better one. That
Gregory Maxwell [EMAIL PROTECTED] wrote:
The flipside there is that a good set of hinting options may increase
the amount of detailed feedback we get from users on improvements
needed in the optimizer. The current knobs are pretty blunt and don't
do as much as I'd like when trying to track
On Thu, 2005-12-01 at 21:01 -0500, Gregory Maxwell wrote:
If we'd really like to avoid people using the knobs to rig queries,
how about making them only work with explain analyze, useful for
debugging but not so useful for actual queries.
That seems a pretty arbitrary limitation. I agree that
Neil Conway [EMAIL PROTECTED] writes:
On Thu, 2005-12-01 at 21:01 -0500, Gregory Maxwell wrote:
If we'd really like to avoid people using the knobs to rig queries,
how about making them only work with explain analyze, useful for
debugging but not so useful for actual queries.
That seems a
On Thursday 2005-12-01 19:01, Gregory Maxwell wrote:
On 12/1/05, Pollard, Mike [EMAIL PROTECTED] wrote:
Optimizer hints were added because some databases just don't have a very
smart optimizer. But you are much better served tracking down cases in
which the optimizer makes a bad choice,
Pollard, Mike [EMAIL PROTECTED] writes:
Optimizer hints were added because some databases just don't have a very
smart optimizer. But you are much better served tracking down cases in
which the optimizer makes a bad choice, and teaching the optimizer how
to make a better one.
You more or
41 matches
Mail list logo
