Thread: Parsing output of EXPLAIN command in PostgreSQL
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
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
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
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
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
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
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
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