Definition
- Seq Scan where Index expected – Missing index, non-sargable predicate (function on column), or low selectivity where the optimizer correctly prefers scanning.
- Nested Loop with huge outer – Probes the inner table once per outer row; catastrophic when outer has millions of rows; a Hash Join is cheaper.
- Sort with high cost – An ORDER BY without a supporting index; adding an index on the sort columns eliminates the sort node.
- Estimated vs actual mismatch – The optimizer chose a plan based on wrong statistics; running
ANALYZErefreshes them and usually produces a better plan.
Think of the plan as a bank clerk's written route for finding and sorting transactions. You scan that route for four mistakes: the clerk flipping through the entire ledger when an index tab exists (Seq Scan), the clerk re-checking the whole archive once per customer in a long list (Nested Loop), the clerk manually re-sorting a big pile when the ledger is already date-ordered (costly Sort), and the clerk badly guessing how many rows they will touch (stale statistics). Spotting each tells you exactly what to fix.
Understanding the Concepts
What is the Seq Scan signature?
The first signature is a sequential scan where you expected an index scan. This has three usual 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)
-- Filter: (merchant_code = 'HDFC_ATM_001')
-- Rows Removed by Filter: 1999985
-- The column has no index!
-- Fix:
CREATE INDEX idx_txn_merchant ON transactions(merchant_code);
-- Re-run: Index Scan (actual time=0.03..0.1 rows=15)
-- Cause 2: Non-sargable predicate
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE UPPER(holder_name) = 'RAJESH KUMAR';
-- Seq Scan on accounts (actual time=0.03..120 rows=1)
-- The UPPER() function prevents index usage
-- Fix option A: functional index
CREATE INDEX idx_upper_name ON accounts(UPPER(holder_name));
-- Fix option B: rewrite query to match stored format
SELECT * FROM accounts WHERE holder_name = 'Rajesh Kumar';
-- Cause 3: Low selectivity (correct Seq Scan)
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE txn_type = 'DEBIT';
-- Seq Scan (actual rows=1600000 out of 2000000)
-- 80% of rows match → index scan would touch 80% of pages anyway
-- The optimizer is correct: nothing to fix
package com.bank.diagnostic;
public class SeqScanAnalysis {
// Decision tree for unexpected Seq Scan:
// 1. Does an index exist on the filtered column?
// NO → CREATE INDEX
// YES → continue
// 2. Is the predicate sargable (no function on column)?
// NO → rewrite predicate or create functional index
// YES → continue
// 3. Is the selectivity low (most rows match)?
// YES → Seq Scan is correct, nothing to fix
// NO → check if statistics are stale → ANALYZE
}
What is the Nested Loop with huge outer signature?
A Nested Loop join probes the inner table once per outer row. This is great when the outer side is tiny and the inner side is indexed, but catastrophic when the outer side has millions of rows.
-- BAD: Nested Loop with 500,000 outer rows
EXPLAIN ANALYZE
SELECT a.holder_name, t.amount
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
WHERE t.created_at >= '2024-01-01';
-- Nested Loop (actual time=0.1..45000 rows=500000)
-- -> Seq Scan on transactions (actual rows=500000)
-- -> Index Scan on accounts (loops=500000)
-- (each loop finds 1 row, but 500,000 loops!)
-- Execution Time: 45000 ms
-- This should be a Hash Join:
-- Build a hash table of accounts (small), stream transactions through
-- Hash Join (actual time=100..800 rows=500000)
-- Execution Time: 800 ms
A bad Nested Loop often appears because the optimizer underestimated the outer row count due to stale statistics.
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 JoinDiagnosticService {
private final DataSource dataSource;
public JoinDiagnosticService(DataSource dataSource) {
this.dataSource = dataSource;
}
// If EXPLAIN shows Nested Loop with huge outer:
// 1. Run ANALYZE on both tables (fix stats)
// 2. Ensure join keys are indexed on both sides
// 3. Filter early to reduce the outer row count
public void getTransactionsWithHolders(String startDate) throws SQLException {
String sql = "SELECT a.holder_name, t.amount "
+ "FROM transactions t "
+ "JOIN accounts a ON t.account_id = a.account_id "
+ "WHERE t.created_at >= ?::date";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, startDate);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
// Process results
}
}
}
}
}
What is the costly Sort signature?
If a query has ORDER BY and the plan shows an expensive Sort node, an index on the ORDER BY columns could let the engine read rows already in order, eliminating the sort entirely.
-- BAD: Sort node with high cost
EXPLAIN ANALYZE
SELECT txn_id, amount, created_at
FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY created_at DESC
LIMIT 20;
-- Sort (cost=50000..50001 actual time=6000..6000)
-- Sort Key: created_at DESC
-- Sort Method: external merge Disk: 4096kB ← spilling to disk!
-- -> Index Scan using idx_account (actual rows=50000)
-- Execution Time: 6005 ms
-- Fix: composite index that provides the sort order
CREATE INDEX idx_txn_acct_date_desc
ON transactions(account_id, created_at DESC);
-- After:
-- Index Scan using idx_txn_acct_date_desc
-- (actual time=0.03..0.1 rows=20)
-- NO Sort node at all! Index provides the order.
-- Execution Time: 0.15 ms
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 RecentTransactionsService {
private final DataSource dataSource;
public RecentTransactionsService(DataSource dataSource) {
this.dataSource = dataSource;
}
// With idx_txn_acct_date_desc, this query has no Sort node
// The index provides rows in created_at DESC order
public void getRecentTransactions(String accountId, int limit) throws SQLException {
String sql = "SELECT txn_id, amount, created_at FROM transactions "
+ "WHERE account_id = ? ORDER BY created_at DESC LIMIT ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setInt(2, limit);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getLong("txn_id") + ": "
+ rs.getBigDecimal("amount"));
}
}
}
}
}
What is the estimated vs actual row mismatch signature?
The fourth signature, which often explains the others, is a large gap between estimated and actual rows in EXPLAIN ANALYZE.
-- The optimizer expected 10 rows but got 500,000
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE status = 'PENDING';
-- Seq Scan on transactions
-- (cost=0..28000 rows=10 width=100) ← estimated 10
-- (actual time=0.05..5000 rows=500000) ← actual 500,000!
-- The optimizer chose Seq Scan because it thought only 10 rows matched
-- With correct stats it would have used an index (or still Seq Scan if 25%)
-- Fix: refresh statistics
ANALYZE transactions;
-- After ANALYZE, re-run:
-- Index Scan on idx_txn_status
-- (cost=0.4..2000 rows=500000 width=100)
-- (actual time=0.05..800 rows=500000)
-- The optimizer now makes a better plan choice
package com.bank.maintenance;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class StaleStatsFixService {
private final DataSource dataSource;
public StaleStatsFixService(DataSource dataSource) {
this.dataSource = dataSource;
}
// After bulk operations, always refresh statistics
public void refreshAfterBulkUpdate() throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
// Refresh stats for tables that received bulk changes
stmt.execute("ANALYZE transactions");
stmt.execute("ANALYZE accounts");
System.out.println("Statistics refreshed after bulk update");
}
}
}
How do you read the plan bottom-up?
The innermost nodes execute first. Read from the bottom of the plan tree upward to understand the execution flow.
Plan reading order (bottom-up, inside-out):
Hash Join (executes LAST)
-> Hash (executes SECOND: builds hash table)
-> Seq Scan on accounts (executes FIRST: scans accounts)
-> Index Scan on transactions (executes THIRD: probes hash)
Execution flow:
1. Seq Scan reads all accounts into memory
2. Hash builds a hash table from accounts
3. Index Scan reads matching transactions
4. Hash Join probes the hash table for each transaction
What are the four signatures summarized?
Signature 1: Unexpected Seq Scan
Cause: missing index, non-sargable, low selectivity
Fix: add index, rewrite predicate, or accept Seq Scan
Signature 2: Nested Loop with huge outer
Cause: optimizer underestimated outer rows (stale stats)
Fix: ANALYZE tables, ensure join keys indexed
Signature 3: Sort with high cost
Cause: ORDER BY without supporting index
Fix: add index matching the sort order
Signature 4: Estimated vs actual row mismatch
Cause: stale statistics after bulk operations
Fix: ANALYZE table to refresh planner histograms
What are common interview follow-up questions?
- How do you fix a Seq Scan that should be an Index Scan?
- What causes the optimizer to choose a Seq Scan even when an index exists?
- How do stale statistics lead to bad query plans?
- What is the difference between a Hash Join and a Merge Join in the plan?
What should a senior engineer know?
A senior engineer uses EXPLAIN (ANALYZE, BUFFERS) to distinguish CPU-bound from I/O-bound queries, enables auto_explain to log slow query plans automatically in production, and knows that the most impactful fixes are almost always fixing stale statistics and adding the right index.
-- Enable auto_explain in postgresql.conf
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '100ms'
-- auto_explain.log_analyze = true
-- auto_explain.log_buffers = true
-- This logs the full EXPLAIN ANALYZE output for any query > 100ms
-- Invaluable for catching plan regressions in production
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ Four Query Plan Signatures │ ├────────────────────────────────────────────────────────────┤ │ │ │ Signature 1: Seq Scan on large table │ │ ┌────────────────────┐ ┌────────────────────────┐ │ │ │ Missing index? │→ │ CREATE INDEX │ │ │ │ Function on column?│→ │ Rewrite sargable │ │ │ │ Low selectivity? │→ │ Accept (correct) │ │ │ └────────────────────┘ └────────────────────────┘ │ │ │ │ Signature 2: Nested Loop (huge outer) │ │ ┌────────────────────┐ ┌────────────────────────┐ │ │ │ Outer = millions │→ │ ANALYZE + index join key│ │ │ │ Should be Hash Join│ │ Filter early │ │ │ └────────────────────┘ └────────────────────────┘ │ │ │ │ Signature 3: Sort with high cost │ │ ┌────────────────────┐ ┌────────────────────────┐ │ │ │ ORDER BY no index │→ │ Add index on sort cols │ │ │ │ Spills to disk │ │ Sort node disappears │ │ │ └────────────────────┘ └────────────────────────┘ │ │ │ │ Signature 4: Estimated ≠ Actual rows │ │ ┌────────────────────┐ ┌────────────────────────┐ │ │ │ Est=10, Act=500K │→ │ ANALYZE table │ │ │ │ Stale statistics │ │ Plan changes completely │ │ │ └────────────────────┘ └────────────────────────┘ │ └────────────────────────────────────────────────────────────┘
Key Takeaway
Reading a query plan for performance problems means pattern-matching against four well-known signatures, each mapping to a specific fix. The first is a sequential scan where an index scan was expected: the three causes are a missing index, a non-sargable predicate wrapping the column in a function, or a low-selectivity filter that correctly prefers scanning. The second is a Nested Loop join driven by a huge outer table, which probes the inner table once per outer row and is catastrophic with millions of outer rows; a Hash Join is usually far cheaper.
The third signature is a Sort node with high cost: if the query orders by a column, an index on that column lets the engine read rows already in order and skip the sort entirely. The fourth, which often explains the others, is a large gap between estimated and actual rows. The optimizer makes every choice from table statistics, and if those are stale after a bulk load, it can estimate ten rows where there are five hundred thousand and choose entirely the wrong plan. Running ANALYZE refreshes the statistics and usually produces a dramatically better plan.
A senior engineer reads plans bottom-up because the innermost nodes execute first, uses the BUFFERS option to distinguish CPU-bound from I/O-bound queries, and enables auto_explain to log slow query plans automatically in production.
At the National Bank of India, a reporting join crawled because the plan showed all four symptoms: a Seq Scan from a function-wrapped predicate, a Nested Loop over millions of rows, a costly Sort on transaction date, and an estimate of ten against an actual of five million. Rajesh rewrote the predicate, added an index on the date column, and ran ANALYZE. The optimizer switched the join to a Hash Join and the sort disappeared, taking the report from minutes to seconds.
Remember one sentence: match the plan against four signatures: unexpected Seq Scan, Nested Loop on a huge outer, costly Sort, and a big estimate-versus-actual gap: each with its own concrete remedy.
---