Обсуждение: 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