8 ms·
Pev: Postgres ‘Explain’ Visualizer (2016)
- edoceo 9y agoRad
- emilsedgh 9y agoAbsolutely fantastic! Thank You!
- beefsack 9y agoI 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.
- linux2647 9y agoI would imagine that as long as you could get a JSON representation of the plan, Pev could consume it. Edit: looks like MySQL can output it as JSON: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
- johannes1234321 9y agoNot 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-explain.html https://dev.mysql.com/doc/workbench/en/wb-performance-explai...
- isatty 9y agoThank you, this is very useful!
- fnord123 9y agoLooks good, but why not dump is as a flame graph?
- thechao 9y agoI'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.
- felixge 9y agoDo you have an example of "traditional (inverted-)back-trace"?
- fnord123 9y agoI think thechao means the stuff you get from perf where it looks a bit like the output of pstack. But I'd like a clarification too.
- thechao 9y agoThe tooling I use records backtraces at some sampling frequency. The obvious sorting is from the root call site (_start), down. However, while that sorting shows the worst offending single backtrace, it can be a bit impractical once the top offenders (in terms of perf) have been fixed. Instead, I like to see an inverted tree: reverse every backtrace then create a tree. This method tends to show systemic issues.
- fnord123 9y ago
- edraferi 9y agoVery 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.
- garysieling 9y agoDoes 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 9y agoI’ve used this before; data is only stored locally. Alternatively, you could run your own copy of the service using the NPM package.
- garysieling 9y agoThanks!
- deleted 9y ago[deleted]
- deleted 9y ago[deleted]
- StavrosK 9y agoHere's a version that runs without a server, entirely on IPFS, for your peace of mind, and also because lately I've been IPFSing everything: https://www.eternum.io/ipfs/QmQ1Qh9moFgV4MikubnXzAmV3rD48JPGSk2BBkUkHXCvM2 https://www.eternum.io/ipfs/QmQ1Qh9moFgV4MikubnXzAmV3rD48JPG...
- sghall 9y agoCool 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.
- dlb_ 9y agoVery nice! I wonder if it would be possible to embed that into pgAdmin? Possibly with Electron?
- j_s 9y agoThe 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 9y agoThe 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 9y agoThanks 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.
- lfittl 9y agoThanks for the shout-out - I'm the author of the base library you listed that can be used to work with the Postgres parser (libpg_query). That said, the tool being discussed here (pev) is used to interpret EXPLAIN plans, which libpg_query doesn't give you, as its only concerned with the parsing stage, not the planning stage. In order to plan a query you'll need more information from the actual database, including table statistics, config settings and the size of the underlying table :)
- maxvu 9y agoWhy, in the example, does the constituent `customerid` join take longer than the forming `orderid` one?
- icebraining 9y agoYou'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.
- mistercow 9y agoI 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()
- atatiyan 9y agocreator of pev here, thanks for all the kind words!
- mooneater 9y agoThis is beautiful. Can I ask, do you use a library to handle laying out the visual network, or is that using custom JS?
- atatiyan 9y agoI don't use JS for layout, it's pretty much all custom CSS.
- swrobel 9y agoStill being developed/maintained? Last push in Oct...
- leohart 9y agoI am trying to look for the logic to layout the plan on a 2D plane so that the boxes don't collide with each other but so far no luck. Can you give me a pointer please? The sample JSON of the plan doesn't indicate how big the boxes would be when presented on the UI. And also how to draw the connections between them.
- obiwahn 9y agoLooks awesome! How about adding a direction to your graph for people just starting with SQL.
- hotdogknight 9y agoI 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 9y agoThat's awesome! Does something like this exist for MySql?
- kakwa_ 9y agoIIRC, it's already present in mysql workbench. https://dev.mysql.com/doc/workbench/en/images/wb-new-visual-explain-52.png https://dev.mysql.com/doc/workbench/en/images/wb-new-visual-...
- TekMol 9y agoI mean something that runs on the command line. Preferable a small open source script like the one hotdogknight wrote.
- mrmondo 9y agoThat’s fantastic and no JavaScript! Thank you so much for your time / effort on this!
- binocarlos 9y agoThats a fantastic tool - there is scope for running it for queries on CI. A bit like a code coverage tool but for db query performance.
- sgt 9y agoThis 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.
- ris 9y agoSomething 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.
- stuaxo 9y agoThis is great, should really be a part of pgadmin4.
- rusht 9y agopgadmin4'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