Course Overview
Slow queries cost money, fail interviews, and get flagged in code review. This course gives you 300 practice questions built around the SQL performance skills that actually come up in senior engineering roles and production incidents.
The six tests cover the full optimisation stack, from fundamentals to expert-level plan reading:
Test 1 covers how databases actually execute queries: the parser, planner, and execution engine. You will understand what the cost model is, how statistics drive plan choice, and why the same SQL can run in 2ms one day and 2 minutes the next.
Test 2 covers indexes: B-tree, GIN, partial, composite, expression, and covering indexes. You will learn which index type fits which query pattern and the common mistakes that make indexes invisible to the planner.
Test 3 covers join optimisation. Nested loop, hash join, merge join, semi-joins, anti-joins, LATERAL, and multi-table join order. You will learn when each strategy wins and how to spot the ones causing fan-out disasters in production.
Test 4 covers aggregations, window functions, and subquery patterns. HashAggregate versus GroupAggregate, COUNT DISTINCT at scale, ROW_NUMBER versus DISTINCT ON, correlated subquery rewrites, and CTE inlining behaviour in PostgreSQL 12 and later.
Test 5 covers anti-patterns and rewrites. Leading wildcard LIKE, implicit type casts, functions on indexed columns, OFFSET pagination, NULL comparison mistakes, bulk DELETE performance, and N+1 queries. Each question presents a real-world slow query and asks you to identify the cause and the fix.
Test 6 covers reading and interpreting EXPLAIN ANALYZE output. You will learn to read plan nodes bottom-up, calculate total node work from loops and actual time, diagnose spills to disk from Batches and Sort Method, identify cardinality errors from estimated versus actual row counts, and distinguish lock waits from I/O bottlenecks.
Every question is scenario-based: a realistic production situation, four plausible answers, and a detailed explanation that goes beyond the correct answer to explain why each wrong answer fails.
FAQ
FAQ area empty
Course curriculum
-
Parser, Planner & Execution Engine
0 Xem trước -
Indexes: Types, Selection & Common Mistakes
0 Xem trước -
Join Optimisation & Execution Strategies
0 Xem trước -
Aggregations, Window Functions & Subquery Patterns
0 Xem trước -
Anti-patterns & Rewrites
0 -
Reading & Interpreting Query Execution Plans
0 Xem trước
Requirment
-
You can write basic SELECT queries with WHERE, JOIN, GROUP BY, and ORDER BY. No experience with query optimisation or EXPLAIN is needed.
Outcomes
-
Diagnose slow queries using EXPLAIN ANALYZE: read plan nodes, spot cardinality errors, identify the slow node, and interpret buffer and timing data.
-
Recognise and fix the most costly SQL anti-patterns: leading wildcards, implicit type casts, functions on indexed columns, and OFFSET pagination.
-
Choose the right join strategy, aggregation method, and index type for a given query and explain why the planner picks one plan over another.
-
Rewrite inefficient queries using window functions, CTEs, cursor pagination, conditional aggregation, and semi-join patterns that scale to hundreds of millions
0 Student
1 Course
0 review