Definition
- EXPLAIN – Shows the execution plan the optimizer chose without running the query: which access method (Seq Scan vs Index Scan), join order, join algorithms, which indexes, and estimated row counts and cost.
- EXPLAIN ANALYZE – Actually executes the query and annotates each node with real row counts, real timings, and loop counts alongside the estimates.
- First red flag – A sequential (full-table) scan on a large table means the engine is reading every row instead of using an index.
- BUFFERS option –
EXPLAIN (ANALYZE, BUFFERS)shows shared buffer hits and disk reads, revealing whether the cost is CPU or I/O.
Imagine asking a bank clerk how they will find a transaction. EXPLAIN is the clerk describing their plan: 'I will flip through every page of the ledger' (Seq Scan) versus 'I will use the index to jump straight to it' (Index Scan). EXPLAIN ANALYZE is sending the clerk to actually do it and timing them, then comparing their guess to reality. If the clerk says they will flip through all ten million pages, that is the full-table scan you want to catch and fix with an index.
Understanding the Concepts
What does EXPLAIN show?
EXPLAIN prints the plan tree the optimizer selected without executing the query. Each node is an operation with estimated costs and row counts.
-- EXPLAIN shows the plan without running it
EXPLAIN
SELECT t.amount, t.created_at
FROM transactions t
WHERE t.account_id = 'SAV2001'
AND t.created_at >= '2024-01-01'
ORDER BY t.created_at DESC;
-- Output:
-- Sort (cost=150..155 rows=100 width=24)
-- Sort Key: created_at DESC
-- -> Index Scan using idx_txn_acct_date on transactions t
-- (cost=0.4..140 rows=100 width=24)
-- Index Cond: (account_id = 'SAV2001' AND created_at >= '2024-01-01')
Each node shows: estimated startup cost, total cost, estimated row count, and row width. These are predictions based on table statistics.
package com.bank.diagnostic;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class ExplainService {
private final DataSource dataSource;
public ExplainService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Show the plan without executing the query
public void showPlan(String query) throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("EXPLAIN " + query)) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
// Show the plan WITH execution (actual timings)
public void showActualPlan(String query) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false); // Wrap in transaction for safety
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("EXPLAIN ANALYZE " + query)) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
conn.rollback(); // Roll back so writes are not committed
}
}
}
How does EXPLAIN ANALYZE differ?
EXPLAIN ANALYZE goes further: it runs the query and annotates each node with actual time, actual rows, and loop count. Now you can compare what the optimizer predicted against what truly happened.
-- EXPLAIN ANALYZE runs the query and shows actuals
EXPLAIN ANALYZE
SELECT t.amount, t.created_at
FROM transactions t
WHERE t.account_id = 'SAV2001'
AND t.created_at >= '2024-01-01'
ORDER BY t.created_at DESC;
-- Output:
-- Sort (cost=150..155 rows=100 width=24)
-- (actual time=0.3..0.4 rows=42 loops=1)
-- Sort Key: created_at DESC
-- Sort Method: quicksort Memory: 28kB
-- -> Index Scan using idx_txn_acct_date on transactions t
-- (cost=0.4..140 rows=100 width=24)
-- (actual time=0.05..0.2 rows=42 loops=1)
-- Index Cond: (account_id = 'SAV2001' AND created_at >= '2024-01-01')
-- Planning Time: 0.1 ms
-- Execution Time: 0.5 ms
Notice: estimated 100 rows, actual 42. This gap is small and acceptable. A gap of 10 estimated vs 500,000 actual would indicate stale statistics.
Why is a sequential scan the first thing you look for?
A sequential scan reads every single row in the table. On a table with millions of rows, it is slow. When you expected an index to be used and see a Seq Scan instead, it points to one of three causes.
-- Cause 1: Missing index
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE merchant_code = 'HDFC_ATM_001';
-- Seq Scan on transactions (actual time=0.05..5800 rows=15)
-- Fix: CREATE INDEX idx_txn_merchant ON transactions(merchant_code);
-- Cause 2: Non-sargable predicate (function on column)
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE UPPER(holder_name) = 'RAJESH KUMAR';
-- Seq Scan on accounts (actual time=0.03..120 rows=1)
-- Fix: CREATE INDEX idx_upper_name ON accounts(UPPER(holder_name));
-- Or: store normalized name and compare directly
-- Cause 3: Low selectivity (correct Seq Scan)
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE txn_type = 'DEBIT';
-- If 80% of transactions are DEBIT, a Seq Scan is correct
-- An index scan would touch 80% of pages anyway
-- Nothing to fix here
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class SeqScanDiagnosticService {
private final DataSource dataSource;
public SeqScanDiagnosticService(DataSource dataSource) {
this.dataSource = dataSource;
}
// This query was slow because UPPER() made it non-sargable
// WRONG:
// SELECT * FROM accounts WHERE UPPER(holder_name) = ?
// FIXED: remove function, match stored format
public String findAccountByName(String name) throws SQLException {
String sql = "SELECT account_id FROM accounts WHERE holder_name = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, name);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getString("account_id") : null;
}
}
}
}
What other plan issues should you look for?
Beyond Seq Scans, there are three more common plan issues.
-- Issue 2: Nested Loop with huge outer table
-- A Nested Loop probes the inner table once per outer row
-- Great when outer is small; catastrophic when outer has millions
EXPLAIN ANALYZE
SELECT a.holder_name, SUM(t.amount)
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
GROUP BY a.holder_name;
-- If plan shows Nested Loop with 100K outer rows → should be Hash Join
-- Usually means stale stats underestimated the outer side
-- Issue 3: Sort with high cost
-- An ORDER BY without a supporting index causes an explicit Sort
EXPLAIN ANALYZE
SELECT * FROM transactions ORDER BY created_at DESC LIMIT 100;
-- Sort (cost=500000... actual time=8000...)
-- Fix: CREATE INDEX idx_txn_created ON transactions(created_at DESC);
-- Issue 4: Estimated vs actual row mismatch
-- The optimizer expected 10 rows but got 1 million
-- Fix: ANALYZE transactions;
How do you use the BUFFERS option?
EXPLAIN (ANALYZE, BUFFERS) shows shared buffer hits (from cache) and reads (from disk), telling you whether the slowness is CPU or I/O.
EXPLAIN (ANALYZE, BUFFERS)
SELECT amount FROM transactions WHERE account_id = 'SAV2001';
-- Output includes:
-- Buffers: shared hit=5 ← 5 pages from cache (fast)
-- Buffers: shared read=15000 ← 15000 pages from disk (slow!)
-- If mostly "shared hit": data is cached, CPU is the cost
-- If mostly "shared read": data is on disk, I/O is the cost
-- If I/O is the cost, more RAM for shared_buffers may help
package com.bank.diagnostic;
public class BuffersInterpretation {
// shared hit = pages found in PostgreSQL's shared buffer cache
// shared read = pages read from disk (or OS page cache)
// shared dirtied = pages modified in this query
// shared written = pages flushed to disk
// High "shared read" with slow query → I/O bound
// → Add more shared_buffers, or add SSD, or add index to reduce pages read
// High "shared hit" with slow query → CPU bound
// → Optimize the query logic, reduce rows processed
}
What are the access method types in a plan?
Understanding the access methods helps you read plans quickly.
PostgreSQL access methods:
Seq Scan → Reads every row in the table (full scan)
Index Scan → Walks the index, fetches matching rows from heap
Index Only Scan → Answers entirely from the index (no heap access)
Bitmap Heap Scan → Combines index with batched heap reads
(used when index returns many rows)
Join methods:
Nested Loop → For each outer row, probe inner (good for small outer)
Hash Join → Build hash table of one side, stream other through
Merge Join → Both sides sorted, merge them
When you see:
Seq Scan on large table → missing index or non-sargable
Nested Loop with large outer → should be Hash Join (stale stats?)
Sort with high cost → missing index for ORDER BY
What are common mistakes when reading query plans?
The most common mistake is looking only at the estimated cost and ignoring the actual execution time. Another mistake is ignoring the "Rows Removed by Filter" metric, which reveals how many rows the engine read but discarded.
-- WRONG: assuming a low cost estimate means a fast query
-- CORRECT: always use EXPLAIN ANALYZE to see actual time
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE account_id = 'SAV2001' AND created_at > '2024-01-01';
-- Check: actual time, rows removed, buffers hit vs read
What should a senior engineer know?
A senior engineer reads plans bottom-up and inside-out because the innermost nodes execute first. They use FORMAT JSON for machine-readable plans and tooling like pgMustard or auto_explain for production monitoring.
-- JSON format for programmatic analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT amount FROM transactions WHERE account_id = 'SAV2001';
-- auto_explain extension: logs plans for slow queries automatically
-- In postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '100ms'
-- auto_explain.log_analyze = true
-- auto_explain.log_buffers = true
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ EXPLAIN vs EXPLAIN ANALYZE │ ├────────────────────────────────────────────────────────────┤ │ │ │ EXPLAIN (plan only, does NOT run): │ │ ┌──────────────────────────────────────────────────┐ │ │ │ Sort (cost=150..155 rows=100) │ │ │ │ → Index Scan (cost=0.4..140 rows=100) │ │ │ │ Estimates only, based on statistics │ │ │ └──────────────────────────────────────────────────┘ │ │ │ │ EXPLAIN ANALYZE (runs the query, shows actuals): │ │ ┌──────────────────────────────────────────────────┐ │ │ │ Sort (cost=150 rows=100) (actual time=0.4 rows=42)│ │ │ │ → Index Scan (cost=0.4 rows=100)(actual rows=42)│ │ │ │ Compare estimates vs actuals! │ │ │ │ Big gap → stale stats → ANALYZE table │ │ │ └──────────────────────────────────────────────────┘ │ │ │ │ What to look for: │ │ 1. Seq Scan on large table → missing index / sargability │ │ 2. Nested Loop with huge outer → should be Hash Join │ │ 3. Sort with high cost → missing index for ORDER BY │ │ 4. Estimated ≠ Actual rows → stale statistics │ └────────────────────────────────────────────────────────────┘
Key Takeaway
EXPLAIN asks the database to show the execution plan the optimizer chose without actually running the query. It shows the access method (Seq Scan versus Index Scan), the join order and algorithm, which indexes will be used, and estimated row counts and cost. EXPLAIN ANALYZE goes further: it actually runs the query and annotates each node with real row counts and real timings alongside the estimates, so you can compare prediction against reality. Because it executes, writing statements should be wrapped in a transaction and rolled back.
The first thing to look for in a plan is a sequential scan on a large table. A Seq Scan reads every single row, so on a table with millions of rows it is slow. Sometimes a Seq Scan is correct: if the query genuinely returns most of the table, scanning beats an index. But when an index was expected and a Seq Scan appears instead, it almost always means a missing index, a non-sargable predicate wrapping the column in a function, or a low-selectivity filter. After that, look for a Nested Loop over a huge outer table that should be a Hash Join, a costly Sort that an index could avoid, and above all a big gap between estimated and actual rows indicating stale statistics.
The BUFFERS option adds shared buffer hits and disk reads, telling you whether the cost is CPU or I/O. A senior engineer reads plans bottom-up because the innermost nodes execute first, and uses auto_explain to automatically log plans for slow queries in production.
At the National Bank of India, a query filtering transactions by account slowed to seconds in production. EXPLAIN showed a Seq Scan because someone had written WHERE UPPER(account_id) = ..., making it non-sargable. Rajesh removed the function so the predicate matched the index, and EXPLAIN ANALYZE confirmed the plan switched to an Index Scan with the time dropping from four seconds to two milliseconds.
Remember one sentence: EXPLAIN shows the chosen plan with estimates, EXPLAIN ANALYZE runs it and shows the actuals, and the first thing you hunt for is a sequential scan on a big table.
---