This will help us various visualization options:
Reduced text view
The original text of a rather simple plan is already causing problems in the analysis:
Therefore, we prefer the abbreviated form when key information is rendered left-right about the runtime and buffers used for each node, and it’s very easy to notice the highs:
But sometimes even just to understand “where it hurts the most” is not easy, especially if it contains several dozen nodes and even the shortened form of the plan takes 2-3 screens.
In this case, a regular pie chart will come to the rescue:
Immediately, offhand, an approximate share of resource consumption each of the nodes. When we hover over it, on the left in the text view, we will see an icon for the selected node.
Alas, piechart shows poorly relationship between different nodes and the “hottest” spots. For this, the “tile” option is much better suited:
But both of these options don’t show complete investment chain service nodes
CTE/InitPlain/SubPlan – it can be seen only on the real execution diagram:
More metrics needed!
If you shoot the plan of the actual execution of the query as
EXPLAIN (ANALYZE)you will see there just wasted time… But very often this is not enough for correct conclusions!
For example, executing a query on a “cold” cache, you will get (but you will not see!) The time of receiving data from the media, and not at all the work of the query itself.
Therefore, a couple of recommendations:
EXPLAIN (ANALYZE, BUFFERS)to see the volume of data pages being subtracted. This value is practically not subject to fluctuations on the load of the server itself and can be used as a metric for optimization.
track_io_timingto understand exactly how long it took work with the carrier…
And if your plan contains not only time, but also
i/o timings, then on each of the charts you can switch to the analysis mode for these metrics. Sometimes you can immediately see, for example, that more than half of all readings fell on a single problem node:
Previous related articles:
- Understanding PostgreSQL query plans even more conveniently
- Recipes for ailing SQL queries
- What EXPLAIN is silent about, and how to get him to talk