Execution Plan Reading

In the film The Matrix, Neo learns to see through the cascading green symbols to perceive the underlying reality of the simulated world. For database engineers, reading execution plans is a similar skill—looking past the rows and tables of EXPLAIN output to perceive the actual flow of computation, the bottlenecks, the inefficiencies, and the opportunities.

An execution plan is not just output to glance at; it's a map of computational work. Every node in the plan represents operations consuming CPU cycles, reading storage pages, and allocating memory. Learning to read this map fluently is perhaps the single most valuable skill for SQL performance engineering.

Most developers look at EXPLAIN output and feel overwhelmed by unfamiliar terms and nested structures. By the end of this page, you'll have a systematic methodology for reading any execution plan—regardless of database vendor—with confidence and precision.

Every execution plan is fundamentally a tree data structure. This isn't just an implementation detail—it's the conceptual model you need to internalize for effective plan reading.

The Tree Analogy:

• Root Node: The topmost operation that produces the final result set sent to the client
• Intermediate Nodes: Operations that process and transform data from their children
• Leaf Nodes: The terminal data access operations (table scans, index lookups) that touch actual storage
• Edges: Represent the flow of rows from child operations to parent operations

Data flows upward through the tree. Leaf nodes fetch or generate rows, which propagate up through intermediate nodes that filter, join, sort, or aggregate them, until the root node produces the final output.

Execution Order vs. Display Order:

A critical insight: the display order in EXPLAIN output does NOT match execution order.

Visually, the root appears at the top. But execution proceeds bottom-up:

1. First, Seq Scan on customers c runs and feeds into Hash
2. Simultaneously (conceptually), Seq Scan on orders o runs with its filter
3. Then Hash Join combines results from both branches
4. Finally, Sort orders the output

The indentation level indicates the tree depth: more indented operations are children of less indented operations. The arrows (->) indicate the data flow direction (from children to parent).

The relationships between operations in an execution plan follow specific patterns. Understanding these patterns is essential for tracing how data moves through the plan.

Unary Operations (One Child): These operations take input from exactly one child and transform it:

• Filter — Removes rows not matching a condition
• Sort — Reorders rows by specified criteria
• Materialize — Caches intermediate results in memory
• Limit — Restricts number of output rows
• Aggregate — Combines multiple rows into summary rows
• Hash — Builds hash table for upcoming join

Binary Operations (Two Children): These operations combine data from two child operations:

• Nested Loop — For each row from outer child, scan inner child
• Hash Join — Build hash from one side, probe with other
• Merge Join — Merge two sorted inputs
• Union/Except/Intersect — Set operations combining two inputs

N-ary Operations (Multiple Children): Some operations (like Append in PostgreSQL) can have many children, typically for partitioned table access or UNION ALL operations.

Join Operations and Inner/Outer Terminology:

In join operations, outer and inner have specific meanings:

• Outer Table (Driving Table): The table that controls the loop or probe
• Inner Table (Lookup Table): The table accessed for each outer row

For Nested Loop joins:

• The outer table is typically scanned once
• The inner table is scanned repeatedly—once per outer row

For Hash Join operations:

• The Build side provides rows for hash table construction
• The Probe side scans afterward, looking up in the hash table

Understanding which table is in which role is crucial for understanding join performance. If a huge table becomes the inner table of a nested loop, you get quadratic behavior.

Once you understand the tree structure, the next skill is tracing how data flows through it. This means following the row counts and data transformations from leaves to root.

The Mental Execution Trace:

When reading a plan, mentally execute it:

1. Start at the deepest leaf nodes — How many rows does each table scan produce?
2. Apply filters and conditions — Which operations reduce row counts?
3. Trace through joins — How many rows emerge from each join?
4. Follow through to the root — What's the final row count?

Along this path, ask:

• Where do row counts explode (joins producing more rows than inputs)?
• Where do row counts collapse (filters removing most rows)?
• Which operations process the most rows?
• Which operations produce the most rows?

Key Metrics to Track at Each Node:

The Volume Formula:

Total work at a node ≈ rows × loops × cost_per_row

Even a cheap operation becomes expensive if looped 100,000 times. Even an inexpensive row becomes work if there are 100 million of them. Always consider the multiplicative effect.

Execution plans include both cost estimates and (with ANALYZE) actual timing. Understanding how to interpret these metrics correctly is essential for effective performance analysis.

Understanding Cost:

Cost in execution plans is measured in arbitrary units calibrated to sequential page reads. Key points:

• Cost is NOT time—it's a relative measure for comparing operations and plans
• The optimizer uses cost to choose between alternative plans
• Cost has two components: startup cost and total cost
• Format: (cost=startup..total rows=N width=W)

Startup Cost: Work done before the first row is produced Total Cost: Work done to produce all rows

For most operations, total cost is what matters. But for operations like Sort or Hash, startup cost is significant because all input must be processed before any output is available.

Interpreting Actual Time:

The actual time shown in EXPLAIN ANALYZE can be confusing:

• Times are shown as time=startup..total
• These times are cumulative, including child node times
• For loops > 1, times are per loop (multiply by loops for total)

Critical Pattern: Identifying Where Time Is Spent:

Parent  (actual time=0.500..100.000 rows=1000)
  ->  Child  (actual time=0.100..98.000 rows=10000)

Here, the parent's time (100ms) includes the child's time (98ms). The parent itself only added 2ms. The child is the bottleneck.

To find where time is actually consumed: Parent-only time = Parent total - sum(Children totals)

With EXPLAIN (ANALYZE, BUFFERS), you get detailed information about memory buffer usage and I/O operations. This is crucial for understanding whether performance problems are CPU-bound or I/O-bound.

PostgreSQL Buffer Statistics:

Diagnosing I/O Problems:

High shared read relative to shared hit:

• Cold cache or dataset doesn't fit in memory
• Consider increasing shared_buffers or adding indexes to reduce scanned pages

Any temp read/written presence:

• Sorts or hash operations spilling to disk
• Increase work_mem to allow more memory per operation
• Alternatively, add indexes to avoid sorts

High buffer counts on small row counts:

• Inefficient access pattern (random I/O)
• Consider index covering columns or clustering table

The Cache Hit Ratio:

Cache hit ratio = shared hit / (shared hit + shared read)

For OLTP workloads, aim for >95% hit ratio. For analytics or cold queries, lower ratios may be acceptable.

Rather than randomly scanning execution plans, use a systematic methodology to quickly identify issues:

The 6-Step Plan Reading Process:

Text-based EXPLAIN output can be challenging to read for complex queries with many joins and subqueries. Development teams often use visual plan analysis tools:

Popular Plan Visualization Tools:

• pgAdmin / pgExplain (PostgreSQL) — Built-in graphical explain
• EXPLAIN.depesz.com (PostgreSQL) — Paste text plans, get visual analysis
• MySQL Workbench (MySQL) — Visual Explain diagram
• SQL Server Management Studio (SQL Server) — Graphical execution plans
• Oracle SQL Developer (Oracle) — Plan visualization with statistics

What Visual Tools Reveal:

Good visualization tools provide:

• Flow diagrams showing data movement
• Color coding for expensive operations (red = slow)
• Tooltips with detailed node information
• Row count comparison (estimated vs. actual)
• Highlighting of warnings and anomalies

Reading Text Output Efficiently:

Even when visual tools aren't available, you can improve text plan readability:

1. Use COSTS OFF initially to focus on structure without noise
2. Pipe to less or grep for searching large plans
3. Look for patterns: repeated operations, high loop counts, scans on large tables
4. Create mental markers: note join nodes, aggregate nodes as anchor points
5. Work in sections: analyze each subtree independently for complex queries

JSON Format for Automation:

EXPLAIN (FORMAT JSON, ANALYZE) SELECT ...;

JSON output is ideal for:

• Storing plans for historical comparison
• Automated performance regression testing
• Custom visualization or analysis tools
• Integration with monitoring systems