Thread: Get explain output of postgresql in Tables
Hi<br /><br />I read a post in the archives saying about storing explain output directly into tables. Is this feature presentin postgres now??<br />I have a software in which I need to display the explain output in a Tree format, for whichI need to parse the textual plan and get the relvant information. <br />I have a parser written in java which does somework but its not completely working. Can I get the grammar for the explain output? Or if someone has <br />some otheridea please let me know.<br /><br />Thanks<br /><br />-Akshat <br />
"Akshat Nair" <akshat.nair@gmail.com> writes:
> Can I get the grammar for the explain output?
There isn't one, it's just text and subject to change at a moment's
notice :-(. The past proposals that we format it a bit more rigidly
have so far foundered for lack of a workable definition of what the
structure should be. It's still an open problem to devise that
definition.
regards, tom lane
On Thu, Mar 23, 2006 at 12:39:52AM -0500, Tom Lane wrote: > "Akshat Nair" <akshat.nair@gmail.com> writes: > > Can I get the grammar for the explain output? > > There isn't one, it's just text and subject to change at a moment's > notice :-(. The past proposals that we format it a bit more rigidly > have so far foundered for lack of a workable definition of what the > structure should be. It's still an open problem to devise that > definition. Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > Structure for the human-consumable output or for something that would be > machine-parsed? ISTM it would be best to keep the current output as-is, > and provide some other means for producing machine-friendly output, > presumably in a table format. How about (well-formed) XML format? Anyone menthioned in the past threads? I guess XML is good for the explain structure. -- NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
Satoshi Nagayasu wrote: > Jim C. Nasby wrote: > > Structure for the human-consumable output or for something that would be > > machine-parsed? ISTM it would be best to keep the current output as-is, > > and provide some other means for producing machine-friendly output, > > presumably in a table format. > > How about (well-formed) XML format? A friend developed a patch for this. He offered to post it but I don't think there was any reaction at all. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Satoshi Nagayasu wrote: > >>Jim C. Nasby wrote: >> >>>Structure for the human-consumable output or for something that would be >>>machine-parsed? ISTM it would be best to keep the current output as-is, >>>and provide some other means for producing machine-friendly output, >>>presumably in a table format. >> >>How about (well-formed) XML format? > > > A friend developed a patch for this. He offered to post it but I don't > think there was any reaction at all. Very interesting. I guess the machine-friendly expalin format is important for query tools, such as Visual Explain, pgAdminIII Query and so on. -- NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
On Fri, Mar 24, 2006 at 07:54:09AM +0900, Satoshi Nagayasu wrote: > Jim C. Nasby wrote: > > Structure for the human-consumable output or for something that would be > > machine-parsed? ISTM it would be best to keep the current output as-is, > > and provide some other means for producing machine-friendly output, > > presumably in a table format. > > How about (well-formed) XML format? > Anyone menthioned in the past threads? > > I guess XML is good for the explain structure. Unless you want to actually analyze the output in something like plpgsql, but I can certainly see uses for both. Perhaps getting one implimented will make it easier to implement the other. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Fri, Mar 24, 2006 at 07:54:09AM +0900, Satoshi Nagayasu wrote: > > Jim C. Nasby wrote: > > > Structure for the human-consumable output or for something that would be > > > machine-parsed? ISTM it would be best to keep the current output as-is, > > > and provide some other means for producing machine-friendly output, > > > presumably in a table format. > > > > How about (well-formed) XML format? > > Anyone menthioned in the past threads? > > > > I guess XML is good for the explain structure. > > Unless you want to actually analyze the output in something like > plpgsql, but I can certainly see uses for both. Perhaps getting one > implimented will make it easier to implement the other. TODO has: * Allow EXPLAIN output to be more easily processed by scripts -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > > * Allow EXPLAIN output to be more easily processed by scripts Can I request an extension/additional point? * Design EXPLAIN output to survive cut & paste on mailing-lists Being able to paste into a web-form and get something readable formatted back would be very useful on the lists. Sometimes it takes me longer to reformat the explain than it does to understand the problem. -- Richard Huxton Archonet Ltd
On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote: > Bruce Momjian wrote: > > > > * Allow EXPLAIN output to be more easily processed by scripts > > Can I request an extension/additional point? > * Design EXPLAIN output to survive cut & paste on mailing-lists > > Being able to paste into a web-form and get something readable formatted > back would be very useful on the lists. Sometimes it takes me longer to > reformat the explain than it does to understand the problem. Actually, I've been wondering about better ways to handle this. One thought is to come up with a non-human readable format that could easily be cut and pasted into a website that would then provide something easy to understand. Ideally that website could also produce graphical output like pgAdmin does, since that makes it trivially easy to see what the 'critical path' is. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote: >> Bruce Momjian wrote: >>> * Allow EXPLAIN output to be more easily processed by scripts >> Can I request an extension/additional point? >> * Design EXPLAIN output to survive cut & paste on mailing-lists >> >> Being able to paste into a web-form and get something readable formatted >> back would be very useful on the lists. Sometimes it takes me longer to >> reformat the explain than it does to understand the problem. > > Actually, I've been wondering about better ways to handle this. One > thought is to come up with a non-human readable format that could easily > be cut and pasted into a website that would then provide something easy > to understand. Ideally that website could also produce graphical output > like pgAdmin does, since that makes it trivially easy to see what the > 'critical path' is. I actually started putting something like this together about a year ago, but the majority of my time was spent reformatting the text rather than reading the explain. I've still got a simple perl script that just looks for the most costly steps in an explain and prints their line-number. Lots of false positives but it helps to give a starting point for investigations. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Jim C. Nasby wrote:
>> Actually, I've been wondering about better ways to handle this. One
>> thought is to come up with a non-human readable format that could easily
>> be cut and pasted into a website that would then provide something easy
>> to understand. Ideally that website could also produce graphical output
>> like pgAdmin does, since that makes it trivially easy to see what the
>> 'critical path' is.
> I actually started putting something like this together about a year
> ago, but the majority of my time was spent reformatting the text rather
> than reading the explain.
I dislike the thought of encouraging people to post stuff in a
not-easily-readable format. They won't do it anyway, if it's not
default; look how we still can't get people to send EXPLAIN ANALYZE
output the first time.
One idea that comes to mind is to work up some trivial little script
that undoes the more common forms of cut-and-paste damage.
I wonder if it would help much just to change EXPLAIN to indent with
something other than spaces? Maybe instead of
Nested Loop (cost=1.06..40.43 rows=5 width=244) Join Filter: (public.tenk1.unique2 = int4_tbl.f1) -> HashAggregate
(cost=1.06..1.11rows=5 width=4)
print
Nested Loop (cost=1.06..40.43 rows=5 width=244)
--Join Filter: (public.tenk1.unique2 = int4_tbl.f1)
----> HashAggregate (cost=1.06..1.11 rows=5 width=4)
Not sure what would look nice, but this would at least remove the hazard
from stuff that thinks whitespace isn't significant.
regards, tom lane
Tom Lane wrote: > > I dislike the thought of encouraging people to post stuff in a > not-easily-readable format. They won't do it anyway, if it's not > default; look how we still can't get people to send EXPLAIN ANALYZE > output the first time. It certainly needs to be one format for both purposes. > One idea that comes to mind is to work up some trivial little script > that undoes the more common forms of cut-and-paste damage. > > I wonder if it would help much just to change EXPLAIN to indent with > something other than spaces? Maybe instead of > > Nested Loop (cost=1.06..40.43 rows=5 width=244) > Join Filter: (public.tenk1.unique2 = int4_tbl.f1) > -> HashAggregate (cost=1.06..1.11 rows=5 width=4) > > print > > Nested Loop (cost=1.06..40.43 rows=5 width=244) > --Join Filter: (public.tenk1.unique2 = int4_tbl.f1) > ----> HashAggregate (cost=1.06..1.11 rows=5 width=4) > > Not sure what would look nice, but this would at least remove the hazard > from stuff that thinks whitespace isn't significant. That's the sort of thing I was thinking of, or even something like: 1> Nested Loop ... 1.1> Join Filter... 1.1.1> HashAggregate... 1.2> etc -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Tom Lane wrote: >> >> I dislike the thought of encouraging people to post stuff in a >> not-easily-readable format. They won't do it anyway, if it's not >> default; look how we still can't get people to send EXPLAIN ANALYZE >> output the first time. > > It certainly needs to be one format for both purposes. > >> One idea that comes to mind is to work up some trivial little script >> that undoes the more common forms of cut-and-paste damage. >> >> I wonder if it would help much just to change EXPLAIN to indent with >> something other than spaces? Maybe instead of >> >> Nested Loop (cost=1.06..40.43 rows=5 width=244) >> Join Filter: (public.tenk1.unique2 = int4_tbl.f1) >> -> HashAggregate (cost=1.06..1.11 rows=5 width=4) >> >> print >> >> Nested Loop (cost=1.06..40.43 rows=5 width=244) >> --Join Filter: (public.tenk1.unique2 = int4_tbl.f1) >> ----> HashAggregate (cost=1.06..1.11 rows=5 width=4) >> >> Not sure what would look nice, but this would at least remove the hazard >> from stuff that thinks whitespace isn't significant. > > That's the sort of thing I was thinking of, or even something like: > 1> Nested Loop ... > 1.1> Join Filter... > 1.1.1> HashAggregate... > 1.2> etc > Why not go all the way. Here's the above using Satoshi's suggestion: <NestedLoop cost="1.06..40.43" rows="5" width="244"> <JoinFilter publicTenk1Unique2="int4_tbl.f1"> <HashAggregatecost="1.06..1.11" rows="5" width="4"/> </JoinFilter> </NestedLoop> Easy to copy/paste and whitespace doesn't matter. Easy to read (well, to some at least) and can be even easier if you have access to an XML viewer. Regards, Thomas Hallgren
On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > <NestedLoop cost="1.06..40.43" rows="5" width="244"> > <JoinFilter publicTenk1Unique2="int4_tbl.f1"> > <HashAggregate cost="1.06..1.11" rows="5" width="4"/> > </JoinFilter> > </NestedLoop> Well, the downside is that such a format means explain output is now twice as long. But I'd love to see something like that as an option. I'd also still like to see an SQL-parseable version as well, since I think there's applications for that. As for those who can't manage to post EXPLAIN ANALYZE to the list; as long as ANALYZE isn't the default I don't see how making a less human-readable version the default will solve anything, because we'll still perpetually be asking people for the output of EXPLAIN ANALYZE. If we want to increase the number of people who provide useful information in initial performance questions, the answer is to make the information about what to submit more prominent. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, K, 2006-04-12 kell 10:29, kirjutas Jim C. Nasby: > On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > <NestedLoop cost="1.06..40.43" rows="5" width="244"> > > <JoinFilter publicTenk1Unique2="int4_tbl.f1"> > > <HashAggregate cost="1.06..1.11" rows="5" width="4"/> > > </JoinFilter> > > </NestedLoop> > > Well, the downside is that such a format means explain output is now > twice as long. You can place end tags differently <NestedLoop cost="1.06..40.43" rows="5" width="244"> <JoinFilter left="publicTenk1Unique2" right="int4_tbl.f1"> <HashAggregatecost="1.06..1.11" rows="5" width="4"/></JoinFilter></NestedLoop> > But I'd love to see something like that as an option. Me too > I'd also still like to see an SQL-parseable version as well, since I think > there's applications for that. > > As for those who can't manage to post EXPLAIN ANALYZE to the list; as > long as ANALYZE isn't the default I don't see how making a less > human-readable version the default will solve anything, because we'll > still perpetually be asking people for the output of EXPLAIN ANALYZE. If > we want to increase the number of people who provide useful information > in initial performance questions, the answer is to make the information > about what to submit more prominent. We could also default to printing a NOTICE at the end of EXPLAIN, which tells users thus: "If you plan to post this output to pgsql-hackers list, you better post result of EXPLAIN ANALYSE" :P ------------ Hannu
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I wonder if it would help much just to change EXPLAIN to indent with > something other than spaces? I like that. Maybe even decrease the indenting a little more, and compress some of the inner whitespace (such as the 2 spaces after the operator name) One other thing I've done in the past that helps a lot is to simplify the text by using "L" for loops, "W" for width, "C" for cost, and "R" for rows, and even "AT" for "actual time". This ends up saveing an enormous amount of horizontal screen space, and is a really easy intuitive one-time learning curve. Normal verbose way: Sort (cost=11383.82..11383.83 rows=1 width=38) (actual time=18942.712..18942.741 rows=9 loops=1) Sort Key: count(*) -> HashAggregate (cost=11383.80..11383.81 rows=1 width=38) (actual time=18942.581..18942.612 rows=9 loops=1) -> Bitmap Heap Scan on turnstep_mail (cost=134.73..11383.79 rows=1 width=38) (actual time=17085.967..18941.677 rows=193 loops=1) Tom + Greg style: Sort (C=11383.82..11383.83 R=1 W=38) (AT=18942.712..18942.741 R=9 L=1) - -Sort Key: count(*) - -->HashAggregate (C=11383.80..11383.81 R=1 W=38) (AT=18942.581..18942.612 R=9 L=1) - ---->Bitmap Heap Scan on turnstep_mail (C=134.73..11383.79 R=1 W=38) (AT=17085.967..18941.677 R=193 L=1) I use capital letters as it makes it easier to read, especially for things like the common single loop (L=1 vs. l=1) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200604121213 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFEPSkQvJuQZxSWSsgRAsc3AKDEWkJR6hHr2/Rgwgk49UNhGVtR6ACgo91Z 7Ck46wiCWoVvGW6V/AR7wAo= =UKnc -----END PGP SIGNATURE-----
Jim C. Nasby wrote: > On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > >><NestedLoop cost="1.06..40.43" rows="5" width="244"> >> <JoinFilter publicTenk1Unique2="int4_tbl.f1"> >> <HashAggregate cost="1.06..1.11" rows="5" width="4"/> >> </JoinFilter> >></NestedLoop> > > > Well, the downside is that such a format means explain output is now > twice as long. But I'd love to see something like that as an option. I'd > also still like to see an SQL-parseable version as well, since I think > there's applications for that. On the plus side, a complex xml document is an easy read in a browser (IE or Firefox, either way). Hard to picture the representation in relational tables, though ... did you have some specific idea for what to do with a plan in SQL, once it was parsed? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.
Greg Sabino Mullane wrote: >>I wonder if it would help much just to change EXPLAIN to indent with >>something other than spaces? > > I like that. Maybe even decrease the indenting a little more, and compress > some of the inner whitespace (such as the 2 spaces after the operator name) Might it be worth checking how many people (and apps) use EXPLAIN output to drive apps? Our (web) reporting has a paging system for long reports, that depends on getting the row/cost estimate from "EXPLAIN somequery" before actually executing "somequery". (Yep, we have pg_autovacuum run ANALYZE a lot :-) Anybody else out there using explain output in an automated way? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.
On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote: > Jim C. Nasby wrote: > >On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > >><NestedLoop cost="1.06..40.43" rows="5" width="244"> > >> <JoinFilter publicTenk1Unique2="int4_tbl.f1"> > >> <HashAggregate cost="1.06..1.11" rows="5" width="4"/> > >> </JoinFilter> > >></NestedLoop> > > > > > >Well, the downside is that such a format means explain output is now > >twice as long. But I'd love to see something like that as an option. I'd > >also still like to see an SQL-parseable version as well, since I think > >there's applications for that. > > On the plus side, a complex xml document is an easy read in a browser (IE > or Firefox, either way). Hard to picture the representation in relational > tables, though ... did you have some specific idea for what to do with a > plan in SQL, > once it was parsed? Well, really just about anything you'd want to do with it in an XML format. The advantage of SQL is that you can do it within the database, and you don't have to worry about having something around that can process XML. Some possibilities... Having an SQL format would make it easier to allow for a mode that captures explain or explain analyze output from every query. Turn that mode on, run an application's test suite, and now you have a pretty good idea of how all the queries will run. Or, take a production system and turn that option on for a single connection. Another option is to have any queries that take more than X amount of time store an EXPLAIN of the query. Having this info in machine format would make it easier to write something that sets the various cost estimator values (random_page_cost, etc). The list goes on. Like I said, you could do all these things with XML, you just couldn't easily do them within the database. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, 2006-04-12 at 14:38 -0500, Jim C. Nasby wrote: > On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote: > > Jim C. Nasby wrote: > > >On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > > > >><NestedLoop cost="1.06..40.43" rows="5" width="244"> > > >> <JoinFilter publicTenk1Unique2="int4_tbl.f1"> > > >> <HashAggregate cost="1.06..1.11" rows="5" width="4"/> > > >> </JoinFilter> > > >></NestedLoop> > > > > > > > > >Well, the downside is that such a format means explain output is now > > >twice as long. But I'd love to see something like that as an option. I'd > > >also still like to see an SQL-parseable version as well, since I think > > >there's applications for that. > [...] We can get the best of both worlds. For instance, EXPLAIN and EXPLAIN ANALYZE with the usual output; but also EXPLAIN XML and EXPLAIN ANALYZE XML with an XML syntax to be used by programs. I have a patch for this behavior, but unfortunately this is not updated. It was made by the time that postgresql 8.0 was beta without any chance to get feedback (everybody were fixing bugs). The strategy was quite simple. It was implemented inside on explain.c; with an extra parameter. So, if any change could happen in the normal output of explain, it could be easier to update the XML one. Get it updated should not be so much hours of work. At this moment I do not have that time :-( -- Germán Poó-Caamaño http://www.ubiobio.cl/~gpoo/ Concepción - Chile
Hi, Germán Poó Caamaño escribió: > We can get the best of both worlds. > > For instance, EXPLAIN and EXPLAIN ANALYZE with the usual output; but > also EXPLAIN XML and EXPLAIN ANALYZE XML with an XML syntax to be > used by programs. > > I have a patch for this behavior, but unfortunately this is not > updated. It was made by the time that postgresql 8.0 was beta > without any chance to get feedback (everybody were fixing bugs). > > The strategy was quite simple. It was implemented inside on > explain.c; with an extra parameter. So, if any change could > happen in the normal output of explain, it could be easier to > update the XML one. > > Get it updated should not be so much hours of work. At this > moment I do not have that time :-( I suggest you post it to -patches. If someone is interested, he or she can update it. (Or if you posted it back then, can you provide the link to the archives?) It would be nice to see the "visual explain" tool that Denis wrote -- did he finish it? Is it available somewhere? Are there any screenshots? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Ühel kenal päeval, K, 2006-04-12 kell 14:38, kirjutas Jim C. Nasby: > Well, really just about anything you'd want to do with it in an XML > format. The advantage of SQL is that you can do it within the database, > and you don't have to worry about having something around that can > process XML. > > Some possibilities... > > Having an SQL format would make it easier to allow for a mode that > captures explain or explain analyze output from every query. Turn that > mode on, run an application's test suite, and now you have a pretty good > idea of how all the queries will run. Maybe. Depending on how much preprocessing is done before saving, this can be true. Just storing something in "SQL format" (whatever that is) doesn't not magically make it easy to process. And storing an XML string is no more complicated than storing a set of records. > Or, take a production system and > turn that option on for a single connection. Another option is to have > any queries that take more than X amount of time store an EXPLAIN of the > query. OTOH, on a production system, where performance matters, you probably still would prefer a format where collecting data is fast, and storing 1 row per plan will always be faster than storing many, especially with indexes. > Having this info in machine format would make it easier to write > something that sets the various cost estimator values (random_page_cost, > etc). I guess that this needs to be written in C anyhow, and parsing a defined subset of XML is not that hard. > The list goes on. Like I said, you could do all these things with XML, > you just couldn't easily do them within the database. I'm not sure about it, at least without a specific example. Processing tree-structured data is not a thing that SQL is very good at. ------------- Hannu
Alvaro Herrera <alvherre@commandprompt.com> writes: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? Red Hat did one of these some years ago: http://sources.redhat.com/rhdb/visualexplain.html I don't see a prebuilt package on that page, but I believe the sources are still available here: http://sources.redhat.com/rhdb/cvs.html regards, tom lane
Ühel kenal päeval, K, 2006-04-12 kell 17:42, kirjutas Alvaro Herrera: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? IIRC there is a "visual explain" tool pin pgAdmin III ------- Hannu
-----Original Message----- From: "Tom Lane"<tgl@sss.pgh.pa.us> Sent: 12/04/06 23:03:08 To: "Alvaro Herrera"<alvherre@commandprompt.com> Cc: "Germán Poó Caamaño"<gpoo@ubiobio.cl>, "Jim C. Nasby"<jnasby@pervasive.com>, "mischa@ActiveState.com"<mischa@ActiveState.com>,"pgsql-hackers@postgresql.org"<pgsql-hackers@postgresql.org> Subject: Re: [HACKERS] Get explain output of postgresql in Tables > Alvaro Herrera <alvherre@commandprompt.com> writes: > > It would be nice to see the "visual explain" tool that Denis wrote -- > > did he finish it? Is it available somewhere? Are there any screenshots? > Red Hat did one of these some years ago: > http://sources.redhat.com/rhdb/visualexplain.html pgAdmin also has visual explain capabilities. /D -----Unmodified Original Message----- Alvaro Herrera <alvherre@commandprompt.com> writes: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? Red Hat did one of these some years ago: http://sources.redhat.com/rhdb/visualexplain.html I don't see a prebuilt package on that page, but I believe the sources are still available here: http://sources.redhat.com/rhdb/cvs.html regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Jim, > The list goes on. Like I said, you could do all these things with XML, > you just couldn't easily do them within the database. XML --> Table conversion should be relatively easy with PL/Perl, PL/Java, and/or an external language. Heck, if we could expand our XML tools (Peter will have a talk on this at the Summit) we could do it in the database by simple function call. If we have an XML patch now, I say use it. I know I want it. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
"Jim C. Nasby" <jnasby@pervasive.com> writes: > Having an SQL format would make it easier to allow for a mode that > captures explain or explain analyze output from every query. Turn that > mode on, run an application's test suite, and now you have a pretty good > idea of how all the queries will run. Or, take a production system and > turn that option on for a single connection. Another option is to have > any queries that take more than X amount of time store an EXPLAIN of the > query. > > Having this info in machine format would make it easier to write > something that sets the various cost estimator values (random_page_cost, > etc). I'm particularly fond of the idea of storing the info in an SQL table. When I first met this in Oracle it seemed awkward and annoying. But as I used it I found more and more reasons why it's useful. I had just such a mode for our application that explained queries before running them (actually just a 1 time in 100 to avoid performance impacts). I could look at an internal administrative web page that listed all queries that showed profiling information, execution counts, explain plan, etc. One advantage this would have is that the SQL table could include much more detailed information than the text output can readably display. Then there could be a function that displays the data from the SQL table in a format similar to the current EXPLAIN output and other functions to display additional information. -- greg
On Wed, Apr 12, 2006 at 03:34:05PM -0700, Josh Berkus wrote: > If we have an XML patch now, I say use it. I know I want it. Certainly; XML is better than nothing. But since it shouldn't be hard to add the ability to output a recordset at the same time... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Dave Page escribió: > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > It would be nice to see the "visual explain" tool that Denis wrote -- > > > did he finish it? Is it available somewhere? Are there any screenshots? > > > Red Hat did one of these some years ago: > > http://sources.redhat.com/rhdb/visualexplain.html > pgAdmin also has visual explain capabilities. How does it work? Does it parse the text representation? I found a screenshot here: http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png Seems nice (but lacking the attributes for each node ...) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Wed, Apr 12, 2006 at 07:28:25PM -0400, Alvaro Herrera wrote: > Dave Page escribi?: > > > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > > It would be nice to see the "visual explain" tool that Denis wrote -- > > > > did he finish it? Is it available somewhere? Are there any screenshots? > > > > > Red Hat did one of these some years ago: > > > http://sources.redhat.com/rhdb/visualexplain.html > > pgAdmin also has visual explain capabilities. > > How does it work? Does it parse the text representation? > > I found a screenshot here: > http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png > > Seems nice (but lacking the attributes for each node ...) To get the details you hover over each box. It would be nice if you could have it show that info on the main screen though... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> -----Original Message----- > From: Alvaro Herrera [mailto:alvherre@commandprompt.com] > Sent: 13 April 2006 00:28 > To: Dave Page > Cc: tgl@sss.pgh.pa.us; gpoo@ubiobio.cl; jnasby@pervasive.com; > mischa@ActiveState.com; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Get explain output of postgresql in Tables > > Dave Page escribió: > > > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > > It would be nice to see the "visual explain" tool that > Denis wrote > > > > -- did he finish it? Is it available somewhere? Are > there any screenshots? > > > > > Red Hat did one of these some years ago: > > > http://sources.redhat.com/rhdb/visualexplain.html > > pgAdmin also has visual explain capabilities. > > How does it work? Does it parse the text representation? Yes. > I found a screenshot here: > http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png > > Seems nice (but lacking the attributes for each node ...) They're there - dangle your mouse over a node (or left click it) and they popup. Regards, Dave.
> -----Original Message----- > From: Jim C. Nasby [mailto:jnasby@pervasive.com] > Sent: 13 April 2006 01:07 > To: Dave Page; tgl@sss.pgh.pa.us; gpoo@ubiobio.cl; > mischa@ActiveState.com; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Get explain output of postgresql in Tables > > On Wed, Apr 12, 2006 at 07:28:25PM -0400, Alvaro Herrera wrote: > > Dave Page escribi?: > > > > > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > > > It would be nice to see the "visual explain" tool that Denis > > > > > wrote -- did he finish it? Is it available > somewhere? Are there any screenshots? > > > > > > > Red Hat did one of these some years ago: > > > > http://sources.redhat.com/rhdb/visualexplain.html > > > pgAdmin also has visual explain capabilities. > > > > How does it work? Does it parse the text representation? > > > > I found a screenshot here: > > http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png > > > > Seems nice (but lacking the attributes for each node ...) > > To get the details you hover over each box. It would be nice > if you could have it show that info on the main screen though... It rapidly fills the canvas and becomes difficult to read - there's quite a bit of text to show. Regards, Dave.