The site explain.depesz.com is a wonderful tool for analyzing Postgres query plans. It takes the ordinary output of EXPLAIN or EXPLAIN ANALYZE and will pull out the relevant information from the query plan. Here’s what a query from the last post looks like in explain.despez.com.
In addition to the numbers contained directly in the query plan, explain.despez.com includes some additional derived numbers. The most important of these numbers is the exclusive time, which is the time spent in a node excluding the time spent in child nodes. Ordinary EXPLAIN ANALYZE only displays the inclusive time, which is the time spent in a node including the node’s children. To get the exclusive time from ordinary EXPLAIN ANALYZE, you have to take the inclusive time for a node and subtract the inclusive time for each of that’s nodes direct child nodes. Why do that when you can have explain.despez.com tell you the exclusive times for each node for you?
Additionally, you can click on the button labeled “exclusive” in the top left of the query in order to have explain.despez.com highlight the nodes with the highest exclusive time. Personally, whenever I come across an extremely large Postgres plan, I’ll typically copy and paste it into explain.despez.com and have it highlight the nodes with the highest exclusive time. From there, I can visually pick out where all of the time is spent. I find it much better than trying to parse the inclusive time from every node and trying to figure out where all of the time spent.