Thread: Ideas of "printing out" the alternative paths

Ideas of "printing out" the alternative paths

From
Zhan Li
Date:
When searching all the possible paths of executing a query, the optimizer finds and saves the cheapest paths for the top level rel. I'd like to check out all the paths the optimizer has ever considered, which I believe, are stored in the pathlist of the top level rel. But I do not have an idea of how to "print out" these paths to see them visually. Does anyone have an idea how I can achieve this?

Thanks,
Zhan

Re: Ideas of "printing out" the alternative paths

From
Tom Lane
Date:
Zhan Li <zhanli89@gmail.com> writes:
> When searching all the possible paths of executing a query, the optimizer
> finds and saves the cheapest paths for the top level rel. I'd like to check
> out all the paths the optimizer has ever considered, which I believe, are
> stored in the pathlist of the top level rel.

No, most of them have been thrown away long before that.  See add_path.
Also realize that in a large join problem, a lot of potential plans never
get explicitly considered, because the input paths get pruned before we
get to considering the join rel at all.  (If this were not so, planning
would take too long.)

People have experimented with having add_path print something about each
path that's fed to it, but the output tends to be voluminous and not all
that useful.
        regards, tom lane



Re: Ideas of "printing out" the alternative paths

From
Zhan Li
Date:
<div dir="ltr">Thank you for your reply Tom. Then a) what are exactly stored in the pathlist of top level rel? Paths
worthconsidering? b) I have been struggling to come up with a way to print the Path struct. If I can print a path the
waylike "A hash join (B nested loop join C)", that would be great. You mentioned people have printed "something" about
eachpath, can you please give me a hint of what's that and how to achieve that?</div><div class="gmail_extra"><br /><br
/><divclass="gmail_quote">On Thu, Nov 14, 2013 at 12:01 PM, Tom Lane <span dir="ltr"><<a
href="mailto:tgl@sss.pgh.pa.us"target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">Zhan Li <<a
href="mailto:zhanli89@gmail.com">zhanli89@gmail.com</a>>writes:<br /> > When searching all the possible paths of
executinga query, the optimizer<br /> > finds and saves the cheapest paths for the top level rel. I'd like to
check<br/> > out all the paths the optimizer has ever considered, which I believe, are<br /> > stored in the
pathlistof the top level rel.<br /><br /></div>No, most of them have been thrown away long before that.  See
add_path.<br/> Also realize that in a large join problem, a lot of potential plans never<br /> get explicitly
considered,because the input paths get pruned before we<br /> get to considering the join rel at all.  (If this were
notso, planning<br /> would take too long.)<br /><br /> People have experimented with having add_path print something
abouteach<br /> path that's fed to it, but the output tends to be voluminous and not all<br /> that useful.<br /><br />
                       regards, tom lane<br /></blockquote></div><br /></div> 

Re: Ideas of "printing out" the alternative paths

From
Tom Lane
Date:
Zhan Li <zhanli89@gmail.com> writes:
> Thank you for your reply Tom. Then a) what are exactly stored in the
> pathlist of top level rel? Paths worth considering? b) I have been
> struggling to come up with a way to print the Path struct. If I can print a
> path the way like "A hash join (B nested loop join C)", that would be
> great. You mentioned people have printed "something" about each path, can
> you please give me a hint of what's that and how to achieve that?

I don't think anyone's tried anything much smarter than
src/backend/nodes/outfuncs.c, or there's some more limited stuff at the
bottom of src/backend/optimizer/path/allpaths.c.  Reassembling into
something more human-readable than that would probably take some work.
        regards, tom lane