Query Plan

SQL is declarative: you describe the result you want, and the database's optimizer decides *how* to get it. It compiles your query into a plan — a tree of operators like scan, filter, join, and sort — and runs it bottom-up, each operator feeding rows to its parent. The plan that touches the fewest rows wins.

A query to plan

SELECT o.id, c.name
FROM customers c
JOIN orders o ON o.cust_id = c.id
WHERE c.city = 'Paris';

-- customers: 10,000 rows, indexes on (city) and (id)
-- orders:  1,000,000 rows, index on (cust_id)
-- about 50 customers live in Paris; ~100 orders each

Two candidate plans

1. 1Plan A (index nested loop). Index Scan customers on city='Paris' -> 50 rows. For each, Index Scan orders on cust_id -> ~100 rows. Rows touched ≈ 50 + 50×100 = 5,050.
2. 2Plan B (scan + hash join). Seq Scan all 10,000 customers, filter to 50, then hash-join against a Seq Scan of all 1,000,000 orders. Rows touched ≈ 10,000 + 1,000,000 = 1,010,000.

Both return identical rows. Plan A touches roughly 200× fewer — *because* the small, selective filter on city runs first and an index turns the join into cheap point lookups. So the optimizer picks it.

Chosen plan (read bottom-up):

Nested Loop  (rows=5000)
  ->  Index Scan on customers  (city = 'Paris', rows=50)
  ->  Index Scan on orders     (cust_id = c.id, rows=100 per loop)