Parsing output of EXPLAIN command in PostgreSQL

Question

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?

The stuff Hugo Depesz has built is pretty fantastic. I doubt you'll find much else, and certainly nothing better. Can't you just call his modules as a web wervice or so? — C. Ramseyer
– C. Ramseyer, Commented Nov 10, 2011 at 16:12
FWIW there's also support for output in JSON and YAML format. — Milen A. Radev
– Milen A. Radev, Commented Nov 10, 2011 at 16:28

Bryce · Accepted Answer · 2012-01-11 07:26:23Z

4

You've got the right answer right there. For your automated needs use XML or the tools at: https://github.com/depesz/Pg--Explain

For the rest of us, the interactive version is a real gem, and I'm posting it here so people find it: http://explain.depesz.com/

Background on explain itself is at: http://www.postgresql.org/docs/current/static/sql-explain.html But there does not appear to be a reference to a DTD or defined schema for explain.

answered Jan 11, 2012 at 7:26

Bryce

8,8627 gold badges58 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Celuk Over a year ago

This can be helpful also if someone needs to parse depesz site output: github.com/celuk/…

CRM · Accepted Answer · 2014-07-06 19:00:37Z

0

I had the same requirement of parsing XML plan output of Explain command. I have used LibXML2 libray. It is easy to use and powerful. Try that.

answered Jul 6, 2014 at 19:00

CRM

1,3492 gold badges12 silver badges30 bronze badges

Comments

Weihang Jian · Accepted Answer · 2021-09-19 09:37:37Z

0

I have a similar requirement and solved by psql and jq, for example:

psql $PG_URL -P pager=off -Aqt -c 'EXPLAIN (FORMAT JSON) SELECT version()' | jq

You can also pipe to other programming languages you are familiar with, for example:

psql $PG_URL -P pager=off -Aqt -c 'EXPLAIN (FORMAT JSON) SELECT version()' |
  ruby -rjson -e 'pp JSON.parse($stdin.read)'

References

psql
jq

answered Sep 19, 2021 at 9:37

Weihang Jian

8,9456 gold badges53 silver badges60 bronze badges

Collectives™ on Stack Overflow

Parsing output of EXPLAIN command in PostgreSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related