Get explain output of postgresql in Tables

"Akshat Nair" <akshat.nair@gmail.com> writes:

Can I get the grammar for the explain output?

There isn't one, it's just text and subject to change at a moment's
notice :-(. The past proposals that we format it a bit more rigidly
have so far foundered for lack of a workable definition of what the
structure should be. It's still an open problem to devise that
definition.

regards, tom lane

On Thu, Mar 23, 2006 at 12:39:52AM -0500, Tom Lane wrote:

"Akshat Nair" <akshat.nair@gmail.com> writes:

Can I get the grammar for the explain output?

There isn't one, it's just text and subject to change at a moment's
notice :-(. The past proposals that we format it a bit more rigidly
have so far foundered for lack of a workable definition of what the
structure should be. It's still an open problem to devise that
definition.

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.

How about (well-formed) XML format?
Anyone menthioned in the past threads?

I guess XML is good for the explain structure.
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

Satoshi Nagayasu wrote:

Jim C. Nasby wrote:

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.

How about (well-formed) XML format?

A friend developed a patch for this. He offered to post it but I don't
think there was any reaction at all.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Satoshi Nagayasu wrote:

Jim C. Nasby wrote:

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.

How about (well-formed) XML format?

A friend developed a patch for this. He offered to post it but I don't
think there was any reaction at all.

Very interesting.

I guess the machine-friendly expalin format is important for query tools,
such as Visual Explain, pgAdminIII Query and so on.
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

On Fri, Mar 24, 2006 at 07:54:09AM +0900, Satoshi Nagayasu wrote:

Jim C. Nasby wrote:

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.

How about (well-formed) XML format?
Anyone menthioned in the past threads?

I guess XML is good for the explain structure.

Unless you want to actually analyze the output in something like
plpgsql, but I can certainly see uses for both. Perhaps getting one
implimented will make it easier to implement the other.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Fri, Mar 24, 2006 at 07:54:09AM +0900, Satoshi Nagayasu wrote:

Jim C. Nasby wrote:

Structure for the human-consumable output or for something that would be
machine-parsed? ISTM it would be best to keep the current output as-is,
and provide some other means for producing machine-friendly output,
presumably in a table format.

How about (well-formed) XML format?
Anyone menthioned in the past threads?

I guess XML is good for the explain structure.

Unless you want to actually analyze the output in something like
plpgsql, but I can certainly see uses for both. Perhaps getting one
implimented will make it easier to implement the other.

TODO has:

* Allow EXPLAIN output to be more easily processed by scripts

--
Bruce Momjian http://candle.pha.pa.us

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

* Allow EXPLAIN output to be more easily processed by scripts

Can I request an extension/additional point?
* Design EXPLAIN output to survive cut & paste on mailing-lists

Being able to paste into a web-form and get something readable formatted
back would be very useful on the lists. Sometimes it takes me longer to
reformat the explain than it does to understand the problem.

--
Richard Huxton
Archonet Ltd

On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote:

Bruce Momjian wrote:

* Allow EXPLAIN output to be more easily processed by scripts

Can I request an extension/additional point?
* Design EXPLAIN output to survive cut & paste on mailing-lists

Being able to paste into a web-form and get something readable formatted
back would be very useful on the lists. Sometimes it takes me longer to
reformat the explain than it does to understand the problem.

Actually, I've been wondering about better ways to handle this. One
thought is to come up with a non-human readable format that could easily
be cut and pasted into a website that would then provide something easy
to understand. Ideally that website could also produce graphical output
like pgAdmin does, since that makes it trivially easy to see what the
'critical path' is.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote:

Bruce Momjian wrote:

* Allow EXPLAIN output to be more easily processed by scripts

Can I request an extension/additional point?
* Design EXPLAIN output to survive cut & paste on mailing-lists

Being able to paste into a web-form and get something readable formatted
back would be very useful on the lists. Sometimes it takes me longer to
reformat the explain than it does to understand the problem.

Actually, I've been wondering about better ways to handle this. One
thought is to come up with a non-human readable format that could easily
be cut and pasted into a website that would then provide something easy
to understand. Ideally that website could also produce graphical output
like pgAdmin does, since that makes it trivially easy to see what the
'critical path' is.

I actually started putting something like this together about a year
ago, but the majority of my time was spent reformatting the text rather
than reading the explain.

I've still got a simple perl script that just looks for the most costly
steps in an explain and prints their line-number. Lots of false
positives but it helps to give a starting point for investigations.

--
Richard Huxton
Archonet Ltd

Richard Huxton <dev@archonet.com> writes:

Jim C. Nasby wrote:

Actually, I've been wondering about better ways to handle this. One
thought is to come up with a non-human readable format that could easily
be cut and pasted into a website that would then provide something easy
to understand. Ideally that website could also produce graphical output
like pgAdmin does, since that makes it trivially easy to see what the
'critical path' is.

I actually started putting something like this together about a year
ago, but the majority of my time was spent reformatting the text rather
than reading the explain.

I dislike the thought of encouraging people to post stuff in a
not-easily-readable format. They won't do it anyway, if it's not
default; look how we still can't get people to send EXPLAIN ANALYZE
output the first time.

One idea that comes to mind is to work up some trivial little script
that undoes the more common forms of cut-and-paste damage.

I wonder if it would help much just to change EXPLAIN to indent with
something other than spaces? Maybe instead of

Nested Loop (cost=1.06..40.43 rows=5 width=244)
Join Filter: (public.tenk1.unique2 = int4_tbl.f1)
-> HashAggregate (cost=1.06..1.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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Ühel kenal päeval, K, 2006-04-12 kell 10:29, kirjutas Jim C. Nasby:

On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote:

<NestedLoop cost="1.06..40.43" rows="5" width="244">
<JoinFilter publicTenk1Unique2="int4_tbl.f1">
<HashAggregate cost="1.06..1.11" rows="5" width="4"/>
</JoinFilter>
</NestedLoop>

Well, the downside is that such a format means explain output is now
twice as long.

You can place end tags differently

<NestedLoop cost="1.06..40.43" rows="5" width="244">
<JoinFilter left="publicTenk1Unique2" right="int4_tbl.f1">
<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@turnstep.com
PGP Key: 0x14964AC8 200604121213
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEPSkQvJuQZxSWSsgRAsc3AKDEWkJR6hHr2/Rgwgk49UNhGVtR6ACgo91Z
7Ck46wiCWoVvGW6V/AR7wAo=
=UKnc
-----END PGP SIGNATURE-----

Jim C. Nasby wrote:

On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote:

<NestedLoop cost="1.06..40.43" rows="5" width="244">
<JoinFilter publicTenk1Unique2="int4_tbl.f1">
<HashAggregate cost="1.06..1.11" rows="5" width="4"/>
</JoinFilter>
</NestedLoop>

Well, the downside is that such a format means explain output is now
twice as long. But I'd love to see something like that as an option. I'd
also still like to see an SQL-parseable version as well, since I think
there's applications for that.

On the plus side, a complex xml document is an easy read in a browser (IE or
Firefox, either way). Hard to picture the representation in relational tables,
though ... did you have some specific idea for what to do with a plan in SQL,
once it was parsed?

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Greg Sabino Mullane wrote:

I wonder if it would help much just to change EXPLAIN to indent with
something other than spaces?

I like that. Maybe even decrease the indenting a little more, and compress
some of the inner whitespace (such as the 2 spaces after the operator name)

Might it be worth checking how many people (and apps) use EXPLAIN output to
drive apps? Our (web) reporting has a paging system for long reports, that
depends on getting the row/cost estimate from "EXPLAIN somequery" before
actually executing "somequery". (Yep, we have pg_autovacuum run ANALYZE a lot :-)

Anybody else out there using explain output in an automated way?

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote:

Jim C. Nasby wrote:

On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote:

<NestedLoop cost="1.06..40.43" rows="5" width="244">
<JoinFilter publicTenk1Unique2="int4_tbl.f1">
<HashAggregate cost="1.06..1.11" rows="5" width="4"/>
</JoinFilter>
</NestedLoop>

Well, the downside is that such a format means explain output is now
twice as long. But I'd love to see something like that as an option. I'd
also still like to see an SQL-parseable version as well, since I think
there's applications for that.

On the plus side, a complex xml document is an easy read in a browser (IE
or Firefox, either way). Hard to picture the representation in relational
tables, though ... did you have some specific idea for what to do with a
plan in SQL,
once it was parsed?

Well, really just about anything you'd want to do with it in an XML
format. The advantage of SQL is that you can do it within the database,
and you don't have to worry about having something around that can
process XML.

Some possibilities...

Having an SQL format would make it easier to allow for a mode that
captures explain or explain analyze output from every query. Turn that
mode on, run an application's test suite, and now you have a pretty good
idea of how all the queries will run. Or, take a production system and
turn that option on for a single connection. Another option is to have
any queries that take more than X amount of time store an EXPLAIN of the
query.

Having this info in machine format would make it easier to write
something that sets the various cost estimator values (random_page_cost,
etc).

The list goes on. Like I said, you could do all these things with XML,
you just couldn't easily do them within the database.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461