Thread: Coster/planner and edge cases...

OK I know it's been beaten nearly to death, but no clear action has come 
of it quite yet.  We all seem to agree that there is some non-optimal 
way in which the planner handles edge cases (cost wise).  I don't 
believe that there are any fundamental type faults in any of the logic 
because we'd have much more major problems.  Instead I'd like to 
investigate these edge cases where the planner chooses sub-optimal cases 
and see if there is anythign that can be done about it.  

No clue if I can cause any help or not yet, just something I'm going ot 
be looking into.  The reason I'm writing though is I need data samples 
and queries that evoke the non-optimal responses (IE choosing the wrong 
plan) in order to look into it.  

Also I'd also like to know if there is a way to get the planner to burp 
out all the possible plans it considered before selecting a final plan 
or do I need to do a little surgery to get that done?


TIA guys!

Michael Loftis

BTW I'm not masochistic, I'm just out of work and BORED :)

Michael Loftis <mloftis@wgops.com> writes:
> Also I'd also like to know if there is a way to get the planner to burp 
> out all the possible plans it considered before selecting a final plan 
> or do I need to do a little surgery to get that done?

You can define OPTIMIZER_DEBUG but the interface leaves a lot to be
desired (output to backend stdout, no way to turn it on or off except
recompile...)  Also, I believe all you will see are the paths that
survived the initial pruning done by add_path.  This is about the
right level of detail for examining join choices, but perhaps not very
helpful for why-didn't-it-use-my-index choices; the paths you wanted
to know about may not have got into the relation's candidate-path list
in the first place.
        regards, tom lane

Tom Lane wrote:

>Michael Loftis <mloftis@wgops.com> writes:
>
>>Also I'd also like to know if there is a way to get the planner to burp 
>>out all the possible plans it considered before selecting a final plan 
>>or do I need to do a little surgery to get that done?
>>
>
>You can define OPTIMIZER_DEBUG but the interface leaves a lot to be
>desired (output to backend stdout, no way to turn it on or off except
>recompile...)  Also, I believe all you will see are the paths that
>survived the initial pruning done by add_path.  This is about the
>right level of detail for examining join choices, but perhaps not very
>helpful for why-didn't-it-use-my-index choices; the paths you wanted
>to know about may not have got into the relation's candidate-path list
>in the first place.
>
Alright, that gives me some places to attack it at then anyway.  Thanks 
very much Tom.  Sounds like I'll probably be doing a little bit of work 
IE I'd like to have the information come back as say a notice or maybe 
as extra information for an EXPLAIN for my purposes, but unless there is 
interest, consensus on how it should be done, and a TODO item made of 
it, I won't be making a patch of that, no reason to clutter the backend 
with stuff that hopefully won't be needed for long :)

Michael

Michael Loftis <mloftis@wgops.com> writes:
> IE I'd like to have the information come back as say a notice or maybe 
> as extra information for an EXPLAIN for my purposes, but unless there is 
> interest, consensus on how it should be done, and a TODO item made of 
> it, I won't be making a patch of that, no reason to clutter the backend 
> with stuff that hopefully won't be needed for long :)

I think it'd be useful to have, actually, as long as we're not talking
about much code bloat.  I tend to try to find a way to see what I want
with EXPLAIN, because using OPTIMIZER_DEBUG is such a pain.  But it's
often difficult to force the plan I'm interested in to rise to the top.
A nicer user interface for looking at the rejected alternatives would
seem like a step forward to me, whether or not ordinary users have any
need for it...
        regards, tom lane

Tom Lane wrote:
> Michael Loftis <mloftis@wgops.com> writes:
> > IE I'd like to have the information come back as say a notice or maybe 
> > as extra information for an EXPLAIN for my purposes, but unless there is 
> > interest, consensus on how it should be done, and a TODO item made of 
> > it, I won't be making a patch of that, no reason to clutter the backend 
> > with stuff that hopefully won't be needed for long :)
> 
> I think it'd be useful to have, actually, as long as we're not talking
> about much code bloat.  I tend to try to find a way to see what I want
> with EXPLAIN, because using OPTIMIZER_DEBUG is such a pain.  But it's
> often difficult to force the plan I'm interested in to rise to the top.
> A nicer user interface for looking at the rejected alternatives would
> seem like a step forward to me, whether or not ordinary users have any
> need for it...

I think there is consensus.  Added to TODO:
Improve ability to display optimizer analysis using OPTIMIZER_DEBUG

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026