Postgres Query Plan Visualization

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

Why Pev

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

• minimal visual noise
• insights
• high degree of customization
• plan in context of the query

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:

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

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:

If you want to see absolutely everything Postgres knows about the node, just click on the title to get the extended 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:

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:

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:

• How To Navigate JSON Trees in Postgres using Recursive CTEs
• How and When to Use Various GORM Querying Options
• XML Databases
• Good Devs Don’t Like Magic
• Database Source Control Revisited

• database
• pev
• Postgres

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