Overview

An uncorrelated subquery references nothing from the outer query, so the engine can evaluate it exactly once and reuse the result for every outer row; a correlated subquery references a column from the outer query's current row, so conceptually it runs once per outer row (though the planner can often rewrite it into a join in practice). A subquery used AS a table in FROM (a derived table) is the same idea applied to a whole result set instead of a single scalar value (Example 1 contrasts an uncorrelated AVG(price) subquery against its CROSS JOIN derived-table equivalent; Example 29 rewrites a correlated subquery as a join).

A CTE names an intermediate result once, and later parts of the same query can reference that name directly instead of the subquery being repeated verbatim wherever it's needed -- a deeply nested query becomes a sequence of named, readable steps instead of one large expression read inside-out (Example 4 factors one filter into a CTE; Example 5 chains several CTEs, each building on the previous step's named result).

An anchor (non-recursive) term establishes the starting rows, and a recursive term repeatedly joins the working table back onto itself, adding new rows until a pass produces zero new rows. Over a graph that contains a cycle, the engine itself does not detect the cycle for you -- only an explicit guard in the RECURSIVE term (typically excluding any node already on the current path) stops the recursion; without one anywhere in the query, nothing stops it (Example 6's counter; Example 30's cycle-safe graph walk; Kata 2 reproduces exactly what happens when that guard is missing entirely).

OVER() computes a value across a set of related rows (its "window") WITHOUT collapsing those rows into one output row the way GROUP BY does -- every input row keeps its own row in the result, now carrying a value computed across its window (a running total, a rank, a neighboring row's value) (Example 8's running total keeps all 5 input rows; Example 9's partitioned average attaches the group average to every row in that group, unlike a GROUP BY that would collapse them).

PARTITION BY restricts the window to rows sharing the same partition key (like a per-group GROUP BY, but without collapsing rows); ORDER BY establishes a sequence within each partition; and an explicit frame (ROWS/RANGE BETWEEN ...) narrows exactly which rows within that ordered partition the function actually sees, from "just this row" to "every row so far" to a custom sliding window. RANGE (the default frame mode) treats tied ORDER BY peers as arriving together; ROWS counts physical rows one at a time regardless of ties (Example 33 shows the two frame modes diverge on tied data; Kata 3 reproduces the double-counted running total the default RANGE frame produces on ties).

ROW_NUMBER() assigns a unique, strictly increasing integer to every row regardless of ties (an arbitrary but deterministic tiebreak given a fully-specified ORDER BY); RANK() gives tied rows the SAME rank and then skips the following rank numbers by the tie's size (1, 2, 2, 4); DENSE_RANK() also gives tied rows the same rank but never skips a number (1, 2, 2, 3) (Example 10 contrasts all three side by side; Example 36's top-N-per-group and Kata 4 both show why the choice between RANK() and ROW_NUMBER() changes how many rows a "top-N" filter actually returns when a tie lands at the boundary).

UNION combines two result sets' rows; INTERSECT keeps only rows present in BOTH; EXCEPT keeps rows present in the first but NOT the second. All three deduplicate their output by default (an implicit DISTINCT pass across the combined rows); appending ALL skips that deduplication, keeping every row including duplicates, which is also cheaper because no dedup pass runs at all (Example 13 measures the row-count difference UNION vs UNION ALL produces on data with real duplicates; Example 14 uses INTERSECT/EXCEPT to compare two sets directly).

ROLLUP adds progressively coarser subtotal rows on top of the normal per-(a, b) groups -- a subtotal per a (across every b), plus one grand-total row -- all in a single query, instead of requiring several separately-grouped queries UNIONed together. Because ROLLUP treats its argument list as a HIERARCHY read left to right, the column order determines which level each subtotal collapses at -- reversing the arguments produces subtotals at the WRONG level entirely (Example 15's category rollup; Example 16's more general GROUPING SETS for combinations ROLLUP can't express; Kata 5 reproduces the wrong-level subtotal a reversed column order produces).

A LATERAL subquery may reference columns from tables that appear EARLIER in the same FROM clause, effectively running once per row of that earlier table -- much like a correlated subquery, but explicitly written as a join. An ordinary (non-LATERAL) subquery in FROM is evaluated independently and can never see the other tables around it at all (Example 37 uses LATERAL for a per-row top-N; Example 38 directly contrasts the same query written as a correlated subquery versus LATERAL).

FILTER (WHERE cond) is dedicated PostgreSQL syntax that restricts which rows an aggregate consumes -- it reads as "aggregate this, but only over rows matching cond." A CASE WHEN cond THEN value END inside an aggregate instead controls what VALUE each row contributes (NULL for a non-match), which composes with any aggregate portably across engines but is easy to get subtly wrong -- an unnecessary ELSE can turn a NULL-skipping COUNT into a plain row count (Example 17's FILTER; Example 18's CASE-based pivot; Kata 6 reproduces exactly the ELSE 0 trap).

Atomicity guarantees a transaction's writes apply as a single all-or-nothing unit -- if any statement inside it fails (or ROLLBACK is issued), every write since BEGIN is undone, leaving no partial state a reader could ever observe. Ordinary statements run OUTSIDE a transaction each commit independently the instant they succeed, so a failure partway through a sequence of them leaves exactly the earlier ones applied and the rest not -- the very inconsistency atomicity exists to prevent (Example 19's BEGIN/COMMIT/ROLLBACK; Example 20 genuinely reproduces a mid-transaction failure and confirms every prior write in that same transaction rolled back too).

PostgreSQL never overwrites a row in place for an UPDATE -- it writes a NEW row version and marks the old one for eventual cleanup by VACUUM, so a reader whose snapshot started before a concurrent writer's commit keeps seeing the OLD row version it opened against, while the writer proceeds independently on its own new version, with neither one blocking the other for a plain read (Example 27's Read Committed default takes a fresh per-statement snapshot, the directly observable effect of MVCC).

Read Committed (the default) gives each individual STATEMENT its own fresh snapshot, so it never sees an uncommitted write, but two statements in the SAME transaction can see different committed data if a concurrent commit lands between them. Repeatable Read gives the whole TRANSACTION one snapshot taken at its first statement, so every statement inside it sees the same data throughout. Serializable adds SSI's dependency-tracking on top of Repeatable Read's snapshot, aborting a transaction whose committed effects could not have arisen from SOME serial (one-at-a-time) execution order (Example 27, Example 57, Example 77's full anomaly matrix side by side).

A non-repeatable read is re-running the same query in one transaction and seeing DIFFERENT already-committed data the second time (Read Committed allows this; Repeatable Read prevents it). A phantom read is a range query that returns a DIFFERENT SET OF ROWS the second time inside one transaction (PostgreSQL's Repeatable Read already prevents this -- stronger than the SQL standard technically requires). Write skew is two transactions each independently reading an overlapping condition, then each writing to a DIFFERENT row in a way that, combined, violates an invariant neither one's own write alone would have broken -- Repeatable Read does NOT prevent this (no single row is contested); only Serializable does (Example 57's non-repeatable read prevented; Example 58's phantom read prevented; Example 59's write skew, which slips through Repeatable Read).

SSI tracks "dangerous structures" -- specific patterns of read-write dependencies between concurrently committing transactions (a rw-antidependency cycle) that are a NECESSARY condition for a non-serializable outcome to occur; when it detects such a cycle forming, it aborts one of the participating transactions with a serialization_failure (SQLSTATE 40001) BEFORE the anomaly can land, rather than proving after the fact that one specific anomaly already happened (Example 59 shows the anomaly Repeatable Read misses; Example 60 shows Serializable catching the equivalent case and the retry loop an application needs).

FOR UPDATE takes an EXCLUSIVE row lock -- no other transaction can lock (or update/delete) that row until the holder commits or rolls back; FOR SHARE takes a weaker lock that lets other FOR SHARE readers proceed concurrently but still blocks a concurrent FOR UPDATE/write. An advisory lock is not tied to any row or table at all -- it is an application-defined lock (keyed by an arbitrary integer you choose) for coordinating something outside the data model itself, like ensuring only one instance of a scheduled job runs at a time (Example 26 proves a real block with lock_timeout; Example 63's advisory lock; Kata 7 reproduces the lost update a missing FOR UPDATE causes).

A deadlock requires a CYCLE of transactions each waiting on a lock the next one in the cycle already holds (A waits for a lock B holds, B waits for a lock A holds) -- PostgreSQL's deadlock detector checks for exactly this cycle (by default every deadlock_timeout, 1 second) and aborts one participant to break it. The one reliable prevention technique is enforcing a single, CONSISTENT lock-acquisition order across every transaction that touches the same set of rows -- if every transaction always locks row 1 before row 2, a cycle can never form in the first place (Example 61 reproduces a real deadlock with two threads locking in opposite order; Example 62 fixes it with consistent ordering).

A B-tree keeps its keys in SORTED order across a balanced tree structure, so it can binary-search down to any single key (equality), walk sequentially from any starting key to satisfy a range (BETWEEN, <, >), and its keys are already in the exact order an ORDER BY on that column would need. A hash index only maps a key to its location via a hash function, which tells you nothing about ordering or ranges, so it can only ever answer "does this EXACT value exist" (Example 21 creates a first B-tree index; Example 24 measures the Seq Scan-to-Index Scan flip it enables).

A composite B-tree is sorted first by its leading column, then by the next column WITHIN each leading-column value -- exactly like a phone book sorted by last name then first name. Searching by first name alone can't use that sort order to narrow anything, so a query that omits the leading column forces a scan of the whole index (or table) instead of a targeted search. INCLUDE appends extra columns to an index's LEAF pages without making them part of the sort key, so a query that only needs columns already present in the index (an "index-only scan") never has to visit the underlying table (heap) at all (Example 40's column-order lesson; Example 41's covering index; Kata 8 reproduces the exact leading-column mismatch).

A hash index is built for pure equality lookups on a column never queried by range or sort order (marginally smaller/faster than a B-tree for that ONE narrow case). GIN (Generalized Inverted Index) is built for "contains" queries over composite/multi-valued data -- a jsonb document's keys, an array's elements -- indexing every individual element rather than the whole value. BRIN (Block Range Index) is built for very large tables where a column strongly correlates with physical insertion order (a timestamp column on an append-only log) -- it stores only a min/max summary per block RANGE, staying tiny even over a huge table, at the cost of a coarser (less exact) filter (Example 44's hash index; Example 45's GIN over jsonb; Example 46's BRIN over a timeseries).

The planner only uses a partial index when it can PROVE the query's WHERE clause implies the index's own predicate -- in practice this generally means the two conditions have to match closely in their written form (or one is a trivially provable subset of the other). Two conditions that are merely LOGICALLY equivalent but spelled differently (status != 'closed' versus the index's status = 'open') are not recognized as equivalent, and the index is silently never even considered, with no error to flag the miss (Example 42's partial index; Example 43's expression index; Kata 9 reproduces the silent miss directly).

Every INSERT, UPDATE (of an indexed column), or DELETE has to update EVERY index on that table, not just the heap -- more indexes mean strictly more work per write, in both time and the storage the index itself occupies. Index bloat is dead index entries (pointing at row versions a prior UPDATE/DELETE already superseded) accumulating faster than autovacuum reclaims them, which grows the index's on-disk size and can slow lookups, without ever being visible in the row count itself (Example 47 measures the write-side slowdown directly; Example 48 observes bloat growing).

Plain EXPLAIN shows the planner's ESTIMATED plan and costs without running the query at all; EXPLAIN ANALYZE actually EXECUTES the query (and, by default in PostgreSQL 18, reports real buffer/IO stats alongside real row counts and timing) so you can compare the estimate against reality -- which means EXPLAIN ANALYZE on an UPDATE, DELETE, or a genuinely slow query really performs that write or that slow scan, not a simulation of it (Example 22's estimate-only EXPLAIN; Example 23's EXPLAIN ANALYZE; Example 52's Buffers output; Example 79's IO-tuning workflow).

Nested Loop scans the outer side once and re-probes the inner side per outer row (best when the outer side is small or the inner side has a fast index lookup, and PostgreSQL's Memoize node can cache repeated inner lookups). Hash Join builds an in-memory hash table from the smaller side once, then streams the larger side through it (best for equality joins with no useful index, given enough work_mem). Merge Join walks both already-sorted sides together (best when both inputs are already sorted, e.g. by an index, avoiding a separate sort step) (Example 49, Example 50, Example 51, each forcing one join type deterministically for teaching).

ANALYZE samples a table's rows and records per-column statistics -- a histogram of value distribution, the most-common values and their frequencies, an estimated distinct-value count, and the correlation between physical row order and logical column order -- all of which the planner's cost model reads to estimate how many rows a WHERE clause will match and pick a strategy sized to that estimate. Without it, the planner falls back to generic default selectivity constants that assume no skew at all, which can be wildly wrong for a column where one value is far more (or less) common than a uniform guess would assume (Example 25 refreshes stats after a bulk load; Example 53's skewed column shows the wrong-strategy consequence directly; Example 70's CREATE STATISTICS for correlated columns).

N+1 is issuing one query to fetch N parent rows, then, inside application code, ONE ADDITIONAL query PER PARENT to fetch that parent's related rows -- N+1 total round trips where the data could have been fetched in far fewer. A JOIN fetches every parent-child pair in exactly one round trip; a batched WHERE id IN (?, ?, ...) fetches every child row for the WHOLE current batch of parents in one additional round trip (two total) -- both replace "one query per parent" with "one query for the whole batch," they just differ in whether the result needs re-assembling in application code afterward (Example 54 reproduces N+1 with real Python timing; Example 55's JOIN fix; Example 56's IN (...) fix).

Denormalization duplicates data (or precomputes a derived value) to make reads cheaper, at the direct cost of write complexity (every write now also has to keep the duplicate in sync) and extra storage -- the same normalization-versus-denormalization trade this curriculum first named in SQL Essentials, now measured with real numbers. A plain VIEW stores nothing -- it re-runs its defining query fresh on every reference; a MATERIALIZED VIEW stores the query's RESULT as an actual on-disk snapshot, so reading it is cheap, but that snapshot goes stale the instant the underlying data changes and stays stale until an explicit REFRESH (Example 64's plain refresh; Example 74's denormalization measured in both directions; Example 75's non-blocking CONCURRENTLY refresh; Kata 10 reproduces the staleness directly).

Declarative partitioning splits one logical table into several physical child tables by a partition key (a date range, for instance); when a query's WHERE clause can only match rows from a KNOWN subset of partitions, the planner performs "partition pruning," skipping every non-matching partition's storage entirely rather than scanning (or even index-probing) rows it can prove in advance won't match. A fresh PostgreSQL connection pays real, measurable setup cost (a new backend process, auth negotiation) on every single request; a connection pool keeps a small set of connections open and hands them out for reuse, paying that setup cost once per pooled connection instead of once per request (Example 71's partitioning; Example 72's pruning; Example 76's pooling benchmark; Example 83's OLTP-vs-OLAP schema contrast).

This is RANK() used where the requirement is an exact row count (co-06) -- RANK() gives every row in a tie the SAME rank, so a tie landing exactly at the cutoff produces MORE rows than the cutoff allows. ROW_NUMBER() with a fully deterministic ORDER BY (adding a tiebreaker column) guarantees exactly N rows regardless of ties (Example 36; Kata 4).

This is write skew (co-14, co-15) -- each transaction's read (checking seats_left) and write (inserting its own booking row) touch DIFFERENT rows from the other transaction's, so no row-level conflict is ever detected under Repeatable Read. Only Serializable's SSI, which tracks the READ dependency between the two transactions (not just the writes), catches this class of anomaly (Example 59).

This is a stale-or-missing-statistics misestimate (co-23, co-24, co-25) -- the planner's ESTIMATE (shown by plain EXPLAIN) came from stats that no longer reflect the table's real distribution, so it picked a join strategy sized for the WRONG row count. Only EXPLAIN ANALYZE reveals the estimate-versus-reality gap directly, and running ANALYZE on the table (refreshing its stats) is the fix that lets the planner choose correctly next time (Example 53; Example 80).

An ordinary (non-LATERAL) subquery in FROM cannot see any other table in the same FROM clause at all -- it is evaluated completely independently. LATERAL (co-09) is the construct built specifically to let a subquery in FROM reference an earlier table's columns, running once per row of that earlier table (Example 37; Example 38).

This is the index cost tradeoff (co-22) -- every index on a table has to be updated on every write to that table, so adding an index that helps ONE read-heavy report imposes a real, ongoing tax on every insert, regardless of whether that insert ever touches the indexed column's read path (Example 47).

GROUPING SETS (co-08) computes an arbitrary LIST of independent grouping combinations -- including non-hierarchical ones a single ROLLUP can't express -- in one pass over the underlying data, instead of scanning it three separate times and combining the results afterward (Example 16).

This is the partition-vs-index bulk-delete tradeoff (co-28) -- a DELETE against an unpartitioned table has to find and remove matching rows (and every index entry pointing at them) one at a time, however many rows match. Dropping an entire PARTITION is a metadata-only operation against the catalog, not a row-by-row delete, because the partition's storage simply stops being attached to the parent table (Example 73).

EXCEPT (co-07) computes exactly "rows in the first set, not in the second" -- last_year EXCEPT this_year returns exactly the churned users, in one query, with no manual NOT IN/NOT EXISTS subquery required (Example 14).

This is a deadlock (co-17) -- the two transactions lock the same two rows in OPPOSITE orders, creating exactly the circular-wait condition PostgreSQL's deadlock detector exists to break by aborting one participant. The reliable fix is enforcing one CONSISTENT lock-acquisition order across every transaction that touches both accounts (e.g. always the lower account id first) (Example 61; Example 62).

A GIN index (co-20) indexes every individual key/element INSIDE a composite value like jsonb, rather than the value as a single opaque unit -- it is built exactly for "does this document contain X" queries a B-tree cannot answer efficiently (Example 45).

This is an OLTP-shaped schema being asked to do OLAP-shaped work (co-28) -- a normalized schema optimized for fast, narrow, single-row writes is exactly the wrong shape for wide analytical scans, which benefit from a denormalized, star-schema layout designed to minimize joins across millions of rows instead of minimizing update-time duplication (Example 83).

This is materialized-view staleness by design (co-27) -- a materialized view is a stored SNAPSHOT, current only as of its last REFRESH; if it refreshes hourly, the numbers it shows are, on average, about half that interval stale by construction, regardless of how current the base table is. The fix is either refreshing more frequently (trading more write cost) or clearly labeling the dashboard's own staleness window (Example 64; Example 75; Kata 10).

Root cause: x NOT IN (SELECT ...) desugars to x <> v1 AND x <> v2 AND ... for every value v the subquery returns. The moment ANY v is NULL, x <> NULL evaluates to NULL (unknown, per three-valued logic), and TRUE AND NULL is NULL -- so the WHOLE AND chain becomes NULL for every row, and WHERE discards a NULL result exactly like FALSE. NOT EXISTS never compares against the subquery's values directly; it only asks "did a matching row exist," which a NULL value simply never satisfies, leaving every genuinely-non-banned book correctly visible.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 id |        title
----+---------------------
  1 | Refactoring
  3 | Working Effectively
(2 rows)

Root cause: WITH RECURSIVE has NO built-in cycle detection -- the anchor term runs exactly once and can never usefully "guard" anything about steps taken later, so a guard written there (or omitted from the recursive term entirely) does nothing to stop the recursion. Moving NOT (e.to_node = ANY(w.path)) INTO the recursive term means every single step re-checks whether the candidate next node is already on ITS OWN path before following that edge, which is what actually breaks the cycle.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 node |  path   | depth
------+---------+-------
 A    | {A}     |     1
 B    | {A,B}   |     2
 D    | {A,D}   |     2
 C    | {A,B,C} |     3
(4 rows)

Root cause: RANGE (the default frame mode when only ORDER BY is given) defines "preceding" in terms of VALUE, not physical row position -- every row that TIES the current row's ORDER BY value is treated as arriving in the same instant, so both rows 2 and 3 (tied at 20.00) see the SAME frame, including each other. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead counts PHYSICAL rows one at a time regardless of value ties, with id added to the ORDER BY purely as a deterministic tiebreaker for which row counts as "current" first.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 id | amount | running_total
----+--------+---------------
  1 |  10.00 |         10.00
  2 |  20.00 |         30.00
  3 |  20.00 |         50.00
  4 |  30.00 |         80.00
(4 rows)

Root cause: RANK() (co-06) is defined to give every row in a tie the SAME rank number -- correct for "what place did this row finish in," but wrong for "give me exactly N rows," because a tie at the cutoff means MORE than N rows share a rank <= N. ROW_NUMBER() assigns a strictly unique, ever-increasing number regardless of ties, so rn <= 2 is guaranteed to return exactly 2 rows per partition; adding id to the ORDER BY makes WHICH of the tied books wins the 2nd slot deterministic and reproducible instead of arbitrary.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 author_id |        title         | price | rn
-----------+----------------------+-------+----
         1 | Refactoring          | 45.00 |  1
         1 | Domain-Driven Design | 40.00 |  2
(2 rows)

Root cause: ROLLUP(a, b) treats its argument list as a HIERARCHY read left to right -- subtotals collapse the RIGHTMOST column first, then the next one leftward, ending in a single grand total. ROLLUP(region, category) collapses category first (giving a per-region subtotal), the exact opposite of what a per-category report needs; swapping the arguments to ROLLUP(category, region) collapses region first instead, producing the per-category subtotal the report actually wants.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 category | region | total
----------+--------+--------
 Books    | East   |  50.00
 Books    | West   | 100.00
 Books    |        | 150.00
 Games    | East   |  20.00
 Games    | West   |  80.00
 Games    |        | 100.00
          |        | 250.00
(7 rows)

Root cause: COUNT(expr) counts every row whose expr is NON-NULL, regardless of what value that expression holds -- CASE WHEN cond THEN 1 ELSE 0 END produces a 0 (not NULL) for every non-matching row, and COUNT treats that 0 exactly like a 1: both get counted. Dropping the ELSE (leaving a non-match as the implicit NULL) is what makes COUNT skip those rows correctly. SUM(CASE ... ELSE 0 END), by contrast, genuinely needs the 0 -- SUM is folding a total, and 0 is the correct value to add for a non-match, which is why the SAME ELSE 0 pattern is right in one aggregate and wrong in the other.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 paid_count | paid_total
------------+------------
          2 |     300.00
(1 row)

Root cause: a plain SELECT takes no lock at all, so nothing stops a second session from reading the exact same pre-write value -- both sessions' subsequent UPDATE overwrite each other using their OWN stale copy, and whichever commits last simply wins, silently discarding the other's decrement. SELECT ... FOR UPDATE (co-16) takes an exclusive row lock the instant it runs, held until commit or rollback -- session B's own FOR UPDATE against the SAME row genuinely blocks (proven here via a short lock_timeout raising LockNotAvailable instead of hanging the demo forever) until session A releases the lock by committing, at which point B's retry reads the FRESH, already-updated value and its own decrement lands correctly on top of it.

Run: python3 kata.py

Output:

Session A locked stock: 10
Session B blocked: LockNotAvailable
Session A wrote stock: 9
Session B locked stock: 9
Session B wrote stock: 8
Final stock: 8

Root cause: a composite B-tree sorts by its FIRST column, then the next column WITHIN each value of the first -- exactly like a phone book sorted by last name then first name. A query that filters only on published_year (the SECOND column) has no way to use that sort order to narrow anything, because rows with published_year = 2010 are scattered throughout the index under every different author_id. A dedicated index with published_year as its OWN leading column makes exactly this access pattern searchable, without removing the original composite index, which still serves any query that DOES filter by author_id first.

Run: psql -U asqp -d asqp -f kata.sql

Output (row-estimate numbers vary slightly run to run because ANALYZE samples the table statistically):

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on book  (cost=63.64..1215.55 rows=5593 width=4)
   Recheck Cond: (published_year = 2010)
   ->  Bitmap Index Scan on idx_book_year  (cost=0.00..62.24 rows=5593 width=0)
         Index Cond: (published_year = 2010)
(4 rows)

Root cause: a partial index is only eligible when the planner can PROVE, from the query's WHERE clause text, that every row the index covers satisfies the query -- in practice this generally requires the two predicates to match closely in written form, not merely be logically equivalent. status != 'closed' and status = 'open' return the identical set of rows on THIS two-status table, but the planner never runs a general theorem prover to confirm that -- it silently drops the index from consideration with no error or warning. Rewriting the query to match the index's own predicate text exactly restores the match.

Run: psql -U asqp -d asqp -f kata.sql

Output (row-estimate numbers vary slightly run to run because ANALYZE samples the table statistically):

                                    QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using idx_ticket_open on ticket  (cost=0.28..31.87 rows=573 width=10)
(1 row)

Root cause: a materialized view stores its defining query's RESULT as an on-disk snapshot at CREATE/REFRESH time -- unlike a plain VIEW, it does not re-run its query on every reference, so nothing about writing to the underlying book table ever touches the view's own stored rows. REFRESH MATERIALIZED VIEW is the only thing that re-executes the defining query and replaces the view's contents; forgetting to schedule or trigger it after a relevant write is what leaves a dashboard silently stale with no error anywhere to flag the gap.

Run: psql -U asqp -d asqp -f kata.sql

Output:

 author_id | total
-----------+--------
         1 | 160.00
(1 row)

Ties to consistency-latency-throughput -- Serializable's SSI does real bookkeeping (predicate locking, dependency tracking) that costs measurable throughput under contention (Example 78 measures this directly), and it can convert a legitimate concurrent transaction into a serialization_failure the application MUST be ready to retry (Example 60). Repeatable Read is cheaper and already sufficient for workloads that don't have write-skew-shaped invariants spanning multiple rows; recommending the strongest isolation level unconditionally would trade away throughput plenty of workloads never needed to spend.

EXPLAIN's cost/row numbers are the planner's OWN estimate, built from a cost model applied to whatever ANALYZE stats happen to exist -- a plan can look cheap on paper and still be wrong if those stats are stale (Example 53) or the workload doesn't match the model's assumptions (Example 80). EXPLAIN ANALYZE is the only way to see whether the planner's estimate and reality actually agree, and the SIZE of that disagreement is often the most useful diagnostic signal of all, independent of what either number says alone (Example 52's real Buffers; Example 79's IO-tuning workflow built entirely around comparing estimate to reality).

Ties to abstraction-and-its-cost -- every index in this topic buys read speed by charging writes and storage, and that is a single unifying insight independent of which specific index type is under discussion. Naming it separately keeps a developer from evaluating a new index purely on "will this make my SELECT faster" without ever asking the other half of the question (Example 47 and Example 48 measure the write-side cost and bloat directly, distinct from any single index type's own mechanics lesson).

A lost update is a conflict on the SAME row -- the fix (FOR UPDATE, or even Repeatable Read's own first-committer-wins rule on that row) can see the conflict because both transactions touch identical data. Write skew's two transactions write to DIFFERENT rows, so no row-level lock or Repeatable-Read conflict check ever sees an overlap at all -- only Serializable's SSI, which tracks the READS each transaction made (not just the writes), has the information needed to catch the invariant violation.

Both features work by giving the planner enough STATIC information to prove, before touching any data, that an entire category of work is unnecessary -- partition pruning proves whole partitions can't match a WHERE clause; a covering index proves the heap itself never needs visiting because every needed column is already present in the index. Recognizing the shared shape (prove, then skip) is what lets a developer look for the same opportunity in a PostgreSQL feature this topic never explicitly covers.

Ties to consistency-latency-throughput and this topic's own opening framing -- a slow SYSTEM is not always a slow QUERY. Connection setup overhead and a schema shaped for the wrong workload both degrade real, end-to-end latency and throughput without any single EXPLAIN plan ever looking wrong in isolation. The topic's closing examples deliberately widen the lens from "is this one query's plan good" to "is the whole system, including its connection and schema choices, shaped for its actual workload."

Each pattern proven in isolation (Examples 1-84) demonstrates the mechanism works; a real production incident rarely arrives as one isolated concept -- it's a slow report (needing a window function AND the right index), degraded further by an N+1 pattern nobody had noticed, made worse under real concurrent load that surfaces an isolation anomaly nobody had reproduced before. The capstone's job is proving the individually-learned tools compose under a realistic, tangled diagnostic sequence, not re-teaching any one of them again.

NOT IN's correctness is CONDITIONAL on a fact the query text itself doesn't guarantee or even mention -- "this column is never NULL" -- and that fact can silently stop being true the moment someone adds a legitimate NULL-producing row to the referenced table, breaking a query that used to work with no change at the call site itself. NOT EXISTS is correct unconditionally, regardless of what the subquery's result ever contains, which is the same "don't depend on an invisible precondition" instinct this whole topic reinforces elsewhere -- missing ANALYZE, a partial index's exact predicate text, a composite index's column order: every one of them is a case where an invisible, easy-to-violate precondition silently determines correctness or performance.

Ties to abstraction-and-its-cost from the opposite direction of W3 -- several separately-grouped queries UNIONed together mean the engine aggregates over the underlying rows MULTIPLE times, once per UNIONed query, where ROLLUP/CUBE/GROUPING SETS compute every requested subtotal level in a SINGLE pass over the data. The syntax costs a developer some upfront unfamiliarity -- learning column-order sensitivity (Kata 5) and how the grouping levels compose -- in exchange for a real, measurable reduction in how many times the underlying data actually gets touched.