SQL 76 🛢️ Execution Plans and Query Optimization
A SQL query is a declaration of intent. The database is free to satisfy it however it chooses: scanning a table, walking an index, joining in one order or another, sorting in memory or on disk. The execution plan is the database’s chosen strategy, and reading it is the only reliable way to understand why a query is slow. The query optimizer, which produces the plan, is a cost-based decision engine. It estimates the cost of many possible plans and picks the cheapest one according to its statistics. When the estimates are wrong, the plan is wrong, and the query is slow. This chapter covers how plans are produced, how to read them, and how to intervene when the optimizer makes a bad choice.
Key point: The optimizer is not infallible. It decides based on statistics, and statistics are approximations of reality. A stale or misleading statistic produces a bad plan, and the developer’s job is to fix the statistic or rewrite the query so the estimate matches the truth.
Why execution plans matter
The black-box problem. A query that returns the right answer in ten milliseconds and a query that returns the right answer in ten minutes look identical in the source code. The difference is invisible until the plan is examined. Reading the plan turns performance tuning from guesswork into diagnosis.
The optimizer’s job. The optimizer receives a parsed query, generates candidate plans, estimates the cost of each, and chooses the cheapest. The cost model is based on statistics: row counts, distinct values, histograms, and index page counts. The optimizer does not execute the query; it reasons about it.
The cost estimation problem. Every estimate is an approximation. The optimizer assumes uniformity when the data is skewed, independence when columns are correlated, and accuracy when the statistics are current. When any of these assumptions fails, the estimate diverges from reality, and the plan is suboptimal.
The plan shapes problem. A plan can be a sequential scan, an index scan, an index-only scan, a bitmap scan, a nested loop join, a hash join, or a merge join. Each shape is appropriate for a different situation. The optimizer chooses based on cost, but the choice depends entirely on the estimates.
The regression problem. A query that was fast last week can be slow this week, with no change to the query. What changed is the data distribution, and therefore the statistics, and therefore the plan. This is the classic plan regression, and it is invisible without a plan comparison.
The portability problem. Every database produces plans in its own format. PostgreSQL uses EXPLAIN, MySQL uses EXPLAIN and EXPLAIN ANALYZE, SQL Server uses execution plans in SSMS, and Oracle uses EXPLAIN PLAN and DBMS_XPLAN. The concepts are shared; the syntax is not. The patterns in this chapter apply across all of them.
a. Reading an execution plan
An execution plan is a tree of operators. Each operator produces rows for its parent and consumes rows from its children. The root produces the query’s final result. The leaves read from tables or indexes.
The PostgreSQL EXPLAIN output
The EXPLAIN command shows the plan without executing the query. The EXPLAIN ANALYZE command executes the query and shows the actual row counts and timings alongside the estimates.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Seq Scan on orders (cost=0.00..45000.00 rows=20 width=50)
Filter: (customer_id = 42)The output has three parts. The operator is Seq Scan on orders, which means the database will read every row of the orders table. The cost is 0.00..45000.00, where the first number is the startup cost and the second is the total cost, both in arbitrary units. The rows=20 is the estimated number of rows the operator will return, and width=50 is the estimated row width in bytes.
The EXPLAIN ANALYZE output
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;Seq Scan on orders (cost=0.00..45000.00 rows=20 width=50)
(actual time=0.015..850.000 rows=20 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 999980
Planning Time: 0.234 ms
Execution Time: 852.123 msThe actual time is the real time in milliseconds, and actual rows is the real row count. The Rows Removed by Filter: 999980 shows that the scan read one million rows to return twenty. The loops=1 indicates the operator ran once.
The discrepancy between estimate and actual
The most important number in the plan is the difference between the estimated rows and the actual rows. A large discrepancy means the optimizer’s statistics are misleading, and the plan may be wrong.
Seq Scan on orders (cost=0.00..45000.00 rows=20 width=50)
(actual time=0.015..850.000 rows=20 loops=1)In this example, the estimate (rows=20) matches the actual (rows=20). The plan is correct, and the problem is that a sequential scan was chosen despite the filter returning few rows.
Seq Scan on orders (cost=0.00..45000.00 rows=200000 width=50)
(actual time=0.015..850.000 rows=20 loops=1)Here the estimate is rows=200000 and the actual is rows=20. The optimizer thought the filter would match many rows, so it chose a sequential scan. The estimate is wrong by four orders of magnitude. The fix is to update the statistics or rewrite the query.
The operator types
Each operator in the plan describes a different strategy.
| Operator | Meaning |
|---|---|
Seq Scan | Read every row of the table |
Index Scan | Walk an index and fetch matching rows |
Index Only Scan | Walk an index without fetching the heap |
Bitmap Index Scan | Build a bitmap of matching pages |
Bitmap Heap Scan | Read the pages from the bitmap |
Nested Loop | For each row of the outer, scan the inner |
Hash Join | Build a hash table and probe it |
Merge Join | Merge two sorted inputs |
Sort | Sort the input |
Aggregate | Compute an aggregate |
Limit | Stop after N rows |
The plan tree
The plan is a tree, and the indentation shows the parent-child relationship. The root is at the top, and the leaves are at the bottom. Execution flows from the leaves to the root.
Hash Join (cost=... rows=...)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=... rows=...)
-> Hash (cost=... rows=...)
-> Seq Scan on customers c (cost=... rows=...)The Hash Join is the parent. Its children are the Seq Scan on orders and the Hash, which in turn has the Seq Scan on customers as its child. The Hash operator builds a hash table from the customers scan, and the Hash Join probes that table for each row of the orders scan.
b. The cost model
The cost model is the set of rules the optimizer uses to estimate the cost of each operation. The costs are relative, not absolute. A sequential page read costs one unit; a random page read costs four; a CPU comparison costs a fraction of a unit.
The cost units
PostgreSQL’s cost units are calibrated against the time to read a sequential page on the machine where PostgreSQL was compiled. The defaults are documented in the postgresql.conf file as seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost.
| Parameter | Default | Meaning |
|---|---|---|
seq_page_cost | 1.0 | Sequential page read |
random_page_cost | 4.0 | Random page read |
cpu_tuple_cost | 0.01 | Processing one row |
cpu_index_tuple_cost | 0.005 | Processing one index entry |
cpu_operator_cost | 0.0025 | Evaluating one operator |
The ratio between seq_page_cost and random_page_cost is what makes the optimizer prefer sequential scans when many rows are needed and index scans when few are needed. On SSDs, the ratio is often changed to 1.1 or 2.0 because random reads are nearly as fast as sequential.
The selectivity estimate
The optimizer estimates the selectivity of each filter, which is the fraction of rows the filter will match. The estimate is based on the column’s statistics.
SELECT * FROM orders WHERE status = 'pending';If the statistics show that status has four distinct values and pending is 25% of the rows, the optimizer estimates the filter’s selectivity at 0.25. The estimated rows are the table’s row count multiplied by the selectivity.
The histograms
For columns with many distinct values, the optimizer uses histograms to estimate selectivity for range filters. The histogram divides the value range into buckets of roughly equal size, and the optimizer interpolates within a bucket.
SELECT * FROM orders WHERE order_date > '2026-01-01';The histogram tells the optimizer what fraction of rows fall in each date range. The estimate is the sum of the fractions for the buckets above the threshold.
The distinct value count
For equality filters, the optimizer uses the number of distinct values. If a column has N distinct values and the data is uniformly distributed, the selectivity of an equality filter is approximately 1/N. When the data is skewed, the estimate is wrong.
SELECT * FROM orders WHERE customer_id = 42;If customer_id has 50,000 distinct values and one million rows, the estimate is 20 rows. If some customers have 10,000 orders and most have 5, the estimate is right on average and wrong for the specific value.
The correlation statistic
The optimizer tracks the correlation between a column’s value and its physical position. A correlation of 1.0 means the column is perfectly ordered; 0.0 means it is random. High correlation means an index scan on that column is cheap, because the rows are physically adjacent. Low correlation means the index scan is expensive, because each row is on a different page.
SELECT correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'order_date';The order_date column on an append-only table usually has correlation near 1.0, which makes an index scan on it efficient. The customer_id column usually has low correlation, which makes index scans expensive and sequential scans preferable for large result sets.
c. Common plan problems
The optimizer produces a good plan most of the time. When it does not, the pattern is usually one of the following.
The sequential scan on a selective filter
A filter that matches few rows should use an index. If the plan shows a Seq Scan with Rows Removed by Filter much larger than the actual rows, the index is missing or the estimate is wrong.
Seq Scan on orders (cost=0.00..45000.00 rows=1 width=50)
(actual time=0.015..850.000 rows=1 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 999999The estimate (rows=1) is correct, but the optimizer still chose a sequential scan. This usually means the index does not exist, or the index is not usable for this query, or the cost model incorrectly prefers the scan.
The index scan on a non-selective filter
An index scan on a filter that matches many rows is worse than a sequential scan, because each row is on a different page. If the plan shows an Index Scan returning a large fraction of the table, the optimizer may have overestimated the benefit.
Index Scan using idx_orders_status on orders (cost=... rows=250000)
(actual time=... rows=250000)
Index Cond: (status = 'pending')A quarter of the table is being fetched through an index, which means 250,000 random page reads. The sequential scan would have been faster.
The nested loop with a large outer input
A nested loop join is efficient when the outer input is small and the inner is indexed. It is disastrous when the outer input is large, because the inner is scanned once per outer row.
Nested Loop (cost=... rows=...)
-> Seq Scan on orders (actual rows=100000)
-> Index Scan on customers (actual rows=1 loops=100000)The loops=100000 means the index scan ran 100,000 times, once for each order. The total cost is 100,000 times the cost of one lookup. A hash join would have been better.
The sort spill to disk
A sort that does not fit in memory spills to disk, which is much slower than an in-memory sort. The plan shows the spill with Sort Method: external merge Disk: N kB.
Sort (cost=... rows=...)
Sort Key: created_at DESC
Sort Method: external merge Disk: 24576 kBThe fix is to raise work_mem for the session or the query, or to add an index that provides the sort order.
The misestimate from a stale statistic
A statistic that has not been updated after a large data change produces wrong estimates, which produce wrong plans. The plan shows the estimate far from the actual.
Seq Scan on orders (cost=... rows=1000 width=50)
(actual time=... rows=500000 loops=1)The estimate is 1,000 and the actual is 500,000. The optimizer chose a sequential scan because it thought the result was small, but the result is large. The fix is to run ANALYZE orders to refresh the statistics.
Complete Example Session
-- ============================================
-- PART 1: A SIMPLE QUERY
-- ============================================
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..45000.00 rows=20 width=50)
-- (actual time=0.015..850.000 rows=20 loops=1)
-- Filter: (customer_id = 42)
-- Rows Removed by Filter: 999980
-- Planning Time: 0.234 ms
-- Execution Time: 852.123 ms
-- 1,000,000 rows read to return 20.
-- The optimizer estimated 20 rows correctly.
-- No index exists on customer_id.
-- ============================================
-- PART 2: ADD THE INDEX
-- ============================================
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Index Scan using idx_orders_customer on orders
-- (cost=0.43..8.45 rows=20 width=50)
-- (actual time=0.020..0.035 rows=20 loops=1)
-- Index Cond: (customer_id = 42)
-- Planning Time: 0.312 ms
-- Execution Time: 0.052 ms
-- 0.052 ms instead of 852 ms.
-- ============================================
-- PART 3: THE MISESTIMATE
-- ============================================
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders (cost=0.00..45000.00 rows=250000 width=50)
-- (actual time=0.015..620.000 rows=250000 loops=1)
-- Filter: (status = 'pending'::text)
-- Rows Removed by Filter: 750000
-- Planning Time: 0.198 ms
-- Execution Time: 622.345 ms
-- The estimate matches the actual.
-- The optimizer chose a sequential scan because 25% of rows match.
-- This is the correct plan.
-- ============================================
-- PART 4: THE BAD JOIN
-- ============================================
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.order_date > '2026-01-01';
-- Nested Loop (cost=... rows=...)
-- (actual time=... rows=... loops=1)
-- -> Seq Scan on orders o (actual rows=100000)
-- -> Index Scan on customers c (actual rows=1 loops=100000)
-- Index Cond: (c.id = o.customer_id)
-- 100,000 index lookups for 100,000 orders.
-- A hash join would be faster.
-- ============================================
-- PART 5: THE FIX — UPDATE STATISTICS
-- ============================================
ANALYZE orders;
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.order_date > '2026-01-01';
-- Hash Join (cost=... rows=...)
-- (actual time=... rows=... loops=1)
-- Hash Cond: (o.customer_id = c.id)
-- -> Seq Scan on orders o (actual rows=100000)
-- -> Hash (actual rows=50000)
-- -> Seq Scan on customers c (actual rows=50000)
-- The hash join builds the customers hash table once and probes it.
-- The 100,000 lookups are replaced by a single hash table build.
-- ============================================
-- PART 6: THE SORT SPILL
-- ============================================
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- Limit (cost=... rows=20 width=...)
-- -> Sort (cost=... rows=1000000 width=...)
-- Sort Key: created_at DESC
-- Sort Method: external merge Disk: 24576 kB
-- -> Seq Scan on orders (actual rows=1000000)
-- The sort spilled to disk.
-- Fix: add an index on created_at DESC.
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- Limit (cost=... rows=20 width=...)
-- -> Index Scan using idx_orders_created_desc on orders
-- (actual rows=20 loops=1)
-- The index provides the sort order.
-- No sort, no spill.
-- ============================================
-- PART 7: THE BITMAP SCAN
-- ============================================
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id IN (42, 99, 137);
-- Bitmap Heap Scan on orders (cost=... rows=...)
-- Recheck Cond: (customer_id = ANY ('{42,99,137}'))
-- Heap Blocks: exact=60
-- -> Bitmap Index Scan on idx_orders_customer
-- Index Cond: (customer_id = ANY ('{42,99,137}'))
-- Three index scans merged into one bitmap.
-- The heap is read in physical page order.
-- ============================================
-- PART 8: THE INDEX-ONLY SCAN
-- ============================================
EXPLAIN ANALYZE
SELECT customer_id, order_date FROM orders WHERE customer_id = 42;
-- Index Only Scan using idx_orders_customer_date on orders
-- Index Cond: (customer_id = 42)
-- Heap Fetches: 0
-- The index contains both columns.
-- The heap is never touched.
-- ============================================
-- PART 9: THE COST PARAMETERS
-- ============================================
-- Check the current cost settings.
SHOW seq_page_cost;
SHOW random_page_cost;
-- On SSDs, lower random_page_cost.
SET random_page_cost = 1.1;
-- The optimizer now prefers index scans more often.
-- ============================================
-- PART 10: THE PLAN REGRESSION
-- ============================================
-- Before the data change: the plan was fast.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'archived';
-- Index Scan using idx_orders_status on orders
-- (actual time=0.5..2.3 rows=100 loops=1)
-- After the data change: the plan is slow.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'archived';
-- Seq Scan on orders
-- (actual time=0.015..620.000 rows=500000 loops=1)
-- Filter: (status = 'archived')
-- Rows Removed by Filter: 500000
-- The statistics were stale.
ANALYZE orders;
-- The plan corrects itself.The ten parts showed a simple query, the effect of adding an index, a correct sequential scan, a bad join, the fix by updating statistics, a sort spill, a bitmap scan, an index-only scan, the cost parameters, and a plan regression.
Quick Reference
EXPLAIN Commands
| Command | Purpose |
|---|---|
EXPLAIN | Show the plan without executing |
EXPLAIN ANALYZE | Execute and show actual times |
EXPLAIN (ANALYZE, BUFFERS) | Include buffer usage |
EXPLAIN (FORMAT JSON) | Machine-readable output |
EXPLAIN (VERBOSE) | Include output columns |
Plan Operators
| Operator | When Used |
|---|---|
Seq Scan | No usable index, or large result |
Index Scan | Selective filter, low correlation |
Index Only Scan | All columns in the index |
Bitmap Heap Scan | Moderate selectivity, many rows |
Nested Loop | Small outer, indexed inner |
Hash Join | Large inputs, no useful order |
Merge Join | Both inputs sorted |
Sort | No index provides the order |
Cost Parameters
| Parameter | Default | Effect |
|---|---|---|
seq_page_cost | 1.0 | Sequential read |
random_page_cost | 4.0 | Random read |
cpu_tuple_cost | 0.01 | Row processing |
work_mem | 4 MB | Sort and hash memory |
Common Fixes
| Problem | Fix |
|---|---|
| Seq Scan on selective filter | Add an index |
| Misestimate | ANALYZE the table |
| Bad join order | Update statistics or rewrite |
| Sort spill | Add an index or raise work_mem |
| Index scan on large result | Force sequential scan or drop the index |
Best Practices
✅ Do This:
-- Always run EXPLAIN ANALYZE before tuning.
EXPLAIN ANALYZE SELECT ...; -- ✅
-- Compare estimated rows to actual rows.
-- Large discrepancies indicate bad statistics. -- ✅
-- Update statistics after large data changes.
ANALYZE orders; -- ✅
-- Add indexes for selective filters.
CREATE INDEX idx ON orders(customer_id); -- ✅
-- Add indexes that provide sort order.
CREATE INDEX idx ON orders(created_at DESC); -- ✅
-- Raise work_mem for complex sorts and hashes.
SET work_mem = '256MB'; -- ✅
-- Tune random_page_cost on SSDs.
SET random_page_cost = 1.1; -- ✅❌ Don’t Do This:
-- Don't tune without reading the plan.
SELECT ... ; -- and guessing -- ❌
-- Don't ignore the estimated vs actual difference.
-- It is the primary diagnostic. -- ❌
-- Don't add indexes for every filter.
-- Each index taxes writes. -- ❌
-- Don't forget to ANALYZE after bulk loads.
COPY orders FROM ...; -- ❌ Statistics are stale
-- Don't assume the optimizer is wrong.
-- The estimates are usually correct. -- ❌
-- Don't force plans with hints without understanding.
-- Fix the statistic or the query. -- ❌
-- Don't ignore the cost parameters.
-- The defaults assume spinning disks. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Slow query with index | Stale statistics | ANALYZE |
| Seq Scan on selective filter | Missing index | Add the index |
| Index scan on large result | Overestimated selectivity | Check statistics |
| Nested loop with large outer | Misestimated join size | Update statistics |
| Sort spill | Low work_mem | Raise it or add an index |
| Plan changes after data load | Statistics not updated | ANALYZE after the load |
| Cost parameters wrong for SSD | Defaults assume HDD | Lower random_page_cost |
| Bitmap scan slower than expected | Too many matching pages | Check selectivity |
Real-World Examples
1. Basic Plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;2. Plan with Actual Times
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;3. Plan with Buffers
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders;4. Update Statistics
ANALYZE orders;5. Add an Index
CREATE INDEX idx_orders_customer ON orders(customer_id);6. Add a Sort Index
CREATE INDEX idx_orders_created ON orders(created_at DESC);7. Raise work_mem
SET work_mem = '256MB';8. Check Correlation
SELECT correlation FROM pg_stats WHERE tablename = 'orders';9. Tune for SSD
SET random_page_cost = 1.1;10. Detect a Regression
-- Compare plans before and after a data change.
-- The estimated rows diverge from the actual.Visual
The Plan Tree
┌────────────────────────────────────────────────────────────┐
│ EXECUTION PLAN TREE │
│ │
│ Hash Join ← root, produces the result │
│ │ │
│ ├── Seq Scan on orders │
│ │ ← leaf, reads the orders table │
│ │ │
│ └── Hash │
│ │ │
│ └── Seq Scan on customers │
│ ← leaf, reads the customers table │
│ │
│ Execution flows from the leaves to the root. │
│ The Hash builds a table from customers. │
│ The Hash Join probes it for each order. │
└────────────────────────────────────────────────────────────┘Estimate vs Actual
┌────────────────────────────────────────────────────────────┐
│ THE KEY DIAGNOSTIC │
│ │
│ Seq Scan on orders (cost=0.00..45000.00 rows=20 width=50)│
│ (actual time=0.015..850.000 rows=20) │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ rows=20 ← the estimate │ │
│ │ actual rows=20 ← the reality │ │
│ │ │ │
│ │ They match. The plan is correctly chosen. │ │
│ │ The problem is elsewhere: no index exists. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ ────────────────────────────────────────────────────── │
│ │
│ Seq Scan on orders (cost=... rows=1000 width=50) │
│ (actual time=... rows=500000) │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ rows=1000 ← the estimate (wrong) │ │
│ │ actual rows=500000 ← the reality │ │
│ │ │ │
│ │ The estimate is off by 500×. │ │
│ │ The statistics are stale. Run ANALYZE. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘Join Strategies
┌────────────────────────────────────────────────────────────┐
│ THREE JOIN STRATEGIES │
│ │
│ NESTED LOOP │
│ ┌──────────────────────────────────────────────────┐ │
│ │ For each row of the outer: │ │
│ │ Look up matching rows in the inner. │ │
│ │ │ │
│ │ Best when: outer is small, inner is indexed. │ │
│ │ Worst when: outer is large. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ HASH JOIN │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Build a hash table from one input. │ │
│ │ Probe it for each row of the other. │ │
│ │ │ │
│ │ Best when: both inputs are large, no order. │ │
│ │ Worst when: the hash table spills to disk. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ MERGE JOIN │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Sort both inputs. │ │
│ │ Walk them in parallel. │ │
│ │ │ │
│ │ Best when: both inputs are already sorted. │ │
│ │ Worst when: a sort is needed for both. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘The Cost Model
┌────────────────────────────────────────────────────────────┐
│ COST MODEL │
│ │
│ seq_page_cost = 1.0 ← sequential read │
│ random_page_cost = 4.0 ← random read │
│ cpu_tuple_cost = 0.01 ← row processing │
│ │
│ SEQUENTIAL SCAN of N pages: │
│ cost = N × 1.0 │
│ │
│ INDEX SCAN for M rows on N pages: │
│ cost = M × (tree traversal) + M × 4.0 │
│ │
│ The optimizer picks the cheaper option. │
│ On SSDs, random reads are nearly as fast as sequential, │
│ so random_page_cost should be lowered. │
│ │
│ The ratio determines the crossover point. │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Execution plan | The database’s chosen strategy |
EXPLAIN | Show the plan without executing |
EXPLAIN ANALYZE | Execute and show actual times |
| Estimated rows | The optimizer’s prediction |
| Actual rows | The real row count |
| Large discrepancy | Indicates stale statistics |
ANALYZE | Update statistics |
Seq Scan | Read every row |
Index Scan | Walk an index |
Bitmap Heap Scan | Batch heap reads |
Nested Loop | Small outer, indexed inner |
Hash Join | Large inputs |
Merge Join | Sorted inputs |
work_mem | Sort and hash memory |
Key takeaways:
- The execution plan is the only reliable way to diagnose a slow query. Reading the plan shows which operators are used, how many rows flow through each, and where the time is spent. Without the plan, tuning is guesswork.
- The estimate-versus-actual comparison is the primary diagnostic. When the estimated rows diverge from the actual rows by orders of magnitude, the optimizer’s statistics are misleading, and the plan is likely wrong.
- Statistics are the foundation of the optimizer’s decisions. Row counts, distinct value counts, histograms, and correlation determine the estimated selectivity of every filter. Stale statistics produce wrong estimates and wrong plans.
ANALYZErefreshes the statistics. After a bulk load, a large delete, or a significant data change,ANALYZEbrings the statistics up to date. Automatic analyze runs on a schedule, but a manual run after a bulk load is often necessary.- Each join strategy has a purpose. Nested loops are efficient for small outer inputs with indexed inner tables. Hash joins are efficient for large inputs with no useful order. Merge joins are efficient when both inputs are already sorted.
- Sort operations can spill to disk. When the sort does not fit in
work_mem, it writes to a temporary file, which is much slower. Raisingwork_memfor the session or adding an index that provides the sort order avoids the spill. - The cost parameters assume spinning disks. The default
random_page_costof4.0reflects the cost of a seek on a mechanical disk. On SSDs, random reads are nearly as fast as sequential, and lowering the parameter to1.1or2.0makes the optimizer prefer index scans where appropriate. - Plan regressions happen without code changes. A query that was fast last week can be slow this week because the data distribution changed and the statistics were not updated. Comparing plans before and after the change reveals the cause.
Remember: Execution plans are the window into how the database satisfies a query. The optimizer chooses a plan based on statistics, and the statistics are approximations. Reading the plan means comparing the estimated rows to the actual rows, identifying the operators, and understanding the cost model. The most common fix is to update the statistics with ANALYZE, the second most common is to add an index that the query can use, and the third is to rewrite the query so the optimizer’s estimates match reality. Cost parameters, especially random_page_cost on SSDs, can be tuned to match the hardware. The result is a query that runs in milliseconds instead of minutes, and a developer who understands why.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!