explain.depesz.com

A tool for finding a real cause for slow queries.

Help

explain.depesz.com is tool for finding real cause for slow queries.

Generally, one would use 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 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 page which shows parsed, and nicely (well, at least nice for me :) colorized to put emphasis on important parts.

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

This graph uses 4 colours to mark important things:

Which color is used, is choosen based on which mode you will use: "Exclusive", "Inclusive" or "Rows X".

Exclusive

This is 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 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 - top node will have Inclusive time equal to total time of query.

Colors:

Rows X

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

For example - if planner estimated that 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: