# Query Optimization (Part 2: Statistics and Common Fixes)

## 1. The Planner's Decisions Depend on Statistics, Not Magic

Part 1 covered *reading* a plan. This part covers *why* the planner picked what it picked, and the concrete moves that change its mind. Every cost estimate in `EXPLAIN`'s output is derived from **table statistics** — row counts, distinct-value counts per column, and value-frequency histograms — that the engine maintains separately from the actual data, via `ANALYZE`:

```sql
ANALYZE orders;   -- refresh statistics for the orders table
```

If statistics are stale (a table grew 100x since the last `ANALYZE`, or its data distribution changed drastically), the planner's cost estimates can be badly wrong — leading it to choose a sequential scan when an index would clearly win, or the reverse. Most engines auto-run `ANALYZE` periodically (PostgreSQL's autovacuum daemon does this), but a bulk load or bulk delete can outpace that and warrants a manual `ANALYZE` immediately after.

## 2. Diagnosing a Missing Index

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
```
```
Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=64) (actual time=0.02..8.41 rows=11 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 49989
```

`Rows Removed by Filter: 49989` is the tell — the engine read 50,000 rows to find 11 matches. That's exactly the shape of query `01-database_indexes/` section 1 uses to motivate indexing in the first place. Adding `CREATE INDEX idx_orders_customer ON orders (customer_id);` and re-running would flip this to an `Index Scan` with a dramatically smaller cost, as shown in part 1 section 2.

## 3. The Correlated Subquery Anti-Pattern

```sql
-- SLOW: runs the subquery once PER ROW of the outer query
SELECT c.name,
       (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
```

A **correlated subquery** references a column from the outer query, which forces the engine to re-evaluate it once per outer row — for 10,000 customers, that's 10,000 separate subquery executions. Rewriting as a `JOIN` with `GROUP BY` lets the engine compute the aggregate in one pass:

```sql
-- FAST: one pass, one aggregation
SELECT c.name, count(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
```

## 4. `SELECT *` and Why It Costs More Than It Looks Like

```sql
SELECT * FROM orders WHERE customer_id = 42;          -- fetches every column
SELECT id, status FROM orders WHERE customer_id = 42;  -- fetches only what's needed
```

Beyond the obvious network/serialization cost of columns nobody uses, `SELECT *` also **defeats index-only scans** (part 1 section 2) — if a covering index exists for `(customer_id) INCLUDE (status)`, only the narrower query can be answered from the index alone; `SELECT *` forces a trip back to the full table row no matter what indexes exist, because some selected column isn't in the index.

## 5. `LIMIT` Without `ORDER BY` Is a Trap, Not an Optimization

```sql
SELECT * FROM orders WHERE status = 'pending' LIMIT 10;   -- "fast" but arbitrary 10 rows
```

`LIMIT` alone can make a query *look* fast (fewer rows returned) without actually making the underlying scan cheaper — and worse, without `ORDER BY`, which 10 rows you get is unspecified and can change between runs. `LIMIT` only helps performance meaningfully when paired with an index that lets the engine stop scanning early (e.g. `ORDER BY created_at DESC LIMIT 10` backed by an index on `created_at`, which can walk the index from one end and stop after 10 matches — see `02-b_tree/` section 4 on sorted leaf traversal).

## 6. Common Pitfalls

1. **Tuning against stale statistics** — always `ANALYZE` after a bulk load/delete before trusting a plan's cost estimates.
2. **Adding an index and not verifying it's actually used** — `EXPLAIN` after every index change; the planner may still prefer a sequential scan if the table is small enough, or if the filter isn't selective enough to justify the index lookup overhead.
3. **Optimizing a query that runs once a day while ignoring one that runs 10,000 times a minute** — profile by total impact (frequency × cost), not by which single query looks slowest in isolation.
4. **Rewriting a correlated subquery into a JOIN without checking for row duplication** — a naive `JOIN` can multiply rows if the joined table has multiple matches per outer row; `GROUP BY`/aggregation (section 3) or a `LEFT JOIN LATERAL` needs to be chosen deliberately, not just "a JOIN instead of a subquery."

## 7. Quick Reference

```
ANALYZE table  -> refresh planner statistics; run after bulk load/delete
Rows Removed by Filter: N  -> the sequential-scan smoking gun; add an index

Correlated subquery (references outer row) -> re-evaluated PER ROW -> rewrite as JOIN+GROUP BY
SELECT *  -> defeats index-only scans, ships unused columns -> select only needed columns
LIMIT without ORDER BY -> arbitrary rows, not a real optimization on its own

Diagnostic loop: EXPLAIN ANALYZE -> spot the expensive node -> fix (index / rewrite / ANALYZE)
                 -> EXPLAIN ANALYZE again -> confirm the plan actually changed
```