Re: Get explain output of postgresql in Tables

Hi

I read a post in the archives saying about storing explain output directly
into tables. Is this feature present in postgres now??
I have a software in which I need to display the explain output in a Tree
format, for which I need to parse the textual plan and get the relvant
information.
I have a parser written in java which does some work but its not completely
working. Can I get the grammar for the explain output? Or if someone has
some other idea please let me know.

Thanks

-Akshat

"Akshat Nair" <akshat(dot)nair(at)gmail(dot)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(dot)nair(at)gmail(dot)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(at)pervasive(dot)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(at)nttdata(dot)co(dot)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(at)nttdata(dot)co(dot)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(at)pervasive(dot)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(at)pervasive(dot)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(at)archonet(dot)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

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

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(at)pervasive(dot)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">
<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

-----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(at)turnstep(dot)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(at)pervasive(dot)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

Ü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

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

Alvaro Herrera <alvherre(at)commandprompt(dot)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

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(at)pervasive(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461