Cost Based Optimizer

Hook: The optimizer is like a smart shopper: it compares several ways to answer the same query and picks the cheapest-looking basket, not the fanciest shelf.

Question: What is a cost based optimizer in SQL?

Answer: A cost based optimizer is the part of the database that chooses the best execution plan for a query by estimating how much work each plan will do. It looks at table statistics, indexes, and join options, then picks the plan with the lowest estimated cost. This is why the same SQL can run very differently depending on data size, data shape, and index design.

Interview-Ready Answer: I think of the cost based optimizer as the database’s planner. When I send a query, it estimates how many rows each step will touch, compares options like sequential scans, index scans, and join orders, and chooses the plan with the lowest estimated cost. The key detail is that cost is based on statistics, so stale stats or a non-sargable predicate can make the optimizer choose a bad plan.

What it is

A cost based optimizer is the database engine’s decision-maker. It does not blindly follow one fixed rule; it estimates the work for several possible plans and chooses the cheapest one. The word cost is important: this is usually an internal score, not wall-clock time in milliseconds.

Two core ideas drive the choice:

• Cardinality = the estimated number of rows that survive a filter or join.
• Selectivity = the fraction of rows that match. Low selectivity usually means fewer rows, which makes an index more attractive.

For example, if a table has 10 million orders and only 500 rows have status = 'pending', the optimizer may prefer an index. If 9 million rows match, a sequential scan can be cheaper because reading the table once is faster than bouncing through the index and then fetching almost every row anyway.

How it works under the hood

1. Read metadata and statistics. The optimizer checks table size, row counts, value distribution, null rates, and available indexes. In PostgreSQL, statistics are gathered by ANALYZE; the default default_statistics_target is 100.
2. Estimate cardinality. It predicts how many rows each filter will keep. Good estimates matter because a small mistake early can change the whole plan.
3. Generate candidate plans. It considers access paths such as sequential scan, index scan, bitmap scan, and different join orders and join types.
4. Score each plan. The planner assigns cost using CPU work, disk I/O, memory use, and sometimes network or temp spill costs. A bitmap scan means the engine uses an index to find matching rows, then batches table page reads to reduce random I/O.
5. Pick the lowest estimated cost. The chosen plan is not always the fastest in real life; it is the one that looks cheapest based on the estimates.
6. Execute the plan and rely on stats quality. If stats are stale, skewed, or incomplete, the real runtime can be very different from the estimate.

Rule-based vs cost-based

Aspect	Rule-based	Cost-based
Decision style	Fixed rules	Estimate costs
Index use	Pattern driven	May skip index
Input needed	Rules only	Stats + indexes
Weak spot	Rigid plans	Bad stats

The big win of cost based planning is flexibility. The same query can choose a different plan as the table grows, the data distribution changes, or a new index appears.

Why indexes matter

An index gives the optimizer another access path. But an index is only useful when the predicate is sargable (search-argument-able), meaning the database can use the column directly to search efficiently. A filter like created_at >= '2025-02-01' is friendly to an index. A filter like DATE(created_at) = '2025-02-01' often hides the column inside a function, which can block a normal index unless you create an expression index.

Performance notes and real numbers

• Planning time is usually tiny for simple queries, often under a few milliseconds, but it can rise for queries with many joins because the search space grows very fast.
• The number of possible join orders grows combinatorially, so real optimizers prune the search instead of trying every possibility.
• An index is often a win when it matches a small fraction of rows, but there is no universal cutoff like 10% or 20%. Table size, row width, caching, and clustering all matter.
• Sequential scans are not bad; they are often the right choice for full-table reads or very low-selectivity filters because disk access is more sequential and predictable.

Important edge cases

• Stale statistics: If the table changed a lot since the last ANALYZE, the optimizer may guess badly.
• Skewed data: One value may be rare while another is common. If the histogram misses that skew, plan choice can be wrong.
• Functions and casts: Wrapping an indexed column in a function may make it non-sargable.
• Composite indexes: The leftmost column matters. An index on (status, created_at) helps when you filter by status first.
• Cost is relative: A plan with lower estimated cost is not guaranteed to be faster in wall-clock time.

Memory hook: Think “GPS with live traffic.” The optimizer does not just know the roads (rules); it checks the traffic report (statistics) and chooses the cheapest route it can see.

Real-World Story: In a checkout service, the team had an orders table with tens of millions of rows. A dashboard query filtered by status and recent created_at values, but a developer wrapped the timestamp in DATE() to make the SQL look simpler. The cost based optimizer could no longer use the normal index well, so it switched to a sequential scan.

What happened in production? p95 latency jumped from tens of milliseconds to multiple seconds, the database CPU spiked, and the slow query log started showing Seq Scan on orders with millions of rows removed by filter. Users saw the checkout page spinning, order confirmations arriving late, and support tickets saying recent orders were missing from the admin view.

The fix was simple but important: rewrite the predicate to a half-open range, keep the column bare, and let the optimizer do its job. If the business truly needed DATE(created_at), the team could also add an expression index. The lesson was that the optimizer is smart, but it can only reason about what the SQL exposes.

Follow-up & Tricky Questions:

• How does the optimizer estimate row counts? It uses table statistics such as histograms, distinct counts, and null fractions to predict how many rows survive each filter. Better stats usually mean better plans.
• Why would it choose a sequential scan when an index exists? Because an index is not automatically cheaper. If many rows match, reading the table once can cost less than lots of random index lookups plus table fetches.
• What is the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN shows the estimated plan; EXPLAIN ANALYZE actually runs the query and reports real timing and row counts. That comparison is one of the fastest ways to spot bad estimates.
• How do composite indexes affect costing? The leading column matters a lot. An index on (status, created_at) is great for status = ... plus a date range, but weak for filtering only on created_at.
• How do stale statistics hurt performance? The optimizer can think a rare value is common, or vice versa. That can flip the plan from an index scan to a table scan and cause a sudden slowdown after data growth.
• Does adding more indexes always help? No. Every index adds write overhead and storage cost, and the optimizer may still choose a scan if the index is not selective enough.
• Is cost the same as execution time? No. Cost is an internal score built from model assumptions. Two plans with similar cost can still behave differently on real hardware or cached data.
• Can a function on a column break index usage? Yes, because it can hide the raw column from the index. A rewrite to a range predicate or an expression index is often the fix.

Common Mistakes:

• Thinking indexes always win. Correction: for large matches, a sequential scan can be cheaper and faster.
• Reading estimated cost as time. Correction: cost is a unitless planner score, not milliseconds.
• Ignoring statistics. Correction: run ANALYZE or rely on autovacuum so the optimizer sees current data shape.
• Writing non-sargable predicates. Correction: keep the column bare when possible, or add an expression index if that expression is truly needed.

Memory Hook: GPS with live traffic. The optimizer is not a rule book; it is a route planner that checks traffic stats before choosing the cheapest road.

Cheat Sheet:

• Cost based optimizer picks the lowest estimated-cost plan.
• It uses statistics: row counts, distributions, and selectivity.
• Indexes help most when predicates are selective and sargable.
• Bad stats can mean bad plans.
• Seq scan can be the right answer for broad filters.
• Use EXPLAIN to inspect the chosen plan.

Practice Tasks:

• Run the code, compare the EXPLAIN output before and after the expression index.
• Change the pending ratio in the data and see when the planner starts preferring a sequential scan.
• Add a second filter column and observe how the composite index changes the plan choice.