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

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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