Execution Plans & Cost Estimation

-- EXPLAIN shows the plan the optimiser CHOSE — it does not run the query.
EXPLAIN
SELECT name, salary
FROM employees
WHERE salary > 70000;

-- EXPLAIN ANALYZE actually RUNS the query and adds the real timings,
-- so you can compare what the optimiser guessed against what happened.
EXPLAIN ANALYZE
SELECT name, salary
FROM employees
WHERE salary > 70000;

-- Read a plan bottom-up and inside-out: the most indented node runs first,
-- and its rows flow up into the node above it.

-- The SAME query can be answered two very different ways.

-- (1) No useful index on salary -> the engine reads every row:
EXPLAIN SELECT * FROM employees WHERE salary > 70000;
-- Plan: Seq Scan on employees  (cost=0.00..1850.00 rows=1200 width=64)
--         Filter: (salary > 70000)
-- "Seq Scan" = sequential scan = read the whole table, throw away non-matches.

-- (2) Add an index that matches the predicate:
CREATE INDEX idx_emp_salary ON employees (salary);

EXPLAIN SELECT * FROM employees W
...

-- Every node is labelled cost=startup..total  rows=estimate  width=bytes.
EXPLAIN SELECT * FROM orders WHERE total > 500;
-- Seq Scan on orders  (cost=0.00..1250.00 rows=5000 width=64)
--   Filter: (total > 500)
--
-- cost=0.00..1250.00  -> 0.00 to return the FIRST row, 1250.00 for ALL rows.
-- rows=5000           -> the optimiser's GUESS of how many rows match.
-- width=64            -> average bytes per row (drives memory + I/O estimates).

-- Those numbers are abstract "cost units", not mill
...

-- EXPLAIN ANALYZE prints BOTH the estimate and the reality. Compare them.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Seq Scan on orders
--   (cost=0.00..1850.00 rows=200 width=72)            <- estimated 200 rows
--   (actual time=0.05..6.90 rows=48000 loops=1)       <- ACTUALLY 48000 rows
--   Filter: (status = 'shipped')
--   Rows Removed by Filter: 12000
--
-- estimate 200 vs actual 48000 = a 240x miss. The optimiser thought almost
-- nothing matched, so it skipped the
...

-- A JOIN can be executed three ways. The optimiser picks based on table
-- sizes, indexes and available memory. Learn to recognise each in a plan.

EXPLAIN ANALYZE
SELECT c.name, o.total
FROM customers c                       -- small: ~1,000 rows
JOIN orders o ON c.id = o.customer_id  -- large: ~1,000,000 rows
WHERE o.total > 100;

-- You will see ONE of these as the join node:
--
-- Nested Loop      -> for each outer row, look up matches in the inner table.
--                     Great when t
...

-- Five rewrites that routinely turn a slow plan into a fast one.

-- 1) SARGABLE PREDICATE — no function on the indexed column.
-- ❌ index on hire_date cannot be used:
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- ✅ bare column + range -> index usable:
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

-- 2) SELECT ONLY THE COLUMNS YOU NEED — lets an index "cover" the query.
-- ❌ forces a trip back to the table for every row:
SELECT * FROM orders W
...

-- The optimiser's row estimates come from STATISTICS it samples per table:
-- how many rows, how many distinct values, the most common values, histograms.
-- If the data changed a lot since the last sample, the estimates go stale and
-- the plans go wrong (remember the 200-vs-48000 miss above).

-- PostgreSQL: refresh statistics by hand (autovacuum also does this for you):
ANALYZE orders;

-- See when each table was last analysed and how big it is:
SELECT relname, last_analyze, last_autoanalyze
...

-- "Hints" let you override the optimiser. SYNTAX VARIES BY ENGINE — there is
-- no standard, so a hint that works on one database is ignored or rejected on
-- another. Reach for them only after fixing indexes, predicates and statistics.

-- PostgreSQL has no inline hints; you nudge the planner with session flags:
SET enable_seqscan = off;   -- discourage sequential scans for this session
EXPLAIN SELECT * FROM employees WHERE salary > 70000;  -- see what it picks now
SET enable_seqscan = on;    
...