
Pev: Postgres ‘Explain’ Visualizer (2016) - insulanian
http://tatiyants.com/postgres-query-plan-visualization/
======
hotdogknight
I needed a version that ran on the command line so I made one here:
[https://github.com/simon-engledew/gocmdpev](https://github.com/simon-
engledew/gocmdpev)

~~~
TekMol
That's awesome! Does something like this exist for MySql?

~~~
kakwa_
IIRC, it's already present in mysql workbench.

[https://dev.mysql.com/doc/workbench/en/images/wb-new-
visual-...](https://dev.mysql.com/doc/workbench/en/images/wb-new-visual-
explain-52.png)

~~~
TekMol
I mean something that runs on the command line. Preferable a small open source
script like the one hotdogknight wrote.

------
atatiyan
creator of pev here, thanks for all the kind words!

~~~
mooneater
This is beautiful.

Can I ask, do you use a library to handle laying out the visual network, or is
that using custom JS?

~~~
atatiyan
I don't use JS for layout, it's pretty much all custom CSS.

------
garysieling
Does this store the plans? I like these things, but I'm always a little leery
that this will expose my database schema in Google search results.

~~~
matthewmacleod
I’ve used this before; data is only stored locally. Alternatively, you could
run your own copy of the service using the NPM package.

~~~
garysieling
Thanks!

------
sgt
This is really great and I think I might start using this. I would also love a
standalone version of this that runs outside the browser. Something that can
maybe connect directly to my DB.

------
mistercow
I use this tool often, and it's great. It's a lot easier to wrap your head
around plans when the way it displays them.

The one thing I wish it had is either the ability to not save plans
automatically, or at least a button to clear the history. As it is, I just
pull up a console from time to time and do localStorage.clear()

------
fnord123
Looks good, but why not dump is as a flame graph?

~~~
thechao
I'm not sure why you're being downvoted without comment: that sucks. I think
the fact this project _can_ show per-node costs of the plan is interesting---
and certainly a killer feature---but I think it's only part of the story. I
think the structure is just as important.

Unfortunately, flame graphs don't show (much) structure.

Now, as a second note, I do a _lot_ of performance work (systems programming
in stacks starting at hand-assembled machine code, working all the way up to
scripting languages). Personally, I find flame graphs not as useful as a
traditional (inverted-)back-trace. I think the _structure_ encoded in a
traditional (inverted-)back-trace tells you a lot about poorly designed
algorithms and bad systems interactions in a way that flame graphs (mostly)
erase.

~~~
fnord123
I'm not sure how you mean flame graphs don't show much structure. They appear
to show a lot of structure so I'm surprised by the criticism. I haven't used
them as much as I would have hoped; but found them useful. Otherwise I
normally see @brandangregg banging on about how good they are so could you
elaborate more on your criticisms?

Thanks

~~~
cormacrelf
Flame graphs are based on high frequency linear sampling of a (at least
conceptually) single thread of execution, where each sample records the stack.

SQL query execution does not follow this structure. Query plans are executed
in whatever order or degree of parallelism is appropriate. And linearising an
SQL query plan is actually wrong. If you've ever watched SQL Server's Live
Query Statistics play out, you'll know that the engine does whatever work it
can when possible. It would be incorrect to think of the query execution as a
stack the CPU can get stuck deep down in, and a flame graph would create an
incorrect model.

As an exercise, where would you put self-time in a flame graph representation
of an execution plan for a filtering node, or a sorting node? The engine
doesn't report that, because filters happen inline (at least in batches) and
sorts are optimised to happen progressively and can be scheduled in between IO
handlers. Ask an expert about this, I'm not your guy.

Also, the data you want to see isn't represented by one dimension on the flame
graph (how wide is this function call?), it is represented by many more: cost,
CPU time, number of rows, IO operations, and estimations of all the above. The
execution plan needs to be roomier, and include all this information, not
merely wall clock time.

~~~
cormacrelf
I should add, in my experience optimising queries, a time breakdown is
generally the first step of many. Flamegraphs are great at telling you where
in 1,000 invocations your time is spent, but your execution plan only has ~20
nodes max, and the percentages do that job well enough. At that point, you
want to know why it's slow, and a flamegraph representation of those
percentages won't help you with that.

------
obiwahn
Looks awesome! How about adding a direction to your graph for people just
starting with SQL.

------
sghall
Cool project. Not a DBA but was interested in playing around with this. Be
great to maybe add some example plans here:
[http://tatiyants.com/pev/#/plans](http://tatiyants.com/pev/#/plans)

So if you just want to checkout the interface you can click to load up an
example or two.

~~~
luhn
It already has that feature!

Hit "New Plan", and then in the top right there's a link that says "Create
Sample Plan"

~~~
sghall
Ahh, nice! Missed that. Maybe make it more prominent ;)

------
beefsack
I can completely see myself using this on my Postgres projects, but something
like this would be most useful for me at work.

How feasible would it be to port this over to MySQL / MariaDB? I know EXPLAIN
output on MySQL is much simpler than what you get out of Postgres so my gut
feeling would be that it wouldn't be possible.

~~~
johannes1234321
Not exactly the same, but if you run MySQL Workbench as GUI there's a visual
explain feature built around MySQL's JSON explain:
[https://dev.mysql.com/doc/workbench/en/wb-performance-
explai...](https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html)

------
ris
Something I've wanted from an explain viewer for a long time is simply using
the "start time" "end time" information on the nodes to put things in a basic
timeline. Most visualisers seem determined to keep the layout as a pimped up
version of the tree given to them.

------
maxvu
Why, in the example, does the constituent `customerid` join take longer than
the forming `orderid` one?

~~~
icebraining
You'd probably have to expand each step to see the specifics, but a simple
possibility is that many customers don't have orders, and hence the first join
filters out a bunch of them.

------
edraferi
Very cool! Now I want to figure out the Postgres EXPLAIN JSON format and start
parsing other DBs to fit, just so I can use this tool on them.

------
stuaxo
This is great, should really be a part of pgadmin4.

~~~
rusht
pgadmin4's Query Tool [0] has a similar analyzer.

[0]
[https://www.pgadmin.org/docs/pgadmin4/1.x/query_tool.html](https://www.pgadmin.org/docs/pgadmin4/1.x/query_tool.html)

------
dlb_
Very nice! I wonder if it would be possible to embed that into pgAdmin?
Possibly with Electron?

------
emilsedgh
Absolutely fantastic! Thank You!

------
isatty
Thank you, this is very useful!

------
j_s
The PostgreSQL parser is available as a C library.

C -
[https://github.com/lfittl/libpg_query](https://github.com/lfittl/libpg_query)

Ruby -
[https://github.com/lfittl/pg_query](https://github.com/lfittl/pg_query)

Go -
[https://github.com/lfittl/pg_query_go](https://github.com/lfittl/pg_query_go)

Node - [https://github.com/zhm/pg-query-parser](https://github.com/zhm/pg-
query-parser)

Python -
[https://github.com/alculquicondor/psqlparse](https://github.com/alculquicondor/psqlparse)

~~~
saurik
The output of explain is not "parse this SQL statement and show me what it
means" but "given this SQL statement, how will it be executed on this database
(which is affected by the table's stored statistics and takes into account any
indexes)". I am thereby not sure how these query parsing libraries are
relevant.

~~~
j_s
Thanks for taking the time to comment (?).

Never-before-on-the-HN-front-page tools for working with PostgreSQL queries
seemed relevant to me; this info may help a few others.

------
edoceo
Rad

