Have you ever wondered why a query runs slowly, or how the database decides which approach to use when fetching your data? PostgreSQL's EXPLAIN command answers these questions by revealing the execution plan — the step-by-step strategy the database uses to retrieve your results.

An execution plan is like a recipe that PostgreSQL follows to cook up your query results. Just as a chef might chop vegetables before sautéing them, the database performs operations in a specific order: scanning tables, filtering rows, joining data, and sorting results.

Understanding how to read an execution plan is crucial for SQL tuning. Plans are tree structures that execute from the deepest indentation outward, processing children before parents. You may hear this described as "bottom up, inside out" — where "bottom" refers to the leaves of the tree (most indented in text output), and "inside" refers to the innermost nested structures.

By the end of this guide, you'll be able to look at any execution plan and understand exactly what PostgreSQL is doing — and more importantly, spot opportunities to make your queries faster.

PostgreSQL provides the EXPLAIN command to show you how it plans to execute a query. Think of it as asking the database, "How would you approach this?" before actually doing the work.

There are two main ways to use EXPLAIN:

Let's explore both approaches.

The simplest form shows the planner's chosen execution strategy without running the query. This is useful when you want a quick look at the plan without waiting for the query to complete.

Output:

Let's break down what this output tells us:

EXPLAIN supports many options that control what information is displayed. You can combine multiple options by separating them with commas inside parentheses.

Here are the available options:

Example combining options:

While basic EXPLAIN shows estimates, EXPLAIN ANALYZE runs the query and shows what actually happened. This is invaluable when you suspect the planner's estimates are wrong.

Output:

Notice the new information compared to basic EXPLAIN:

⚠️ Warning: ANALYZE actually executes the query. For INSERT, UPDATE, or DELETE statements, always wrap in a transaction to prevent unintended changes:

This lets you see the execution plan without committing the changes.

When troubleshooting a problematic query, use this comprehensive combination to get the most detailed information:

This shows:

EXPLAIN can output plans in several formats. Choose based on how you'll use the output.

The default format is human-readable with indentation showing the tree structure. This is what you'll use most often when analyzing queries interactively.

JSON is ideal for programmatic analysis, visualization tools like explain.depesz.com or explain.dalibo.com, and storing plans for later comparison.

YAML is also machine-readable and slightly more compact than JSON:

XML format is available for integration with XML-based tools and workflows:

Looking at an execution plan can seem overwhelming at first — there are nested operations, cost numbers, and unfamiliar terms. Don't worry! Once you understand the structure, it becomes much clearer.

An execution plan is a tree structure. Just like a family tree, it has a root at the top and branches down to leaves at the bottom. The key insight is that data flows from the leaves up to the root:

When reading any execution plan, follow these three simple rules:

1. Start at the deepest indentation — Find the most indented lines; these are the leaf nodes where execution begins
2. Work your way up — Follow the data as it flows upward through parent operations to the root
3. For nodes at the same level — Read from top to bottom (the first child is the "outer" side, which drives execution)

Let's see how these rules apply to a real query. Consider this query joining three tables:

The execution plan might look like this (we've numbered the operations in execution order):

Notice how the Index Scan on customers (the most deeply indented operation) executes first, even though it appears in the middle of the text output. The indentation shows nesting depth, not execution order.

Here's the same plan shown as a tree diagram. Notice how data flows from the bottom (leaves) up to the top (root):

Step-by-step execution:

Here's exactly what happens when PostgreSQL runs this plan:

1. [1] Index Scan on customers — PostgreSQL finds customers where country = 'USA' using an index. This is where execution starts.
2. [2] Index Scan on orders — For each customer found in step 1, PostgreSQL looks up their orders using the customer_id index.
3. [3] Nested Loop (inner) — Combines each customer row with their matching order rows.
4. [4] Index Scan on order_items — For each customer+order combination from step 3, PostgreSQL finds the order items.
5. [5] Nested Loop (outer) — Combines everything and produces the final result rows.

When you see a join operation with two children, each child has a specific role. The terms "outer" and "inner" refer to how the join algorithm uses each input:

Different join types use their children differently:

In a Hash Join, PostgreSQL builds a hash table from one input, then probes it with the other:

How Hash Join executes:

1. Build phase (inner side first): Read all customers and build a hash table in memory
2. Probe phase (outer side): For each order, look up the matching customer in the hash table

In a Nested Loop, PostgreSQL iterates through the outer side and, for each row, searches the inner side:

How Nested Loop executes:

1. Get one row from the outer side (customers)
2. Search the inner side (orders) for all matching rows
3. Output the combined rows
4. Repeat steps 1-3 for each outer row

Real-world queries often involve multiple operations: joins, filters, aggregations, and sorting. Let's work through a more complex example to see how all the pieces fit together.

Here's a query that joins tables, filters, aggregates, and sorts:

Execution order explained:

Notice how the data "flows" upward: raw table data → joined data → aggregated data → sorted results.

When using EXPLAIN ANALYZE, you'll see loops=N for each operation. This tells you how many times that operation was executed. This is crucial for understanding true costs because the reported time and row counts are per execution.

In this example, the Index Scan on orders shows rows=50 loops=100. This means:

Calculating true totals:

One of the trickiest aspects of reading EXPLAIN output is understanding that costs are cumulative — each node's cost includes the costs of all its children. This means a high cost at a parent node doesn't necessarily mean that node is the problem; the issue might be in one of its children.

The key insight is that which child costs are included in a node's startup cost depends on when that node can start producing output. Some operations can begin producing output immediately (streaming), while others must wait for all input before producing anything (blocking).

Understanding which nodes are "blocking" (must wait for all input) versus "streaming" (can start immediately) helps you interpret costs correctly:

Sort is a classic blocking operation. It cannot output the smallest (or largest) value until it has seen every input row.

Breaking down the costs:

Notice that Sort's startup cost (5000) equals the Seq Scan's total cost. This makes sense: Sort cannot begin producing output until it has received all 100,000 rows from the scan.

The Sort's own work (comparing and sorting) adds only 100 cost units (5100 - 5000), but the startup cost reflects the waiting time.

Nested Loop is a streaming operation. It can output results as soon as it finds matches, without waiting for either input to complete.

Breaking down the costs:

The Nested Loop can produce its first output row as soon as:

1. The customers scan returns its first row (0.29)
2. The orders scan finds a match for that customer (0.21)

This is why Nested Loops often have low startup costs — they're excellent when you only need the first few results (like with LIMIT clauses).

Hash Join is interesting because it's asymmetric — one side (the build side) must complete before the other side begins.

The Hash Join startup cost (125.00) breaks down as:

This reflects reality: PostgreSQL must read all customers and build the hash table before it can look up even a single order. The probe side (orders) can then stream through, with each row quickly finding its match in the hash table.

Understanding cumulative costs helps you make better optimization decisions:

1. Find the real bottleneck

A high cost at the top doesn't mean the top node is slow — it might just be accumulating costs from expensive children. Always drill down to find where the cost actually originates.

2. Understand query responsiveness

Blocking operations (Sort, HashAggregate, Hash) mean the user waits for everything to finish before seeing any results. Streaming operations (Nested Loop, Limit) can show results quickly even if the total time is the same.

3. Optimize for your use case

Here's a handy reference for understanding the most common operations you'll see in execution plans:

For large tables and complex queries, PostgreSQL can use multiple CPU cores to speed up execution. When you see nodes like Gather or Parallel Seq Scan in your execution plan, the database is distributing work across multiple worker processes.

In a parallel query, PostgreSQL divides the work among:

The workers execute their portion of the plan, and the leader collects and combines their results.

Here's what a parallel query plan looks like:

The key to understanding parallel plans is the loops value:

Here, loops=3 means:

The timing shown (198.45 ms) is the average per process. Since they run in parallel, the wall-clock time is roughly the same as a single worker's time, not the sum.

With EXPLAIN ANALYZE, you can see detailed worker statistics:

If Workers Launched is less than Workers Planned, the system may be under resource pressure (other queries using workers, or hitting max_parallel_workers).

PostgreSQL considers parallel execution when:

1. Table size exceeds threshold: The table must be larger than min_parallel_table_scan_size (default 8MB)
2. Cost justifies it: The parallel plan must be cheaper than the serial alternative
3. Workers are available: Must not exceed max_parallel_workers_per_gather
4. Query type supports it: Not all operations can be parallelized

Operations that can be parallelized:

Operations that cannot be parallelized:

Aggregates use a two-phase approach in parallel plans:

Phase 1 (Partial): Each worker computes its own aggregate over its portion of data

Phase 2 (Finalize): The leader combines partial results:

Hash joins can also run in parallel:

In a Parallel Hash Join:

When parallel queries help:

When parallel queries may not help:

Now it's your turn! Look at this execution plan and try to trace the execution order before reading the answer.

Hint: Start at the deepest indentation (the Seq Scan on customers), then work your way up.

Congratulations! You've learned how to use PostgreSQL's EXPLAIN command to understand and optimize query performance. These skills are essential for maintaining efficient database applications.

1. How to generate execution plans using EXPLAIN and its various options
2. How to interpret cost estimates, row counts, and timing information
3. How to read execution plans from the inside out (deepest indentation first)
4. How to understand different node types including Seq Scan, Index Scan, and various join methods
5. How to identify blocking operations versus streaming operations
6. How to use the loops multiplier to calculate true execution totals