Thread: Get explain output of postgresql in Tables

Get explain output of postgresql in Tables

From
"Akshat Nair"
Date:
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 /> 

Re: Get explain output of postgresql in Tables

From
Tom Lane
Date:
"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


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Satoshi Nagayasu
Date:
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>


Re: Get explain output of postgresql in Tables

From
Alvaro Herrera
Date:
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.


Re: Get explain output of postgresql in Tables

From
Satoshi Nagayasu
Date:
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>


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Bruce Momjian
Date:
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. +


Re: Get explain output of postgresql in Tables

From
Richard Huxton
Date:
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


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Richard Huxton
Date:
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


Re: Get explain output of postgresql in Tables

From
Tom Lane
Date:
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


Re: Get explain output of postgresql in Tables

From
Richard Huxton
Date:
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


Re: Get explain output of postgresql in Tables

From
Thomas Hallgren
Date:
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


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Hannu Krosing
Date:
Ü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




Re: Get explain output of postgresql in Tables

From
"Greg Sabino Mullane"
Date:
-----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-----




Re: Get explain output of postgresql in Tables

From
Mischa Sandberg
Date:
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.


Re: Get explain output of postgresql in Tables

From
Mischa Sandberg
Date:
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.


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Germán Poó Caamaño
Date:
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



Re: Get explain output of postgresql in Tables

From
Alvaro Herrera
Date:
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.


Re: Get explain output of postgresql in Tables

From
Hannu Krosing
Date:
Ü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



Re: Get explain output of postgresql in Tables

From
Tom Lane
Date:
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


Re: Get explain output of postgresql in Tables

From
Hannu Krosing
Date:
Ü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



Re: Get explain output of postgresql in Tables

From
"Dave Page"
Date:

-----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



Re: Get explain output of postgresql in Tables

From
Josh Berkus
Date:
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


Re: Get explain output of postgresql in Tables

From
Greg Stark
Date:
"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



Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
Alvaro Herrera
Date:
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.


Re: Get explain output of postgresql in Tables

From
"Jim C. Nasby"
Date:
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


Re: Get explain output of postgresql in Tables

From
"Dave Page"
Date:

> -----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.


Re: Get explain output of postgresql in Tables

From
"Dave Page"
Date:

> -----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.