
Postgres Query Plan Visualization - areski
http://tatiyants.com/postgres-query-plan-visualization/
======
valgog
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/](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/](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.

~~~
atatiyan
Thank you. Pev was definitely influenced by explain.depesz, it's a great tool

------
pmontra
That page yields a 504 Gateway Time-out right now. Cached at
[http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R...](http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R0E1cJ:tatiyants.com/postgres-
query-plan-visualization/+&cd=2&hl=en&ct=clnk&gl=us)

The demo at
[http://tatiyants.com/pev/#/plans](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.

~~~
colanderman

        \a
        \t
    

is what you want. (\a, unaligned mode, removes the + characters, and \t,
tuples only, removes the header and footer.)

~~~
pmontra
Thanks

------
janfoeh
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!

~~~
pmontra
See the hints in my other comment.

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

------
dveeden2
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...](https://www.mysql.com/common/images/products/mysql_wb_visual_explain_linux.png)

------
morenoh149
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/...](https://github.com/morenoh149/postgresDBSamples/tree/master/dellstore2-normal-1.0)

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

~~~
atatiyan
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)

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

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

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

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

