Of cause it is not so nicely pretty as the one from Tatiyants, but I use it now and then and it became a standard explain visualisation tool for many PostgreSQL users.
The table format from http://explain.depesz.com/ is very useful and one can understand a lot of details about your execution plan without the need to visualise in the form of a graph.
Also the default execution plan visualiser in pgAdmin looks really cool.
* In psql use \o plan.txt to redirect the output of explain to a file. It will be a long output because you must use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) and copying it from the terminal won't be fun.
* Remove the first two lines (header) and the last two (footer) leaving only the json data. Remove all the + characters at the end of every line. Suggestion to the author: the tool should handle that.
That said, it works. It really is much clearer than the output of explain in the terminal and as a result I'm googling how to speed up sort now. Thanks.
Re:Postgres Query Plan Visualization
by orf on Sunday January 24, 2016 @09:26AM
(#10962565)
This looks great, much better than the pgadmin output! A good feature would be an CLI tool we could pipe output to, i.e `psql some_long_query | pev`, which would bring back a URL we could open.
Re:Postgres Query Plan Visualization
by dveeden2 on Sunday January 24, 2016 @06:24AM
(#10962220)
MySQL Workbench recently also got a visual explain option.
This is very useful, don't know why everyone keeps struggling with a text format (especially for large explain plans).
Re:Postgres Query Plan Visualization
by ris on Sunday January 24, 2016 @10:48AM
(#10962808)
This looks very nice.
Something I've wanted in a postgres query plan visualizer is a "timeline" view of the various nodes. Seeing as for each node we get a "start time" and "duration" it seems like it should be possible to draw something a little like a flame graph to see at what points the nodes are doing their work.
Re:Postgres Query Plan Visualization
by atatiyan on Sunday January 24, 2016 @12:32PM
(#10963168)
Hi, I created Pev. This should be possible to do since you can graph durations and they're even color coded (red being the slowest and green being the fastest)
Re:Postgres Query Plan Visualization
by barrkel on Sunday January 24, 2016 @11:06AM
(#10962860)
The output from postgres explain is already in a very readable format when you compare it with the mishmash from mysql. A nice tree view all adding up. If you're aware of how evaluating a SQL query actually works, it's very straightforward.
MySQL, meanwhile, just gives you a list of things, with random lists of indexes and keywords (like temporary, filesort, derived, etc), and estimates of row counts. Piecing together the data flow back into a tree format that maps to the syntax tree of your SQL query is not so easy.
As an additional information about already existing execution plan visualisation tools:
Depesz has written the classical PostgreSQL Execution Plan Visualiser years ago.
http://explain.depesz.com/
Of cause it is not so nicely pretty as the one from Tatiyants, but I use it now and then and it became a standard explain visualisation tool for many PostgreSQL users.
The table format from http://explain.depesz.com/ is very useful and one can understand a lot of details about your execution plan without the need to visualise in the form of a graph.
Also the default execution plan visualiser in pgAdmin looks really cool.
The demo at http://tatiyants.com/pev/#/plans works.
Unfortunately it has some glitches.
Hints for using it:
* In psql use \o plan.txt to redirect the output of explain to a file. It will be a long output because you must use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) and copying it from the terminal won't be fun.
* Remove the first two lines (header) and the last two (footer) leaving only the json data. Remove all the + characters at the end of every line. Suggestion to the author: the tool should handle that.
That said, it works. It really is much clearer than the output of explain in the terminal and as a result I'm googling how to speed up sort now. Thanks.
For some reason I just cannot parse Postgres' query planner output, so this might help me understand EXPLAINs for the first time. Thanks for sharing!
https://www.mysql.com/common/images/products/mysql_wb_visual...
Something I've wanted in a postgres query plan visualizer is a "timeline" view of the various nodes. Seeing as for each node we get a "start time" and "duration" it seems like it should be possible to draw something a little like a flame graph to see at what points the nodes are doing their work.
MySQL, meanwhile, just gives you a list of things, with random lists of indexes and keywords (like temporary, filesort, derived, etc), and estimates of row counts. Piecing together the data flow back into a tree format that maps to the syntax tree of your SQL query is not so easy.
Nowadays I just avoid it by using NoSql (which to be fair gives me a whole different set of problems). :)