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 b
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 ha
>
> 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 t
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, an
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_authent
[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 fo
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
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
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 somethin
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 parameter
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",
> > t
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
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 c
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 requirement
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
> b
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
>
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 re
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 don
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 broadcast)-
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
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 r
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 cas
> 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?)
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
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
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
"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
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 ch
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
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 th
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 tr
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.
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 in
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 in
"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 p
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 beca
"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
"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 particula
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
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 comple
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,
41 matches
Mail list logo