CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Name what the optimizer relies on instead of live data (15 minutes)

State what the optimizer relies on to estimate cost, instead of scanning the table's live data each time.

Done when: periodically collected statistics are correctly named.

Hint

Periodically collected statistics about the data.

Easy 2: Name what a selectivity estimate predicts (15 minutes)

State what a selectivity estimate predicts.

Done when: how many, or what fraction, of a table's rows a condition will match is correctly described.

Hint

How many, or what fraction, of a table's rows a condition is expected to match.

Easy 3: Name one reason the estimate can be wrong (15 minutes)

Name one concrete reason a cost estimate can be inaccurate.

Done when: any one of stale statistics, column correlation, or data skew is correctly named.

Hint

Stale statistics, column correlation, or data skew (any one is sufficient).

Medium

Medium 1: Connect selectivity to plan choice (25 minutes)

Explain why a highly selective condition, matching very few rows, tends to favor an index lookup over a full scan.

Done when: the explanation correctly notes an index lookup's cost for very few matching rows is much lower than scanning the entire table, while a full scan's cost stays roughly the same regardless of how many rows match.

Hint

An index lookup stays cheap when very few rows match, while a full scan's cost is roughly the same no matter how selective the condition is, so selectivity tips the balance toward the index.

Medium 2: Explain why stale statistics mislead the optimizer (25 minutes)

Explain how statistics collected before a large batch import can lead to a worse plan choice afterward.

Done when: the explanation correctly notes the optimizer bases its row-count and distribution estimates on the outdated statistics, potentially choosing a plan suited to the old, smaller data rather than the new, larger reality.

Hint

The optimizer still estimates based on the outdated, smaller statistics, so it may choose a plan that was appropriate for the old data size but not the new, larger one.

Medium 3: Explain data skew's effect on a specific query (30 minutes)

A status column is assumed to be evenly distributed across three values, but in reality, ninety percent of all rows have status = 'delivered'. Explain how this skew could mislead a query filtering WHERE status = 'delivered'.

Done when: the explanation correctly notes the optimizer might underestimate how many rows actually match, since it assumes a roughly even one-third distribution, potentially choosing an index lookup when a full scan would actually have been cheaper for this specific, highly common value.

Hint

The optimizer, assuming a roughly even split, underestimates how many rows actually have status = 'delivered', potentially picking an index lookup when, given the true ninety percent concentration, a full scan would actually have been cheaper.

Hard

Hard 1: Diagnose a bad plan from an unexpectedly high row count (30 minutes)

An execution plan chooses a nested loop join expecting a small number of matching rows, but at actual execution, far more rows matched than estimated, making the query much slower than expected. Explain the most likely root cause.

Done when: the explanation correctly identifies the optimizer's selectivity estimate as too low, likely from stale statistics, undetected column correlation, or data skew, causing it to choose a nested loop join that is efficient only for a much smaller row count than actually occurred.

Hint

A selectivity estimate that was too low, likely due to stale statistics, unaccounted-for column correlation, or data skew, led the optimizer to pick a nested loop join suited to far fewer matching rows than actually existed.

Hard 2: Explain why column correlation is hard for per-column statistics to capture (35 minutes)

Explain, using city and postal_code as an example, why statistics collected separately per column can misjudge the selectivity of a condition that filters on both columns together.

Done when: the explanation correctly notes that per-column statistics do not record how values in different columns move together, so a condition on both columns might match far fewer rows than multiplying their individual selectivities would suggest, since most postal codes are tied to a specific city.

Hint

Per-column statistics treat city and postal_code independently, missing that postal codes are tied to specific cities, so multiplying their individual selectivities overstates how rare the combined condition really is.

Hard 3: Propose a response to a recurring bad-plan problem (40 minutes)

A specific query reliably gets a poor execution plan after any large data load, but runs fine again shortly after. Propose a likely cause and a reasonable response.

Done when: the proposed cause correctly identifies stale statistics immediately following the large data load as the likely culprit, with a reasonable response involving refreshing statistics, such as running ANALYZE, promptly after large data loads rather than waiting for the next scheduled refresh.

Hint

The likely cause is statistics that have gone stale immediately after the large load; refreshing statistics with a command such as ANALYZE right after large data loads, rather than waiting for the next scheduled refresh, is a reasonable response.