After a recent stint in query optimization, I once again found myself wanting a better way to view query plans produced by EXPLAIN. So, I finally decided to do something about it and the result is Postgres EXPLAIN Visualizer (or Pev):

Pev plan

Why Pev

I wanted a tool that can make plans simple to understand and be visually pleasing. More specifically, I wanted:

Let’s see how Pev helps with these. I’ll use the plan produced by the query below for illustration (you can run this query against the dellstore2 database):

SELECT C.STATE,SUM(O.NETAMOUNT), SUM(O.TOTALAMOUNT)
FROM CUSTOMERS C
  INNER JOIN CUST_HIST CH ON C.CUSTOMERID = CH.CUSTOMERID
  INNER JOIN ORDERS O ON CH.ORDERID = O.ORDERID
GROUP BY C.STATE
  LIMIT 10 OFFSET 1

I should also note that Pev only works with EXPLAIN Plans in JSON format. To produce one, use this code:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

Node Visualization

First off, Pev uses a classic tree graph to visualize the plan. I find this to be easier to view than the left-to-right tree used by PgAdmin:

pgAdmin query plan

By default, each node displays its type + relevant details (like the object being scanned or the join condition), duration, and key insights (like whether this node is some type of outlier):

pev default node view

Speaking of insights, Pev currently calculates the following:
– outlier nodes (largest, slowest, costliest)
– nodes with bad planner estimates (planner missed by a factor of 100 or more)

Pev also allows for various customizations, like showing planner estimate details and a graph of either rows, duration, or cost:

Pev node expanded view

If you want to see absolutely everything Postgres knows about the node, just click on the title to get the extended view:

Pev expanded view

Using these customizations (available in the settings menu on the left), you can easily create graphs like the one below which shows how fast each node is:

pev node speed tree

Query Display

I personally find it hard to mentally map the plan I’m seeing to the query that generated it. Pev helps in this regard by showing you the query right next to your node and highlighting the relevant part wherever possible. Just click the little blue database icon inside the node:

Pev highlighted query

I must admit that highlighting the relevant part of the query is quite rudimentary at this point, but I’m hopeful that it can be improved in the future.

Two more things

Pev is heavily influenced by the excellent explain.depesz.com. I learned a lot about how Postgres planner works from using it and reading the help.

If you do use Pev, please let me know how you like it at @alexTatiyants. If you want to make it better, the code is on github.

You may also like:

Did you love / hate / were unmoved by this post?
Then show your support / disgust / indifference by following me on Twitter!

This post got 19 comments so far. Care to add yours?

  1. Nikolay says:

    Tried to use it, but got JS errors after clicking SUBMIT btn: https://www.dropbox.com/s/f20koihhmags6gn/Screenshot%202016-01-24%2011.16.10.png?dl=0

  2. Eugene says:

    JS error

    Uncaught EXCEPTION: Error during evaluation of “click”
    ORIGINAL EXCEPTION: SyntaxError: Unexpected token L
    ORIGINAL STACKTRACE:
    SyntaxError: Unexpected token L
    at Object.parse (native)
    at a.createPlan (http://tatiyants.com/pev/bundles/app.js:2:5722)
    at a.submitPlan (http://tatiyants.com/pev/bundles/app.js:2:8889)
    at e.ChangeDetector_a_0.handleEventInternal (eval at (http://tatiyants.com/pev/lib/angular2.min.js:1:0), :584:35)
    at e.handleEvent (http://tatiyants.com/pev/lib/angular2.min.js:6:21391)
    at e.dispatchEvent (http://tatiyants.com/pev/lib/angular2.min.js:7:6036)
    at e.dispatchRenderEvent (http://tatiyants.com/pev/lib/angular2.min.js:7:5906)
    at t.dispatchRenderEvent (http://tatiyants.com/pev/lib/angular2.min.js:5:30935)
    at o (http://tatiyants.com/pev/lib/angular2.min.js:7:14521)
    at http://tatiyants.com/pev/lib/angular2.min.js:7:14856
    ERROR CONTEXT:
    [object Object]

  3. Robins says:

    Pasted SQL / EXPLAIN and name… click SUBMIT….nothing happened. Ctrl-F5 and repeat.. nothing happens…!

    • Alex Tatiyants says:

      can you please submit this on gitHub along with the JSON / sql you were using? thanks

  4. […] Postgres Explain Visualizer (Pev) is a tool I wrote to make EXPLAIN output easier to grok. It creates a graphical representation of the query plan.  […]

  5. […] POSTGRES QUERY PLAN VISUALIZATION […]

  6. […] Postgres Query Plan Visualization […]

  7. C Wiles says:

    Hello,

    My organization is not comfortable with us utilizing your hosted version of this (even though I have tried and failed to explain that it only stores data locally).

    I am not fairly knowledgeable with web development, etc, but would like to host a local version of this tool. Would anyone be able to provide some concise instructions for setting this up to be hosted on a windows-based VM?

    Thanks,

    ~CW

    • Alex Tatiyants says:

      I’m sorry to hear about your predicament. To use Pev locally, you need to build it with npm (instructions are on my (gitHub page). As far as setting up a Node VM on windows, here’s one tutorial.