Postgres Query Plan Visualization

Wow, wonderful work!

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.

That page yields a 504 Gateway Time-out right now. Cached at http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R...

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.

It seems to be getting hammered in the moment, so I can't give it a spin right now, but just from your explanation this looks fantastic.

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!

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.

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).

https://www.mysql.com/common/images/products/mysql_wb_visual...

for those whole couldn't get the anonymous cvs access working. I put the sample db on github https://github.com/morenoh149/postgresDBSamples/tree/master/...

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.

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.

Awesome! I used this the other day and it is great. The site seems to be down, I recommend bookmarking it and coming back!

Thank you!!!! This will make my life easier and more fun.

I wish I had this five years ago when I was dealing with Postgres query planning every day!

Nowadays I just avoid it by using NoSql (which to be fair gives me a whole different set of problems). :)