Y

SQL · Advanced · 4 h

Query plans and performance

Read a query plan, choose indexes and partitions, and stop scanning data you do not need.

Why employers care: On warehouses you pay per byte scanned. Mid-level roles expect you to keep queries fast and cheap.

In the roadmaps for: Data Analyst, Data Engineer, Software Engineer (Backend)

Lessons, in order

  1. SQL EXPLAIN: read query plans and use indexesRead real PostgreSQL query plans, turn a full scan into an index lookup, fix filters that silently skip the index, and catch statistics that are off by 335 times.18 min
  2. SQL composite indexes and covering indexesCompare composite index column orders on real data, apply the leftmost prefix rule, build covering, partial and expression indexes, and measure what indexes cost on writes.24 min
  3. Hash join vs nested loop vs merge joinHow databases join tables with nested loops, hash joins and merge joins, how to read each one in a PostgreSQL plan, and how a bad row estimate picks the wrong one.23 min
  4. SQL query optimization: rewrite slow queriesFind the queries that cost the most with pg_stat_statements, then speed them up with six proven rewrites, each measured before and after and checked for the same answer.27 min
  5. SQL isolation levels, locks and deadlocksReproduce lost updates, non-repeatable reads, write skew and deadlocks with two PostgreSQL sessions, then fix each with the right isolation level or lock.26 min