CSE Roadmap

Level 6 of 13 · about 2 weeks

Query processing and optimization

The stages between a SQL statement and a result set: turning SQL into a query plan, the different join algorithms an optimizer can pick, sorting costs, cost-based optimization and why its estimates can be wrong, writing index-friendly queries, pagination that doesn't slow down on later pages, and materialized views.

Modules

Core: everyone needs it. Common: most jobs use it. Specialist: optional depth, safe to skip on a first read.

  1. 1

    From SQL to a query plan

    the stages a query statement passes through before any row comes back

    Common 5 parts · about 2 hours

  2. 2

    Join algorithms

    three different ways to combine two tables, and when the optimizer reaches for each

    Core 5 parts · about 2 hours

  3. 3

    Sorting and the cost of ORDER BY

    when a sort is nearly free, and when it becomes one of the most expensive parts of a query

    Common 5 parts · about 2 hours

  4. 4

    Cost-based optimization

    how the optimizer estimates which physical plan is cheapest, and why those estimates can be wrong

    Common 5 parts · about 2 hours

  5. 5

    Writing queries the optimizer can use

    small changes in how a condition is written that decide whether an index can even be considered

    Core 5 parts · about 2 hours

  6. 6

    Pagination done right

    why OFFSET gets slower on every later page, and what to use instead

    Core 5 parts · about 2 hours

  7. 7

    Materialized views

    precomputing an expensive query's result once, and deciding how often to recompute it

    Common 5 parts · about 2 hours