SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Performance and Query Optimization

Sooq Analytics' event store has 300,000 events, 100,000 orders and 20,000 users, and apart from primary keys nothing is indexed. In this track you find out why its dashboards are slow and fix them. You learn how PostgreSQL turns SQL into a plan, read EXPLAIN and EXPLAIN ANALYZE output node by node, build B-tree, composite, partial and expression indexes, and learn when an index can't help: low selectivity, functions on columns, leading wildcards. You then weigh what indexes cost, recognise nested-loop, hash and merge joins, rewrite queries into faster equivalents, and finish with a troubleshooting checklist applied to realistic incidents. Everything runs on real PostgreSQL 18 in your browser. Timings there are not production benchmarks, so exercises grade results and plan shapes, never milliseconds.

Start the track

Lessons

  1. How PostgreSQL executes a queryThe difference between what your SQL means and what the database physically does to answer it.18 min · 4 exercisesFree
  2. Reading EXPLAIN plansRead a plan tree from the bottom up: node types, costs, row estimates and the lines underneath each node.22 min · 3 exercises Members
  3. EXPLAIN ANALYZE: what really happenedRun a query under measurement and compare the planner's estimates with actual rows, loops and filters.22 min · 3 exercises Members
  4. B-tree indexesHow a B-tree index finds rows in a few page reads, and how to create, inspect and drop one.24 min · 4 exercises Members
  5. Composite indexes and column orderIndexes on several columns: how they are sorted, the leftmost-prefix rule, and how to choose the column order.24 min · 4 exercises Members
  6. Selectivity: when an index pays offWhy an index helps for rare values and not for common ones, how the planner estimates that, and how partial indexes target the rare rows.22 min · 3 exercises Members
  7. When indexes don't helpFunctions on columns, casts, arithmetic, leading wildcards and other patterns that hide a column from its index, and how to fix each.24 min · 4 exercises Members
  8. What indexes costEvery index slows writes and takes space. Learn to weigh that cost, and to find duplicate, redundant and unindexed foreign keys.20 min · 3 exercises Members
  9. Join strategies: nested loop, hash and mergeThe three ways PostgreSQL can join two inputs, when each one wins, and how indexes and row estimates steer the choice.24 min · 3 exercises Members
  10. Rewriting queries for performanceEquivalent queries can have very different plans: sargable filters, EXISTS instead of joins for existence, pre-aggregation, fewer columns, and keyset pagination.26 min · 4 exercises Members
  11. Troubleshooting slow queriesA repeatable checklist for diagnosing a slow query, from measuring it to verifying the fix, applied to four real-world cases.28 min · 4 exercises Members