With the naked eye: at a glance about PostgreSQL query problems

We continue to open for public access a new feature of our service for analyzing query execution plans in PostgreSQL explain.tensor.ru… Today we will learn to identify sore spots offhand in large and complex plans, just glancing at them with an armed eye …

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:

Pie chart

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:

Execution diagram

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:

  • Use 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.
  • Use track_io_timingto understand exactly how long it took work with the carrier

And if your plan contains not only time, but also buffers or 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

Similar Posts

Leave a Reply