7 ms·

Postgres Query Plan Visualization

251 points by areski 11y ago

janfoeh 11y ago
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 11y ago
  See the hints in my other comment.
pmontra 11y ago
That page yields a 504 Gateway Time-out right now. Cached at http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R0E1cJ:tatiyants.com/postgres-query-plan-visualization/+&cd=2&hl=en&ct=clnk&gl=us http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R... 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 11y ago
  \a \t is what you want. (\a, unaligned mode, removes the + characters, and \t, tuples only, removes the header and footer.)
  - pmontra 11y ago
    Thanks
valgog 11y ago
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 11y ago
  Thank you. Pev was definitely influenced by explain.depesz, it's a great tool
dveeden2 11y ago
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_explain_linux.png https://www.mysql.com/common/images/products/mysql_wb_visual...
andrewvc 11y ago
Awesome! I used this the other day and it is great. The site seems to be down, I recommend bookmarking it and coming back!
orf 11y ago
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.
ris 11y ago
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 11y ago
  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 11y ago
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.
banku_brougham 11y ago
Thank you!!!! This will make my life easier and more fun.
morenoh149 11y ago
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/dellstore2-normal-1.0 https://github.com/morenoh149/postgresDBSamples/tree/master/...
jedberg 11y ago
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). :)

251 points by areski 11y ago

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 11y ago
See the hints in my other comment.

pmontra 11y ago

That page yields a 504 Gateway Time-out right now. Cached at http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R0E1cJ:tatiyants.com/postgres-query-plan-visualization/+&cd=2&hl=en&ct=clnk&gl=us http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R... 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 11y ago
\a \t is what you want. (\a, unaligned mode, removes the + characters, and \t, tuples only, removes the header and footer.)
- pmontra 11y ago
  Thanks

valgog 11y ago

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 11y ago
Thank you. Pev was definitely influenced by explain.depesz, it's a great tool

dveeden2 11y ago

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_explain_linux.png https://www.mysql.com/common/images/products/mysql_wb_visual...

andrewvc 11y ago

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

orf 11y ago

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.

ris 11y ago

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 11y ago
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 11y ago

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.

banku_brougham 11y ago

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

morenoh149 11y ago

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

jedberg 11y ago

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