KWAS Academy
Learn. Build. Master.
QUICK START:PatternsErrors & FixesSecurityBenchmarksDevOps RecipesCheatsheetsInterviewCompareTopicsHTMLCSSJavaScriptTypeScriptPythonSQLReactNext.jsNode.jsLinux & UbuntuKotlinSwiftC# / .NETJavaGoRustC++DSASystem DesignDevOpsCybersecurityAI / ML🎁 Cloud Perks & Tools →
Advanced 24 min readModule: Module 14: Query Optimizer Internals: Cost Models & Join Strategies

Query Optimizer Internals: Cost Models & Join Algorithms

Master the Cost-Based Optimizer (CBO): how PostgreSQL estimates disk I/O and CPU costs (`seq_page_cost`, `random_page_cost`), selecting between Nested Loop, Hash Join, and Merge Join algorithms, and tuning `pg_statistic` histogram buckets.

What You Will Learn in This Lesson

  • The Cost-Based Optimizer formula: Total Cost = CPU Cost + Sequential I/O + Random I/O
  • The 3 Relational Join Algorithms: Nested Loop Join, Hash Join, and Merge Join
  • How `ANALYZE` builds MCV (Most Common Values) lists and histogram bounds in `pg_statistic`
  • Why Genetic Query Optimization (GEQO) activates on queries joining 12+ tables

Introduction & Core Concept

When you submit a SQL query, the database does not execute your SQL literally. The Cost-Based Optimizer (CBO) explores thousands of alternative execution trees and chooses the plan with the lowest estimated cost. Understanding how the optimizer calculates costs and picks join algorithms allows you to fix slow query plans with precision.
WHY DOES THIS MATTER IN THE REAL WORLD?

When table statistics become stale, the optimizer can miscalculate row estimates by 1,000,000x, picking a catastrophic Nested Loop join over a fast Hash Join and causing queries to take 10 minutes instead of 10 milliseconds.

Syntax & Structure

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT ...;
SET enable_nestloop = off;

Analyzing Optimizer Join Strategies with EXPLAIN BUFFERS

sql
sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 1. Execute Detailed Execution Plan with Memory Buffers
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    c.name AS course_name,
    COUNT(e.user_id) AS total_enrolled
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE c.category = 'Systems Engineering'
GROUP BY c.id, c.name;
 
-- Sample Output Analysis:
-- -> HashAggregate (Cost: 450.20..460.50 Rows: 150)
--      Buffers: shared hit=42
--      -> Hash Join (Cost: 120.00..420.00 Rows: 12000)
--           Hash Cond: (e.course_id = c.id)
--           -> Seq Scan on enrollments e (Cost: 0.00..250.00 Rows: 15000)
--           -> Hash (Cost: 110.00..110.00 Rows: 800)
--                -> Seq Scan on courses c (Filter: category = 'Systems Engineering')
 
-- 2. Inspecting PostgreSQL Optimizer Statistics
SELECT 
    tablename, 
    attname, 
    n_distinct, 
    correlation 
FROM pg_stats 
WHERE tablename = 'courses';

Line-by-Line Technical Breakdown

1Join Strategy Decision Matrix: 1. Nested Loop Join is optimal when one table is tiny (<100 rows) and the inner table has an index lookup. 2. Hash Join is optimal for large unsorted datasets that fit in `work_mem`. 3. Merge Join is optimal when both inputs are already sorted by the join key.

Try It Yourself (Interactive Editor)

Modify the code in real-time and click Run to test live browser output and console logs.

Intelligent Code Runner & Live Sandbox[SQL]
SQL SOURCE EDITOR
Interactive Live Code

Common Mistakes & How to Avoid Them

#1: Leaving `random_page_cost` at default 4.0 on modern NVMe SSD cloud storage.

A high random_page_cost discourages the optimizer from using indexes, causing it to incorrectly prefer slow full table sequential scans.

Incorrect / Antipattern
SET random_page_cost = 4.0; -- Default for legacy magnetic spinning hard drives
Correct / Professional Solution
SET random_page_cost = 1.1; -- Accurate for NVMe SSD / EBS gp3 storage

Industry Best Practices & Professional Standards

  • Set `random_page_cost = 1.1` on SSD environments to encourage index usage.
  • Increase `work_mem` for analytical queries to allow Hash Joins to fit entirely in RAM.
  • Run `ANALYZE` after large batch insertions to update `pg_statistic` histogram buckets.

Lesson Summary & Core Takeaways

  • Cost-Based Optimizer models CPU and I/O costs to choose execution trees.
  • Nested Loop, Hash Join, and Merge Join each suit specific data distributions.
  • `EXPLAIN (ANALYZE, BUFFERS)` reveals exact memory cache hits and execution timings.
Claim Free Developer Cloud Credits & VPS Perks
Claim Rewards