explain.depesz.com

PostgreSQL's explain analyze made readable

Help

explain.depesz.com is a tool for finding real causes for slow queries.

Generally, one would use the EXPLAIN ANALYZE query; and read the output. The problem is that not all parts of the output are easily understandable by anybody, and it's not always obvious whether a node that executes in 17.3ms is faster or slower than the one that runs in 100ms - given the fact that the first one is executed 7 times.

To use the site, simply go to first page and paste there explain analyze output from your psql.

After uploading, you will be directed to a page which shows parsed, and nicely (well, at least nice for me :) colorized output, to emphasize important parts.

The url for colorized output is persistent, so you can easily show it to others. For example to those nice guys on the irc channel #postgresql on freenode.

This graph uses 4 colors to mark important things:

The color is chosen based on which mode you use: "Exclusive", "Inclusive" or "Rows X".

Exclusive

This is the total amount of time PostgreSQL spent evaluating this node, without time spent in its subnodes. If the node has been executed many times (for example because of a Nested Loop plan), this time will be correctly multiplied.

Colors:

Inclusive

This is just like Exclusive, but it doesn't exclude time of subnodes. So, by definition the top node will have Inclusive time equal to the total time of the query.

Colors:

Rows X

This value stores information about how big the planner's mistake was when it estimated the return row count.

For example, if planner estimated that a given node will return 230 rows, but it returned 14118 rows, the error is 14118/230 == 61.4.

It has to be noted that if the numbers were the other way around (estimated 14118, but really only 230), the Rows X would be the same. To show whether planner underestimated or overestimated - there is an arrow showing either ↓ - if planner underestimated rowcount, or ↑ if it overestimated.

Colors: