Postgres Query Plan Visualisation - sasha-alias/sqltabs GitHub Wiki

One day I accidentally discovered that Postgresql returns a command status EXPLAIN for queries starting with EXPLAIN and EXPLAIN ANALYZE. This basically makes possible reliably distinguish EXPLAIN queries from all other types of queries. Why not to interpret the EXPLAIN resultset a bit differently then?

Normally Postgres returns a single column dataset where each record represents some kind of step or details about query execution plan. Records there formatted a special way so they form a tree with a single root and many branches and leafs. Depending if it was just a plain EXPLAIN query or EXPLAIN ANALYZE there is a cost or execution time information present in the records:

QUERY PLAN
----------------------------------------------------
Nested Loop  (cost=1.15..2.69 rows=1 width=345)
  ->  Hash Join  (cost=1.02..2.41 rows=1 width=281)
        Hash Cond: (s.usesysid = u.oid)
        ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=217)
        ->  Hash  (cost=1.01..1.01 rows=1 width=68)
              ->  Seq Scan on pg_authid u  (cost=0.00..1.01 rows=1 width=68)
  ->  Index Scan using pg_database_oid_index on pg_database d  (cost=0.13..0.27 rows=1 width=68)
        Index Cond: (oid = s.datid)

When working as a DBA or Database Developer you may debug complicated query plan in order to find the problematic part of it. Normally what you need is to find the most expensive part of query plan, i.e. the node of the tree which has the highest cost or highest actual time field. The only complication here is that Postgres displays an inclusive cost/time for each execution plan record. You remember, execution plan is a tree. Inclusive cost/time means that each record displays the value which is a sum of all child records. So for better understanding it's better to know an exclusive cost/time. I.e. how is expensive a particular plan step without subplan records.

So this is how SQL Tabs solves the issue. When you execute an EXPLAIN or EXPLAIN ANALYZE query it detects from Postgresql response that it's a query plan and parses it. Then it assigns to each plan record two weight values: inclusive weight and exclusive weight. For plain EXPLAIN query a weight is taken form cost field and for EXPLAIN ANALYZE the weight is taken from actual time field. The weight is calculated as a percentage of total cost/time. Both weights are displayed as background colors for the plan records:

The dull red color is for inclusive cost/time and the bright red is for exclusive one.

So this way you can visually recognize the heaviest parts of the query from the first glance and at the same time see the text representation of query plan you probably already get used to. In case you want to reduce a visual noise created by a weight colors you can just click on the column header to switch it off.

Another common challenge in analyzing query execution plan is to understand a tree structure. It's just not always obvious which record is a parent of which child. Especially when a query plan is long. So SQL Tabs makes parent records collapsible on arrow icon click.

And for even better understanding of a tree structure it's possible to switch to a graphical tree view:

You may notice some nodes on the tree diagram are bigger than others. The radius of a node also depends on the exclusive cost/time weight. So it should be easier to detect the most expensive parts of queries on the diagramm as well. There are actually two types of trees with a bit different nodes arrangement. You can try and figure out which one is better for you.

There are few more features SQL Tabs provides you while working with queries plans.

It's not always the case you have an access to a database to execute the query. When you work as a consultant for example you may have only a text of a query plan. So for that purposes you can use explain.sqltabs.com. There you can just paste the query plan and get a visualised representation of it.

Also you can securely share a query plan on share.sqltabs.com right from SQL Tabs and send the link to your colleagues. Here is an example of shared execution plan.

I hope SQL Tabs will help you to understand your queries plans better.

If you like it please give SQL Tabs a star on github.

In case you have improvement request or bug report please file it here.