dev-resources.site
for different kinds of informations.
Bringing PostgreSQL Query Issues to Light with Insightful Visuals
As we keep sharing more features of our PostgreSQL query analysis service Saby Explain, today we'll give you a quick overview of how to spot issues in large and complex query plans by just taking a quick look at their visualization.
You’ll see that our visualization tools may really come in handy for this. To analyze your plan click DEMO on the Saby page.
Simplified visualization
Reading a query plan as plain text can be quite challenging, even if it's an easy one:
That’s exactly why our developers normally deal with a simplified version of the plan. It shows all the key data on execution time and buffers used for each node separately, making it easier to pinpoint extreme values:
Pie chart
Just figuring out the most critical issues can sometimes be a real hassle, especially with so many nodes to look through and a simplified visualization that spans more than two screens.
In this case, you can hardly do without a pie chart.
Glance over the chart to quickly assess the approximate share of resource usage for each node. When hovering over a specific sector, an icon appears to help you locate the corresponding node in the plan’s text.
Tile view
The pie chart doesn’t give us the whole story. If you need to see the relationship between nodes or identify critical issues, you’d better use a tile visualization.
Processing flow diagram
But neither of the visualization options above shows the full hierarchy of nested CTE, InitPlan, and SubPlan nodes. If you want to get the whole picture, take a look at the diagram:
More metrics? Say no more!
If you run the query execution plan using the EXPLAIN (ANALYZE) command, you'll only see the time spent. But we need more than just that to come to an accurate conclusion.
Here’s the thing: when you run a query on a cold cache, the time you actually see includes the time it takes to fetch data from storage, not just the time spent executing the query itself (even though it might not be obvious).
Building on that, here are a couple of tips to keep in mind:
- Use the
EXPLAIN (ANALYZE, BUFFERS)command only to see how many data pages are being read. This metric doesn’t depend much on the server workload, so you can use it as a reliable benchmark for query optimization. - To measure the time spent on disk operations, use
track_io_timing. And since your execution plan shows not just the time spent but also things like buffers and I/O timings, you can easily get to the analysis of all these metrics from any type of diagram. This can sometimes reveal the unpleasant truth that more than half of all the reads go to just one problematic node:
More articles if you're interested in the topic:
Featured ones: