Re: Get explain output of postgresql in Tables

On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote:
> >
> >><NestedLoop cost="1.06..40.43" rows="5" width="244">
> >> <JoinFilter publicTenk1Unique2="int4_tbl.f1">
> >> <HashAggregate cost="1.06..1.11" rows="5" width="4"/>
> >> </JoinFilter>
> >></NestedLoop>
> >
> >
> >Well, the downside is that such a format means explain output is now
> >twice as long. But I'd love to see something like that as an option. I'd
> >also still like to see an SQL-parseable version as well, since I think
> >there's applications for that.
>
> On the plus side, a complex xml document is an easy read in a browser (IE
> or Firefox, either way). Hard to picture the representation in relational
> tables, though ... did you have some specific idea for what to do with a
> plan in SQL,
> once it was parsed?

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

Some possibilities...

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

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

The list goes on. Like I said, you could do all these things with XML,
you just couldn't easily do them within the database.
--
Jim C. Nasby, Sr. Engineering Consultant 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