Thread: Parsing output of EXPLAIN command in PostgreSQL

Parsing output of EXPLAIN command in PostgreSQL

From
Αναστάσιος Αρβανίτης
Date:
I'm developing an application that requires parsing of
execution plans (those produced as output by issuing an EXPLAIN [query]
command). Are you aware of any Java library that I could use for this
purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.


Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1.
However,in that case it  
would better to have the XML Schema of the generated plans available.

Is there any other solution I am not aware of?

Thank you



Re: Parsing output of EXPLAIN command in PostgreSQL

From
Robert Haas
Date:
2011/11/10 Αναστάσιος Αρβανίτης <tasosarvanitis@yahoo.gr>:
> I'm developing an application that requires parsing of
> execution plans (those produced as output by issuing an EXPLAIN [query]
> command). Are you aware of any Java library that I could use for this
> purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.
>
> Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1.
However,in that case it 
> would better to have the XML Schema of the generated plans available.
>
> Is there any other solution I am not aware of?

Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
too, but that's in C++.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Parsing output of EXPLAIN command in PostgreSQL

From
Andrew Dunstan
Date:

On 11/10/2011 04:29 PM, Robert Haas wrote:
> 2011/11/10 Αναστάσιος Αρβανίτης<tasosarvanitis@yahoo.gr>:
>> I'm developing an application that requires parsing of
>> execution plans (those produced as output by issuing an EXPLAIN [query]
>> command). Are you aware of any Java library that I could use for this
>> purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.
>>
>> Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1.
However,in that case it
 
>> would better to have the XML Schema of the generated plans available.
>>
>> Is there any other solution I am not aware of?
> Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
> too, but that's in C++.
>

Pg--Explain is extremely well written, and should be easily translatable 
to Java if you really need to. The whole thing is less than 2000 lines, 
and a large part of that is comments.

cheers

andrew


Re: Parsing output of EXPLAIN command in PostgreSQL

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> On 11/10/2011 04:29 PM, Robert Haas wrote:
>> 2011/11/10 ���������� ���������<tasosarvanitis@yahoo.gr>:
>>> Is there any other solution I am not aware of?

>> Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
>> too, but that's in C++.

> Pg--Explain is extremely well written, and should be easily translatable 
> to Java if you really need to. The whole thing is less than 2000 lines, 
> and a large part of that is comments.

Nonetheless, it's solving the wrong problem.  Any program that is being
written today to read EXPLAIN output should be written to read one of
the machine-readable formats.  If you insist on reading the text format,
don't be surprised when (not if) it breaks in future releases, and don't
expect any sympathy from these quarters.

It really shouldn't be that difficult to come by pre-fab code to read
at least one of XML, JSON, or YAML into a suitable data structure.

Now, if you're looking for something that "understands" the resulting
data structure in more than a superficial fashion, that's a different
question.  But you'd need to get a lot more specific about what you
need it to do.
        regards, tom lane


Re: Parsing output of EXPLAIN command in PostgreSQL

From
Greg Smith
Date:
On 11/10/2011 11:10 AM, Αναστάσιος Αρβανίτης wrote:
> I'm developing an application that requires parsing of
> execution plans (those produced as output by issuing an EXPLAIN [query]
> command). Are you aware of any Java library that I could use for this
> purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.
>    

There's also a little Javascript program that consumes the JSON version at:

http://www.postgresonline.com/journal/archives/171-pgexplain90formats_part1.html
http://www.postgresonline.com/journal/archives/174-pgexplain90formats_part2.html

> Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1.
However,in that case it
 
> would better to have the XML Schema of the generated plans available.
>    

That's the easiest way to solve this problem in Java, and in that case 
most of the text-based code in Pg--Explain will just be a distraction.  
I know some of the earlier versions of XML EXPLAIN included a "DTD" 
option to output that, but I don't see that in the committed code.  I'm 
not sure where that is at actually; it's a good question.

The only reference to doing this I found was Andrew's blog:  
http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html 
where he talks about there being a RELAXNG specification for the XML 
output.  I can't find where that came from either.  Andrew?

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us



Re: Parsing output of EXPLAIN command in PostgreSQL

From
Andrew Dunstan
Date:

On 11/10/2011 04:59 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>
>> Pg--Explain is extremely well written, and should be easily translatable
>> to Java if you really need to. The whole thing is less than 2000 lines,
>> and a large part of that is comments.
> Nonetheless, it's solving the wrong problem.  Any program that is being
> written today to read EXPLAIN output should be written to read one of
> the machine-readable formats.
>             


Umm, it *does* handle all the formats:
   $ wc -l ../Pg--Explain/lib/Pg/Explain.pm ../Pg--Explain/lib/Pg/Explain/*      248 ../Pg--Explain/lib/Pg/Explain.pm
   75 ../Pg--Explain/lib/Pg/Explain/FromJSON.pm      182 ../Pg--Explain/lib/Pg/Explain/From.pm      202
../Pg--Explain/lib/Pg/Explain/FromText.pm     109 ../Pg--Explain/lib/Pg/Explain/FromXML.pm       77
../Pg--Explain/lib/Pg/Explain/FromYAML.pm     785 ../Pg--Explain/lib/Pg/Explain/Node.pm      292
../Pg--Explain/lib/Pg/Explain/StringAnonymizer.pm    1970 total
 


One of the obvious reasons for handling text is to deal with old servers 
before we had machine readable output.

cheers

andrew


Re: Parsing output of EXPLAIN command in PostgreSQL

From
Andrew Dunstan
Date:

On 11/10/2011 05:26 PM, Greg Smith wrote:
>   I know some of the earlier versions of XML EXPLAIN included a "DTD" 
> option to output that, but I don't see that in the committed code.  
> I'm not sure where that is at actually; it's a good question.
>
> The only reference to doing this I found was Andrew's blog:  
> http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html 
> where he talks about there being a RELAXNG specification for the XML 
> output.  I can't find where that came from either.  Andrew?


I created one at some stage, but I it's rather bitrotted. Essentially I 
think we decided that we were going to be pretty free to whack around 
the format and having a spec wasn't going to be terribly helpful.

cheers

andrew


Re: Parsing output of EXPLAIN command in PostgreSQL

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> On 11/10/2011 04:59 PM, Tom Lane wrote:
>> Nonetheless, it's solving the wrong problem.  Any program that is being
>> written today to read EXPLAIN output should be written to read one of
>> the machine-readable formats.

> Umm, it *does* handle all the formats:

The point I'm trying to make is that translating the parts that handle
text into another language seems like wasted effort.  depesz has a
specific problem to solve, namely wanting to accept emailed input from
users who are likely to send any format; and that requires him to expend
a lot of effort that is not likely to be necessary in any other setting.
        regards, tom lane