BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 936 of 1,9538. PostgreSQL InternalsFree lesson

Explain Plan Visualization and Metrics

You will interpret EXPLAIN ANALYZE output to locate bottlenecks in execution trees.

In this lesson, you will learn how to identify execution bottlenecks by mapping EXPLAIN ANALYZE metrics directly onto the hierarchical structure of a query plan.

Visualizing the Tree

When you run EXPLAIN (ANALYZE, BUFFERS) query;, PostgreSQL returns a tree structure. Think of this tree as a pipeline: data flows from the leaves (scans) up to the root (the final result). Because the planner is recursive, the "cost" and "time" of a parent node include the time spent by its children. To find a bottleneck, you must look for the node where the gap between its own execution time and its children's execution time is the largest.

Analyzing the Metrics

When inspecting the output, focus on these three indicators:

  1. Actual Time: Look for the actual time field. PostgreSQL shows this as (start_time..end_time). The actual time is the time elapsed in milliseconds. If a node shows (0.05..150.00), it took 150ms to finish.
  2. Rows vs. Loops: If a node has loops > 1 (often in Nested Loops), the actual time is the time for one iteration. Multiply the actual time by loops to get the total cumulative time spent at that node.
  3. Buffers: The Buffers metric is vital. If a node shows high shared read counts, it means the query is hitting the disk instead of RAM. This is the most common cause of "sluggish" plans.

Identifying the Bottleneck

Consider this snippet:

->  Seq Scan on orders (cost=0.00..450.00 rows=1000 width=16) (actual time=0.02..85.00 rows=1000)
      Buffers: shared read=500

Here, the Seq Scan is a bottleneck because it took 85ms and performed 500 disk reads. If this node is deep inside a nested loop, that 85ms cost is being multiplied by the number of loops, causing the query to explode in duration. When you see a node where rows expected significantly deviates from rows actual, your statistics (histograms) are likely stale, leading the planner to choose an inefficient algorithm like a Nested Loop instead of a Hash Join.

Key Takeaway: To find a bottleneck, locate the node where the cumulative actual time is highest and check if it is being driven by excessive disk I/O (Buffers) or a mismatch between estimated and actual row counts.