Re: [HACKERS] Get explain output of postgresql in Tables
-Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: 13 April 2006 00:28 To: Dave Page Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Get explain output of postgresql in Tables Dave Page escribió: Alvaro Herrera [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Get explain output of postgresql in Tables
-Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: 13 April 2006 01:07 To: Dave Page; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; 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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Get explain output of postgresql in Tables
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
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.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. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
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 HashAggregate cost=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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Get explain output of postgresql in Tables
Ü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 HashAggregate cost=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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604121213 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEPSkQvJuQZxSWSsgRAsc3AKDEWkJR6hHr2/Rgwgk49UNhGVtR6ACgo91Z 7Ck46wiCWoVvGW6V/AR7wAo= =UKnc -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
Ü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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Get explain output of postgresql in Tables
Alvaro Herrera [EMAIL PROTECTED] 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
Re: [HACKERS] Get explain output of postgresql in Tables
Ü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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
-Original Message- From: Tom Lane[EMAIL PROTECTED] Sent: 12/04/06 23:03:08 To: Alvaro Herrera[EMAIL PROTECTED] Cc: Germán Poó Caamaño[EMAIL PROTECTED], Jim C. Nasby[EMAIL PROTECTED], [EMAIL PROTECTED][EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [HACKERS] Get explain output of postgresql in Tables Alvaro Herrera [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
Dave Page escribió: Alvaro Herrera [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
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. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
On Thu, Mar 23, 2006 at 12:39:52AM -0500, Tom Lane wrote: Akshat Nair [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
Akshat Nair [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
