Intermediate Examples

-- Example 29: Correlated Subquery to Join.
-- Rewriting a correlated subquery (co-01) as an explicit JOIN often gives the
-- planner more freedom to choose a hash or merge strategy instead of a per-row
-- probe (co-24) -- same result set, potentially a very different plan.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS book,
author CASCADE;
 
-- Resetting both tables guarantees this example's row counts are exactly
-- what the comments below describe, regardless of what ran before it.
-- => resets state -- this example is fully self-contained
-- Same author/book schema as Examples 1-5, plus Turing (zero books) reprised
-- from Example 2 -- this dataset is reused deliberately across many examples.
CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
 
CREATE TABLE book (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  -- price is unused by either query form here -- carried over from the shared
  -- schema purely for consistency with earlier examples, not because this
  -- example needs it.
  price NUMERIC(6, 2) NOT NULL,
  -- The same FOREIGN KEY relationship both forms rely on -- Form 1's correlation
  -- predicate and Form 2's JOIN condition both walk this exact FK relationship.
  author_id INTEGER REFERENCES author (id)
);
 
-- => both tables exist, currently empty
-- Turing again seeds the zero-books edge case -- both forms below must agree
-- on excluding him, proving the rewrite preserves EXISTS's exact semantics.
INSERT INTO
  author (id, name)
VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');
 
-- Ada has 2 books, Grace has 1, Turing has 0 -- enough variety that a plain
-- JOIN (Form 2) would produce a DIFFERENT row count than EXISTS (Form 1)
-- if DISTINCT were omitted, which is exactly the gotcha this example proves.
INSERT INTO
  book (id, title, price, author_id)
VALUES
  (1, 'The Pragmatic Programmer', 34.99, 1),
  (2, 'Clean Code', 29.99, 1),
  (3, 'The Mythical Man-Month', 24.50, 2);
 
-- => Turing has zero books -- the anti-join case below
-- Form 1: correlated EXISTS (co-01) -- same shape as Example 2.
-- Form 1 never duplicates a row: EXISTS is a boolean test, so each author
-- appears AT MOST once regardless of how many books they have.
SELECT
  a.name
FROM
  author a
WHERE
  EXISTS (
    -- Same idiomatic SELECT 1 as Example 2 -- EXISTS only cares whether a matching
    -- row is found, never what that row contains.
    SELECT
      1
    FROM
      book b
    WHERE
      b.author_id = a.id
  )
  -- Sorting alphabetically makes the two forms' output trivially comparable
  -- side by side -- both forms return "Ada" then "Grace", in that order.
ORDER BY
  a.name;
 
-- => Ada, Grace -- authors WITH at least one book
-- Form 2: the join rewrite -- DISTINCT because a join duplicates author rows once
-- PER matching book, which EXISTS never did (co-24: a different physical strategy).
-- Ada's 2 books mean the raw JOIN (before DISTINCT) would emit Ada TWICE --
-- once per matching book row -- Grace's 1 book emits her once, and Turing,
-- having zero books, never appears in an INNER JOIN result at all.
SELECT DISTINCT
  a.name
FROM
  author a
  -- This ON condition is IDENTICAL to the correlation predicate inside Form 1's
  -- EXISTS -- the rewrite is mechanical: move the correlation from a subquery
  -- WHERE clause into an explicit JOIN condition.
  JOIN book b ON b.author_id = a.id
  -- The SAME ordering as Form 1 -- necessary for a fair side-by-side comparison
  -- of the two forms' output, independent of what each form's default row order might be.
ORDER BY
  a.name;
 
-- => identical 2 rows: Ada, Grace -- same logical result,
-- => but the planner can now choose Hash Join or Merge Join
-- => instead of a per-row correlated subquery probe
-- "Different physical strategy, same logical result" is the running theme this
-- example sets up -- Examples 49-51 show EXPLAIN output proving the planner
-- really does pick different join algorithms (nested loop, hash, merge) for
-- queries shaped like these two forms.

     name
--------------
 Ada Lovelace
 Grace Hopper
(2 rows)
 
     name
--------------
 Ada Lovelace
 Grace Hopper
(2 rows)

-- Example 30: Recursive CTE Graph Cycle.
-- A GRAPH, unlike a tree, can have cycles -- a naive recursive CTE (co-03) over a
-- cyclic route table would loop forever. The fix: carry the visited path in an
-- ARRAY and refuse to revisit any city already in it.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS route CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- route stores directed edges as plain rows -- no PRIMARY KEY, since the same
-- city pair could legitimately appear more than once in a real route network.
CREATE TABLE route(from_city TEXT NOT NULL, to_city TEXT NOT NULL);
                                    -- => a directed edge list -- deliberately contains a CYCLE
INSERT INTO route(from_city, to_city) VALUES
    ('Jakarta', 'Singapore'),
    ('Singapore', 'Bangkok'),
    ('Bangkok', 'Jakarta'),        -- => Bangkok -> Jakarta closes a CYCLE back to the start
    ('Bangkok', 'Hanoi');
                                    -- => without a guard, Jakarta->Singapore->Bangkok->Jakarta->... loops forever
 
-- The path ARRAY (co-03) accumulates every city visited so far. The recursive
-- term's WHERE clause is the cycle guard: NOT (r.to_city = ANY(path)) refuses to
-- step into a city already on the current path.
-- path is a native Postgres ARRAY column -- ANY(path) and array concatenation
-- (path || r.to_city) are built-in array operators, no separate table needed
-- to track "which cities has this branch of the search already visited".
WITH RECURSIVE reachable AS (
    SELECT from_city AS city, ARRAY[from_city] AS path
    FROM route
    WHERE from_city = 'Jakarta'     -- => anchor: start at Jakarta with a 1-city path
    UNION ALL
-- Each recursive pass explores every outbound edge from the CURRENT frontier
-- of cities -- this is a breadth-first graph walk, not the depth-first walk
-- a recursive function call would perform.
    SELECT r.to_city, reachable.path || r.to_city
    FROM route r
    JOIN reachable ON r.from_city = reachable.city
    WHERE NOT (r.to_city = ANY(reachable.path))
                                    -- => cycle guard: skip any city ALREADY in this path
)
SELECT DISTINCT city FROM reachable ORDER BY city;
                                    -- => Bangkok, Hanoi, Jakarta, Singapore -- every reachable city,
                                    -- => and the query TERMINATES despite the Bangkok->Jakarta cycle
-- Without the cycle guard, PostgreSQL would eventually raise an out-of-memory
-- or statement-timeout error rather than loop truly forever -- but by then it
-- has already done a great deal of wasted, unbounded work.

   city
-----------
 Bangkok
 Hanoi
 Jakarta
 Singapore
(4 rows)

-- Example 31: Recursive CTE Bill of Materials.
-- A bill-of-materials (BOM) explosion (co-03) answers "how many of each raw part
-- do I need to build ONE finished product?" -- quantities MULTIPLY down each level.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS part CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Same self-referencing adjacency-list shape as Example 7's org chart, but with
-- an extra qty_per_parent column -- the multiplier at each level, not just the
-- structural link.
CREATE TABLE part (
  id INTEGER PRIMARY KEY,
  -- id is a plain integer identifier, unrelated to the multiplier math -- name is
  -- what carries meaning in the final report below.
  name TEXT NOT NULL,
  -- Just as in Example 7, the root's parent_id is NULL -- the recursion's anchor
  -- is selected by WHERE id = 1, not by testing parent_id IS NULL, though either
  -- approach would work for this single-root dataset.
  parent_id INTEGER REFERENCES part (id),
  -- qty_per_parent means "how many of THIS part are needed to build ONE of its
  -- immediate parent" -- a purely LOCAL ratio; the recursion's job is turning
  -- these local ratios into a cumulative total from root to leaf.
  qty_per_parent INTEGER NOT NULL
);
 
-- => self-referencing FK, like Example 7's org chart
-- A 2-level bicycle: Wheel and Frame are direct children of Bicycle; Spoke and
-- Tire are children of Wheel -- deep enough to prove the multiplier COMPOUNDS
-- across levels (Spoke's 72 = 2 wheels x 36 spokes) rather than just copying down.
INSERT INTO
  part (id, name, parent_id, qty_per_parent)
VALUES
  (1, 'Bicycle', NULL, 1), -- => the root -- 1 bicycle is the top-level product
  (2, 'Wheel', 1, 2), -- => each bicycle needs 2 wheels
  (3, 'Frame', 1, 1), -- => each bicycle needs 1 frame
  (4, 'Spoke', 2, 36), -- => each WHEEL needs 36 spokes
  (5, 'Tire', 2, 1);
 
-- => each wheel needs 1 tire
-- Anchor: the root part, multiplier 1. Recursive term: multiply the CHILD's own
-- qty_per_parent by the PARENT's already-accumulated multiplier (co-03) -- this is
-- what makes spokes come out as 2 wheels x 36 spokes = 72, not just 36.
-- multiplier starts at 1 for the anchor (Bicycle needs exactly 1 of itself) --
-- every other part's multiplier is computed relative to that starting value.
WITH RECURSIVE
  bom AS (
    SELECT
      id,
      name,
      parent_id,
      -- Same accumulator-column pattern as Example 7's depth column, but MULTIPLYING
      -- instead of ADDING at each recursive step -- the recursion mechanics are
      -- identical; only the arithmetic combining function changes.
      1 AS multiplier
    FROM
      -- The anchor's FROM part with WHERE id = 1 scans the WHOLE part table looking
      -- for one row -- fine at this tiny scale; a real BOM table would want an index
      -- on id (already implicit, since id IS the primary key).
      part
    WHERE
      id = 1 -- => anchor: the Bicycle itself, multiplier 1
    UNION ALL
    -- p.id, p.name, p.parent_id must again match the anchor's column list exactly
    -- in count and type -- see Example 7's note on this same requirement.
    SELECT
      p.id,
      p.name,
      p.parent_id,
      -- bom.multiplier * p.qty_per_parent is the entire algorithm: each level's
      -- multiplier is the PARENT's multiplier scaled by how many of THIS part one
      -- parent needs -- compounding naturally falls out of ordinary multiplication.
      bom.multiplier * p.qty_per_parent
    FROM
      part p
      -- Joining CHILD parts to their already-processed PARENT row (not the other way
      -- around) is what lets bom.multiplier already hold the fully-accumulated
      -- multiplier from the root down to that parent before this level multiplies it further.
      JOIN bom ON p.parent_id = bom.id
      -- => recursive term: multiply DOWN the tree, not just add
  )
SELECT
  name,
  multiplier AS total_needed_per_bicycle
FROM
  bom
  -- Excluding the root mirrors a real bill-of-materials report: nobody needs to
  -- be told "you need 1 bicycle to build 1 bicycle" -- only the component list matters.
WHERE
  id != 1 -- => exclude the root -- we want components, not the product itself
  -- Alphabetical ordering here is purely cosmetic -- it does not affect any of
  -- the already-computed multiplier values.
ORDER BY
  name;
 
-- => Frame: 1, Spoke: 72 (2 wheels x 36), Tire: 2, Wheel: 2
-- This same multiply-down-the-tree pattern generalizes to any explosion
-- problem -- ingredient quantities in a recipe tree, dependency counts in a
-- build graph, or resource requirements in a project breakdown structure.

 name  | total_needed_per_bicycle
-------+--------------------------
 Frame |                        1
 Spoke |                       72
 Tire  |                        2
 Wheel |                        2

-- Example 32: Window Moving Average.
-- ROWS BETWEEN N PRECEDING AND CURRENT ROW (co-05) makes the frame EXPLICIT: a
-- sliding 3-day window, not the whole-history running total from Example 8.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS daily_sales CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Same daily_sales shape as Example 8 -- one extra day (Jan 5) added so the
-- sliding window has room to visibly SLIDE past its first few rows.
CREATE TABLE daily_sales (
  sale_date DATE PRIMARY KEY,
  -- Same NUMERIC(8, 2) precision convention as Example 8 -- exact decimal
  -- arithmetic for the AVG computed inside each sliding window.
  amount NUMERIC(8, 2) NOT NULL
);
 
-- Values chosen so the moving average visibly changes shape as the window
-- fills up (day 1: 1 row) and then slides (day 4 onward: always exactly 3 rows).
INSERT INTO
  daily_sales (sale_date, amount)
VALUES
  ('2026-01-01', 100.00),
  ('2026-01-02', 50.00),
  ('2026-01-03', 75.00),
  ('2026-01-04', 120.00),
  ('2026-01-05', 90.00);
 
-- => 5 days -- the frame below only ever looks BACK at most 2 days
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (co-05) is an EXPLICIT frame: sum only
-- the current row plus the 2 rows immediately before it -- a sliding window, not
-- Example 8's ever-growing "everything since the start" default frame.
SELECT
  sale_date,
  amount,
  -- ROUND wraps the window result exactly as in Example 9 -- a scalar function
  -- applied AFTER the window function has already produced its per-row value.
  ROUND(
    -- 2 PRECEDING AND CURRENT ROW is inclusive on both ends -- 3 rows total once
    -- the window is fully "warmed up", which is why the label says 3day, not 2day.
    AVG(amount) OVER (
      -- ROWS (not RANGE) is specified explicitly here because sale_date is UNIQUE per
      -- row -- with a unique ORDER BY key the two frame modes agree, but writing ROWS
      -- states the intent unambiguously (see Example 33 for when they diverge).
      ORDER BY
        sale_date ROWS BETWEEN 2 PRECEDING
        AND CURRENT ROW
    ),
    2
  ) AS moving_avg_3day
FROM
  -- Early rows (Jan 1, Jan 2) have FEWER than 2 preceding rows available --
  -- Postgres simply uses however many actually exist rather than padding with
  -- NULLs or erroring, which is why Jan 1's average is just itself.
  daily_sales
  -- The outer ORDER BY (again) only controls display order -- the window's own
  -- ORDER BY sale_date already fixed which rows fall inside each 3-day frame.
ORDER BY
  sale_date;
 
-- => Jan 1: avg of just itself = 100.00 (only 1 row exists so far)
-- => Jan 3: avg of (100+50+75)/3 = 75.00 (full 3-row window)
-- => Jan 5: avg of (75+120+90)/3 = 95.00 (window has SLID forward)
-- Unlike Example 8's running total, this average never simply grows -- it can
-- rise or fall as new days enter the 3-day window and old ones age out of it,
-- which is the entire point of a MOVING average versus a cumulative one.

 sale_date  | amount | moving_avg_3day
------------+--------+-----------------
 2026-01-01 | 100.00 |          100.00
 2026-01-02 |  50.00 |           75.00
 2026-01-03 |  75.00 |           75.00
 2026-01-04 | 120.00 |           81.67
 2026-01-05 |  90.00 |           95.00
(5 rows)

-- Example 33: RANGE vs ROWS Frame.
-- With a UNIQUE ORDER BY key, RANGE and ROWS frames agree. With DUPLICATE ORDER BY
-- values, they diverge (co-05): RANGE treats every tied "peer" row as ARRIVING
-- together, ROWS treats each physical row as its own step regardless of ties.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS sale CASCADE;
 
-- => resets state -- this example is fully self-contained
-- A minimal 4-row table with exactly one deliberate tie -- the smallest
-- dataset that can expose a RANGE-vs-ROWS divergence at all.
CREATE TABLE sale (
  -- id is never referenced inside either OVER() clause -- only amount drives
  -- the ordering and frame boundaries for both window functions here.
  id INTEGER PRIMARY KEY,
  amount NUMERIC(8, 2) NOT NULL
);
 
-- id doubles as an arbitrary insertion-order tie-breaker -- amount alone
-- cannot distinguish row 2 from row 3, both windows key off amount only.
INSERT INTO
  sale (id, amount)
VALUES
  (1, 10.00),
  (2, 20.00),
  (3, 20.00), -- => TIES with row 2 -- both have amount = 20.00
  (4, 30.00);
 
-- => rows 2 and 3 are "peers" under ORDER BY amount -- the tie
-- Without a tie, this whole example would have nothing to show -- RANGE and
-- ROWS only disagree when two or more rows share the SAME ORDER BY value.
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (co-05) walks PHYSICAL rows one
-- at a time -- row 2 and row 3 get DIFFERENT running sums even though they tie.
-- RANGE (the default frame mode) treats BOTH peers as arriving simultaneously --
-- row 2 and row 3 get the SAME running sum, computed as of the whole peer group.
SELECT
  id,
  amount,
  -- UNBOUNDED PRECEDING AND CURRENT ROW means "every row from the very start
  -- through this one" -- the running-total shape, just with the frame MODE made
  -- explicit instead of relying on the default.
  SUM(amount) OVER (
    ORDER BY
      amount ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS rows_frame,
  -- This is IDENTICAL to Example 8's default (no explicit frame) behavior --
  -- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is exactly what Postgres
  -- assumes when ORDER BY is present but no frame clause is written at all.
  SUM(amount) OVER (
    ORDER BY
      amount RANGE BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS range_frame
FROM
  -- Both window functions read from the SAME sale table in the SAME query --
  -- only the frame MODE (ROWS vs RANGE) differs between the two OVER() clauses.
  sale
  -- Ordering by amount THEN id breaks the tie deterministically for DISPLAY --
  -- rows_frame and range_frame's values were already fixed by each window's own
  -- ORDER BY amount, independent of this outer tie-break.
ORDER BY
  amount,
  id;
 
-- => id 2: rows_frame 30.00 (10+20), range_frame 50.00 (10+20+20, BOTH peers)
-- => id 3: rows_frame 50.00 (10+20+20), range_frame 50.00 (SAME as id 2 -- peers tie)
-- The practical rule of thumb: reach for ROWS whenever the frame should be
-- about PHYSICAL row position (exactly N rows), and rely on the RANGE default
-- only when ties in the ORDER BY column should logically move together.

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

-- Example 34: FIRST_VALUE and LAST_VALUE.
-- FIRST_VALUE/LAST_VALUE (co-04) read a value from the EDGE of the current frame.
-- The classic gotcha: LAST_VALUE with the DEFAULT frame (RANGE ... UNBOUNDED
-- PRECEDING AND CURRENT ROW, co-05) sees the CURRENT row as the last row -- not
-- the true last row of the partition -- unless the frame is widened explicitly.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS employee CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Single-department dataset (all Engineering) keeps PARTITION BY trivial here --
-- the interesting behavior is entirely about frame boundaries within one partition.
CREATE TABLE employee (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  -- dept is constant (Engineering) across all 3 rows here -- PARTITION BY still
  -- works correctly with a single partition, it just has nothing to contrast
  -- against in this minimal example.
  dept TEXT NOT NULL,
  -- Same money-safe NUMERIC precision convention used throughout this topic's
  -- salary/price columns.
  salary NUMERIC(9, 2) NOT NULL
);
 
-- Salaries strictly decrease by insertion order -- irrelevant to the query,
-- which orders by salary DESC regardless of insertion order.
INSERT INTO
  employee (id, name, dept, salary)
VALUES
  (1, 'Linus', 'Engineering', 105000),
  (2, 'Ada', 'Engineering', 95000),
  (3, 'Barbara', 'Engineering', 88000);
 
-- => 3 Engineering employees, ordered by salary DESC below
-- FIRST_VALUE (co-04) with the default frame correctly returns Linus (highest
-- salary) for every row -- the frame's start is fixed at the partition's start.
-- last_value_wrong uses the DEFAULT frame, so it wrongly echoes each row's OWN
-- salary owner back. last_value_fixed widens the frame to the WHOLE partition.
SELECT
  name,
  salary,
  -- FIRST_VALUE's default frame -- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
  -- ROW -- always includes the partition's FIRST row (Linus, once ordered DESC),
  -- so FIRST_VALUE is correct with no extra frame clause needed.
  FIRST_VALUE (name) OVER (
    -- PARTITION BY dept means each department would get its OWN highest_paid/
    -- last_value if more than one department existed -- moot here with just one.
    PARTITION BY
      dept
    ORDER BY
      salary DESC
  ) AS highest_paid,
  -- LAST_VALUE's default frame ALSO ends at CURRENT ROW, not at the partition's
  -- true last row -- so LAST_VALUE, unlike FIRST_VALUE, silently returns the
  -- CURRENT row's own name for every row: the classic default-frame gotcha.
  LAST_VALUE (name) OVER (
    PARTITION BY
      dept
    ORDER BY
      salary DESC
  ) AS last_value_wrong,
  -- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING widens the frame to
  -- the ENTIRE partition, front to back -- only then does LAST_VALUE actually
  -- reach the partition's true last row (Barbara) for every output row.
  LAST_VALUE (name) OVER (
    PARTITION BY
      dept
    ORDER BY
      salary DESC ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING
  ) AS last_value_fixed
FROM
  -- All three window functions read the SAME partition/order specification,
  -- differing only in their FRAME -- isolating frame width as the one variable.
  employee
  -- Ordering DESC for display matches the DESC ordering already used inside
  -- every OVER() clause -- Linus (highest) prints first, Barbara (lowest) last.
ORDER BY
  salary DESC;
 
-- => highest_paid is Linus for ALL 3 rows -- correct on the first try
-- => last_value_wrong just echoes each row's OWN name -- the gotcha
-- => last_value_fixed is Barbara for ALL 3 rows -- the TRUE lowest earner
-- MIN()/MAX() OVER (PARTITION BY dept) would sidestep this whole gotcha for a
-- simple highest/lowest lookup -- FIRST_VALUE/LAST_VALUE earn their keep once
-- you need an arbitrary EXPRESSION evaluated at a specific frame edge, not just
-- the min/max of one column.

  name   |  salary   | highest_paid | last_value_wrong | last_value_fixed
---------+-----------+--------------+------------------+------------------
 Linus   | 105000.00 | Linus        | Linus            | Barbara
 Ada     |  95000.00 | Linus        | Ada               | Barbara
 Barbara |  88000.00 | Linus        | Barbara           | Barbara
(3 rows)

-- Example 35: PERCENT_RANK and CUME_DIST.
-- PERCENT_RANK (co-06) reports each row's relative position as a fraction from 0
-- to 1. CUME_DIST reports the fraction of rows AT OR BELOW the current row's rank.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS employee CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- Five employees, five distinct salaries -- no ties, so every rank is unique
-- and PERCENT_RANK/CUME_DIST land on clean, easy-to-verify fractions.
CREATE TABLE employee(id INTEGER PRIMARY KEY, name TEXT NOT NULL, salary NUMERIC(9,2) NOT NULL);
INSERT INTO employee(id, name, salary) VALUES
    (1, 'Linus',   105000), (2, 'Grace',    99000),
    (3, 'Ada',      95000), (4, 'Alan',     88000),
    (5, 'Barbara',  80000);
                                    -- => 5 employees -- PERCENT_RANK/CUME_DIST both range over [0, 1]
 
-- PERCENT_RANK() (co-06) = (rank - 1) / (total_rows - 1) -- the LOWEST earner is
-- always 0.0, the HIGHEST is always 1.0. CUME_DIST = rows_at_or_below / total_rows.
-- Both return DOUBLE PRECISION, so ROUND needs an explicit ::numeric cast.
SELECT
    name,
    salary,
-- (rank - 1) / (total_rows - 1) is PERCENT_RANK's own formula -- with 5 rows,
-- each step up in rank moves percent_rank by exactly 1/4 = 0.25.
    ROUND(PERCENT_RANK() OVER (ORDER BY salary)::numeric, 3) AS percent_rank,
-- CUME_DIST's denominator is total_rows, not total_rows - 1 -- that's why the
-- LOWEST earner's cume_dist (0.200 = 1/5) is never 0.0, unlike percent_rank.
    ROUND(CUME_DIST()    OVER (ORDER BY salary)::numeric, 3) AS cume_dist
FROM employee
ORDER BY salary;
                                    -- => Barbara (lowest): percent_rank 0.000, cume_dist 0.200 (1 of 5 rows)
                                    -- => Linus (highest): percent_rank 1.000, cume_dist 1.000 (all 5 rows)

  name   |  salary   | percent_rank | cume_dist
---------+-----------+--------------+-----------
 Barbara |  80000.00 |        0.000 |     0.200
 Alan    |  88000.00 |        0.250 |     0.400
 Ada     |  95000.00 |        0.500 |     0.600
 Grace   |  99000.00 |        0.750 |     0.800
 Linus   | 105000.00 |        1.000 |     1.000
(5 rows)

-- Example 36: Top-N per Group.
-- ROW_NUMBER() PARTITION BY ... (co-06) numbers rows WITHIN each group -- filtering
-- WHERE rn <= N in an outer query is the standard "top N per group" pattern.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS employee CASCADE;
 
-- Resetting the table first guarantees exactly 6 rows -- 3 per department --
-- regardless of what any earlier example left behind.
-- => resets state -- this example is fully self-contained
-- Same employee shape as Example 9, now with a second department seeded so
-- "top N PER group" has two independent groups to demonstrate against.
CREATE TABLE employee (
  -- id is unused in either the CTE or the final SELECT -- it exists purely
  -- as the table's primary key, not because this query needs it.
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  -- dept is the PARTITION BY key -- every distinct value becomes its own
  -- independent numbering group, however many departments end up existing.
  dept TEXT NOT NULL,
  -- Same money-safe NUMERIC precision convention used throughout this topic's
  -- salary columns.
  salary NUMERIC(9, 2) NOT NULL
);
 
-- 3 rows per department, salaries all distinct within each dept -- clean ranks
-- with no ties to complicate which 2 rows "top 2" should keep.
INSERT INTO
  employee (id, name, dept, salary)
VALUES
  (1, 'Linus', 'Engineering', 105000),
  (2, 'Ada', 'Engineering', 95000),
  (3, 'Barbara', 'Engineering', 88000),
  (4, 'Grace', 'Data', 99000),
  (5, 'Alan', 'Data', 91000),
  (6, 'Edsger', 'Data', 85000);
 
-- => 3 Engineering, 3 Data -- top-2 per dept below excludes 2 rows total
-- ROW_NUMBER() CANNOT appear in a WHERE clause directly (window functions run
-- AFTER WHERE) -- wrap it in a subquery/CTE, then filter the OUTER query (co-06).
-- This is the SAME problem Example 10 introduced for a single, ungrouped rank
-- -- Top-N per group is that same numbering trick with PARTITION BY layered on.
WITH
  -- ranked is a plain (non-recursive) CTE -- exactly the Example 4 pattern,
  -- just carrying a window function's output through to the outer query.
  ranked AS (
    SELECT
      -- name, dept, and salary all ride through the CTE untouched -- ranked simply
      -- adds rn alongside the original columns, it does not transform them.
      name,
      dept,
      salary,
      -- PARTITION BY dept resets the numbering back to 1 at the start of EACH
      -- department -- without it, rn would count 1 through 6 across the whole table,
      -- and "rn <= 2" would keep only the 2 single highest earners company-wide.
      ROW_NUMBER() OVER (
        PARTITION BY
          dept
          -- Ordering DESC inside the window is what makes rn = 1 mean "highest earner"
          -- -- flipping to ASC would instead make rn = 1 the LOWEST earner per department.
        ORDER BY
          salary DESC
      ) AS rn
    FROM
      -- No WHERE clause at all inside the CTE -- ranked deliberately keeps EVERY row,
      -- numbered; filtering down to the top N is entirely the OUTER query's job.
      employee
  )
  -- rn itself is intentionally NOT selected in the final output -- it did its job
  -- inside the WHERE clause below and has no further meaning to the reader.
SELECT
  name,
  dept,
  salary
FROM
  ranked
  -- This WHERE clause is what a naive "WHERE ROW_NUMBER() OVER (...) <= 2" wishes
  -- it could be -- Postgres rejects that form outright because window functions
  -- are computed AFTER the WHERE clause has already filtered rows, not before.
WHERE
  rn <= 2 -- => keeps only the top 2 earners PER department
  -- Ordering by dept groups the two departments' top earners together for
  -- display, and salary DESC surfaces each department's #1 first.
ORDER BY
  dept,
  salary DESC;
 
-- => Engineering: Linus, Ada (Barbara excluded -- 3rd place)
-- => Data: Grace, Alan (Edsger excluded -- 3rd place)
-- Changing "rn <= 2" to any other N (say, 1 for department heads, or 5 for a
-- wider leaderboard) is the ENTIRE change needed to adjust N -- nothing about
-- the ranked CTE itself needs to change.
-- Top-N-per-group via ROW_NUMBER is the general-purpose tool -- DISTINCT ON
-- (a Postgres-specific extension) can express the N=1 special case even more
-- tersely, at the cost of being non-standard SQL.

 name  |    dept     |  salary
-------+-------------+-----------
 Grace | Data        |  99000.00
 Alan  | Data        |  91000.00
 Linus | Engineering | 105000.00
 Ada   | Engineering |  95000.00
(4 rows)

-- Example 37: LATERAL Join Top-N.
-- LATERAL (co-09) lets a subquery in FROM reference COLUMNS from an earlier item
-- in the same FROM clause -- here, each author's OWN id feeds their own per-author
-- top-2-books lookup, something a plain JOIN's subquery could never reference.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS book,
author CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Same author/book schema as Examples 1-5, with Ada given a THIRD book so her
-- top-2 result visibly EXCLUDES one, unlike Hopper's exactly-2 books.
CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
 
CREATE TABLE book (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  -- Same money-safe NUMERIC precision convention as every book.price column
  -- in this topic's earlier examples.
  price NUMERIC(6, 2) NOT NULL,
  -- This is the exact FOREIGN KEY the LATERAL WHERE clause below walks --
  -- book.author_id = a.id -- the same relationship every join in this topic uses.
  author_id INTEGER REFERENCES author (id)
);
 
INSERT INTO
  author (id, name)
VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');
 
-- Ada's 3 books have 3 distinct prices -- Refactoring and Pragmatic Programmer
-- are her 2 priciest, so Clean Code is the one LATERAL's LIMIT 2 excludes.
INSERT INTO
  book (id, title, price, author_id)
VALUES
  (1, 'The Pragmatic Programmer', 34.99, 1),
  (2, 'Clean Code', 29.99, 1),
  (3, 'Refactoring', 39.99, 1),
  (4, 'The Mythical Man-Month', 24.50, 2),
  (5, 'Peopleware', 22.00, 2);
 
-- => Ada has 3 books, Hopper has 2 -- top-2-per-author below
-- LATERAL (co-09) lets this subquery reference a.id -- the OUTER author row --
-- something an ordinary FROM-clause subquery is not allowed to do at all.
SELECT
  -- a.name is the only column pulled from author itself -- everything else in
  -- the output comes from the LATERAL subquery's own result set.
  a.name,
  -- top_books.title/price are the LATERAL subquery's own OUTPUT columns --
  -- accessible in the outer SELECT exactly like any other joined table's columns.
  top_books.title,
  top_books.price
FROM
  -- author a is the DRIVING side of this LATERAL join -- its rows are what get
  -- fed, one at a time, into the LATERAL subquery on the right.
  author a
  -- CROSS JOIN LATERAL (rather than a plain CROSS JOIN) is what grants the
  -- subquery permission to reference a.id -- LATERAL is the keyword that lifts
  -- the "subqueries in FROM cannot see sibling FROM items" restriction.
  -- CROSS JOIN LATERAL drops an author entirely if their LATERAL subquery
  -- returns zero rows -- a LEFT JOIN LATERAL would instead keep the author with
  -- NULL book columns, the same INNER-vs-OUTER distinction as any other join.
  CROSS JOIN LATERAL (
    -- Conceptually, Postgres re-runs this entire subquery ONCE PER outer author
    -- row, substituting that row's a.id each time -- similar in spirit to how a
    -- correlated subquery (Example 2) re-evaluates per outer row, but usable in FROM.
    SELECT
      title,
      price
    FROM
      book
    WHERE
      -- This is the LATERAL reference itself -- a.id would be completely out of
      -- scope here without the LATERAL keyword on the join above.
      book.author_id = a.id -- => the LATERAL reference: a.id from the OUTER row
      -- Ordering by price DESC INSIDE the subquery is what determines which 2 books
      -- LIMIT keeps -- this ORDER BY is local to the LATERAL subquery, unrelated to
      -- the outer query's own final ORDER BY below.
    ORDER BY
      price DESC
      -- LIMIT works INSIDE a LATERAL subquery exactly as it would in a standalone
      -- query -- LATERAL does not restrict which SQL features are usable within it.
    LIMIT
      2 -- => top 2 per author, computed FRESH for each author row
  ) AS top_books
  -- The outer ORDER BY re-sorts the FINAL combined result for display -- each
  -- author's own top_books.price DESC ordering was already fixed inside the
  -- LATERAL subquery itself, independent of this outer clause.
ORDER BY
  a.name,
  top_books.price DESC;
 
-- => Ada: Refactoring (39.99), Pragmatic Programmer (34.99) -- Clean Code excluded
-- => Hopper: Mythical Man-Month (24.50), Peopleware (22.00) -- both, only 2 exist
-- Before Postgres 9.3 introduced LATERAL, this exact per-author top-N pattern
-- had no clean SQL expression at all -- it required either a correlated
-- subquery returning an array, or application-side looping.
-- LATERAL composes with any subquery shape -- aggregates, window functions,
-- or (as here) an ORDER BY/LIMIT top-N pattern all work identically.

     name     |          title            | price
--------------+---------------------------+-------
 Ada Lovelace | Refactoring                | 39.99
 Ada Lovelace | The Pragmatic Programmer   | 34.99
 Grace Hopper | The Mythical Man-Month     | 24.50
 Grace Hopper | Peopleware                 | 22.00
(4 rows)

-- Example 38: LATERAL vs Correlated Subquery.
-- A correlated scalar subquery in the SELECT list (co-01) and a LEFT JOIN LATERAL
-- (co-09) can express the SAME "one related row per outer row" query -- but only
-- LATERAL can return MULTIPLE columns from that related row in one join.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS book,
author CASCADE;
 
-- Resetting both tables first keeps the row counts in every comment below
-- accurate regardless of what ran before this script.
-- => resets state -- this example is fully self-contained
-- Same author/book schema as Examples 1-5, with Ada given a second book and
-- Turing reprising his zero-books role from Example 2 -- both forms below must
-- agree on how they handle Turing.
CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
 
CREATE TABLE book (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  -- Same money-safe NUMERIC precision convention as this topic's other
  -- book.price columns.
  price NUMERIC(6, 2) NOT NULL,
  -- The same author/book FK relationship every correlated query in this topic
  -- walks -- Form 1 and Form 2 both filter on it identically.
  author_id INTEGER REFERENCES author (id)
);
 
INSERT INTO
  author (id, name)
VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');
 
-- Ada's two books have different prices -- Refactoring (39.99) outranks
-- Pragmatic Programmer (34.99), so Refactoring is the one "top_book" both
-- forms below should agree on.
INSERT INTO
  book (id, title, price, author_id)
VALUES
  (1, 'The Pragmatic Programmer', 34.99, 1),
  (2, 'Refactoring', 39.99, 1),
  (3, 'The Mythical Man-Month', 24.50, 2);
 
-- => Turing has ZERO books -- the outer-join case both forms must handle
-- Form 1: a correlated scalar subquery (co-01) -- limited to exactly ONE column.
-- The parenthesized subquery here sits directly in the SELECT list, as if it
-- were an ordinary scalar expression -- Postgres requires it to return AT MOST
-- one row and one column, exactly like the scalar subquery gotcha in Example 1.
SELECT
  a.name,
  (
    -- Only ONE column (title) can be projected here -- adding a second column
    -- would make this an invalid scalar subquery, raising a syntax/type error.
    SELECT
      title
    FROM
      book
    WHERE
      -- This correlation is structurally identical to Example 2's EXISTS subquery --
      -- a.id reaches into the outer author row from inside the correlated subquery.
      book.author_id = a.id
      -- ORDER BY price DESC LIMIT 1 is what turns "all of this author's books" into
      -- "the single highest-priced one" -- LIMIT 1 is what guarantees the scalar
      -- subquery's one-row requirement is actually satisfied, not just hoped for.
    ORDER BY
      price DESC
    LIMIT
      1
  ) AS top_book
FROM
  -- No JOIN at all is needed for Form 1 -- the correlated subquery embedded in
  -- the SELECT list implicitly runs once per author row, achieving the same
  -- "one row per author, even with zero books" outcome an OUTER JOIN would need.
  author a
ORDER BY
  a.name;
 
-- => Turing: top_book is NULL -- a scalar subquery with zero
-- => matching rows naturally returns NULL, no outer-join syntax needed
-- A scalar subquery naturally behaves like an OUTER join for the "row exists
-- or not" question -- Turing gets a row with NULL, never gets dropped, with
-- zero extra join syntax required.
-- Form 2: LEFT JOIN LATERAL (co-09) -- can return SEVERAL columns (title AND
-- price here), which a scalar subquery could never do in one expression.
-- Form 2 repeats the EXACT SAME correlated filter/order/limit logic as Form 1's
-- subquery, just written as a LATERAL subquery in FROM instead of in SELECT.
SELECT
  -- top.title and top.price are both pulled from the SAME single matched row --
  -- LATERAL's subquery runs once per author, same as Form 1, but can hand back
  -- its ENTIRE row shape instead of being squeezed through one scalar value.
  a.name,
  top.title AS top_book,
  top.price AS top_price
FROM
  author a
  -- LEFT (not INNER) JOIN LATERAL is what keeps Turing's author row when his
  -- LATERAL subquery returns zero books -- an INNER JOIN LATERAL would silently
  -- drop Turing entirely, the opposite of what Form 1's scalar subquery does.
  LEFT JOIN LATERAL (
    -- Both title AND price are projected here -- LATERAL has no scalar-subquery
    -- restriction on column count, which is this whole example's payoff.
    SELECT
      title,
      price
    FROM
      book
    WHERE
      -- The identical correlation predicate as Form 1 -- LATERAL's a.id reference
      -- works the same way a correlated subquery's does, just inside a FROM clause.
      book.author_id = a.id
      -- Same ORDER BY price DESC LIMIT 1 as Form 1 -- LATERAL does not require
      -- LIMIT 1 the way a scalar subquery does, but keeping it here preserves the
      -- "exactly one top book per author" semantics both forms are comparing.
    ORDER BY
      price DESC
    LIMIT
      1
      -- "ON TRUE" is a deliberately trivial join condition -- the REAL correlation
      -- already happened inside the LATERAL subquery's own WHERE clause; ON TRUE
      -- just satisfies JOIN's syntactic requirement for SOME condition.
  ) AS top ON TRUE
ORDER BY
  a.name;
 
-- => identical top_book column to Form 1 -- PLUS top_price, a
-- => second column the scalar-subquery form structurally cannot provide
-- => LEFT JOIN ... ON TRUE (not CROSS JOIN) keeps Turing's row with NULLs
-- The general rule: reach for a correlated scalar subquery when exactly ONE
-- column is needed, and for LATERAL the moment a SECOND column, an aggregate
-- over several rows, or a LIMIT-based top-N shape is required instead.

     name     |        top_book
--------------+------------------------
 Ada Lovelace | Refactoring
 Alan Turing  |
 Grace Hopper | The Mythical Man-Month
(3 rows)
 
     name     |        top_book        | top_price
--------------+------------------------+-----------
 Ada Lovelace | Refactoring            |     39.99
 Alan Turing  |                        |
 Grace Hopper | The Mythical Man-Month |     24.50
(3 rows)

-- Example 39: CUBE Crosstab.
-- CUBE(region, category) (co-08) produces EVERY combination of subtotals: the full
-- cross-tabulation, both single-dimension totals, AND the grand total -- ROLLUP's
-- fixed hierarchy is a STRICT SUBSET of what CUBE computes.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS sale CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Identical schema and seed data to Examples 15 and 16 -- CUBE, ROLLUP, and
-- GROUPING SETS are compared fairly against the exact same 4 rows.
CREATE TABLE sale (
  id INTEGER PRIMARY KEY,
  -- region and category are BOTH included in CUBE's argument list -- CUBE(region)
  -- alone would instead just produce region subtotals plus a grand total.
  region TEXT NOT NULL,
  category TEXT NOT NULL,
  -- Same amount column and NOT NULL constraint as Examples 15/16 -- reused
  -- verbatim so this example's ONLY new concept is the CUBE keyword itself.
  amount NUMERIC(8, 2) NOT NULL
);
 
-- The same 2-region, 2-category, 4-row dataset -- small enough that all 9
-- CUBE output rows can be eyeballed directly against the raw input.
INSERT INTO
  sale (id, region, category, amount)
VALUES
  (1, 'East', 'Books', 100.00),
  (2, 'East', 'Games', 50.00),
  (3, 'West', 'Books', 80.00),
  (4, 'West', 'Games', 40.00);
 
-- => same 4-row dataset as Examples 15/16 -- compare all three outputs
-- CUBE(region, category) (co-08) = detail rows + region subtotals + category
-- subtotals + grand total -- ROLLUP only gave detail + region subtotals + grand total.
SELECT
  region,
  category,
  -- The single SUM(amount) aggregate is unchanged from Examples 15/16 -- only
  -- the grouping construct (CUBE here) differs across the three examples.
  SUM(amount) AS total
FROM
  sale
  -- CUBE(region, category) is shorthand for the FULL grouping-sets list:
  -- GROUPING SETS((region, category), (region), (category), ()) -- all FOUR
  -- combinations of the two columns, including both included and both excluded.
GROUP BY
  CUBE (region, category)
  -- NULLS LAST is required for the same reason as Examples 15 and 16 -- every
  -- omitted-column combination fills that column with NULL in CUBE's output.
ORDER BY
  region NULLS LAST,
  category NULLS LAST;
 
-- => 4 detail + 2 region subtotals + 2 category subtotals + 1 grand total = 9 rows
-- => (ROLLUP produced 7, GROUPING SETS produced 5 -- CUBE is the superset)
-- CUBE grows FAST with more columns -- CUBE(a, b, c) produces 2^3 = 8
-- groupings, not 4 -- reach for GROUPING SETS directly once only a handful of
-- the full combinatorial set is actually needed.

 region | category | total
--------+----------+--------
 East   | Books    | 100.00
 East   | Games    |  50.00
 East   |          | 150.00
 West   | Books    |  80.00
 West   | Games    |  40.00
 West   |          | 120.00
        | Books    | 180.00
        | Games    |  90.00
        |          | 270.00
(9 rows)

-- Example 40: Composite Index Order.
-- A composite index on (customer_id, product_id) (co-19) is sorted by customer_id
-- FIRST, product_id second -- it can only be searched efficiently starting from its
-- LEFTMOST column. customer_id is seeded HIGH-CARDINALITY on purpose: with a
-- high-cardinality leading column, PostgreSQL 18's B-tree skip scan is not worth it
-- (too many distinct customer_id groups to skip between), so the classic left-most-
-- prefix rule holds cleanly -- see the "Why it matters" note below for the exception.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS order_item CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE order_item(id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL);
                                    -- => customer_id: ~100,000 distinct values (HIGH cardinality)
                                    -- => product_id: only 20 distinct values (LOW cardinality)
INSERT INTO order_item(id, customer_id, product_id, quantity)
SELECT n, n, 1 + (n % 20), 1 + (n % 5)
FROM generate_series(1, 100000) AS n;
                                    -- => customer_id = n -- every row has a UNIQUE customer_id
 
CREATE INDEX idx_order_item_customer_product ON order_item(customer_id, product_id);
ANALYZE order_item;
                                    -- => composite B-tree, sorted by (customer_id, product_id)
 
-- Query 1: filters on customer_id, the LEADING column -- the index is directly usable.
EXPLAIN SELECT * FROM order_item WHERE customer_id = 50000;
                                    -- => Index Scan using idx_order_item_customer_product -- leftmost prefix used
 
-- Query 2: filters on product_id ONLY -- customer_id (the leading column) is
-- ABSENT from WHERE. With a high-cardinality leading column, skip scan gains
-- nothing (co-19), so the planner falls back to scanning the table directly.
EXPLAIN SELECT * FROM order_item WHERE product_id = 5;
                                    -- => Seq Scan on order_item -- the composite index is NOT used at all

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using idx_order_item_customer_product on order_item  (cost=0.29..8.31 rows=1 width=16)
   Index Cond: (customer_id = 50000)
(2 rows)
 
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on order_item  (cost=0.00..1791.00 rows=4973 width=16)
   Filter: (product_id = 5)
(2 rows)

-- Example 41: Covering Index and Index Only Scan.
-- INCLUDE adds EXTRA columns to an index WITHOUT making them part of the sort key
-- (co-19) -- purely so the index alone can answer a query, letting the planner
-- skip the heap (the actual table storage) entirely: an Index Only Scan (co-24).
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS book_catalog CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE book_catalog(id INTEGER PRIMARY KEY, isbn TEXT NOT NULL, price NUMERIC(6,2) NOT NULL);
INSERT INTO book_catalog(id, isbn, price)
SELECT n, 'ISBN-' || LPAD(n::TEXT, 9, '0'), (10 + (n % 90))::NUMERIC
FROM generate_series(1, 100000) AS n;
                                    -- => 100,000 rows -- large enough for the plan choice to matter
 
-- INCLUDE (price) (co-19) stores price ALONGSIDE the isbn index entries -- price is
-- NOT part of the sort key, just carried along for queries that only need to READ it.
CREATE INDEX idx_book_catalog_isbn_covering ON book_catalog(isbn) INCLUDE (price);
ANALYZE book_catalog;
                                    -- => VACUUM sets the visibility map so Index Only Scan can trust it
VACUUM book_catalog;
 
-- This query needs ONLY isbn (in WHERE) and price (in SELECT) -- both are present
-- IN the index itself, so EXPLAIN ANALYZE's "Heap Fetches" line proves the heap
-- was never touched at all (co-24) -- the definitive evidence, not just the node name.
EXPLAIN (ANALYZE) SELECT price FROM book_catalog WHERE isbn = 'ISBN-000050000';
                                    -- => Index Only Scan using idx_book_catalog_isbn_covering
                                    -- => Heap Fetches: 0 -- the DEFINITIVE proof the heap was skipped

                                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_book_catalog_isbn_covering on book_catalog  (cost=0.42..4.44 rows=1 width=5) (actual time=0.025..0.026 rows=1.00 loops=1)
   Index Cond: (isbn = 'ISBN-000050000'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=20
 Planning Time: 0.083 ms
 Execution Time: 0.040 ms
(9 rows)

-- Example 42: Partial Index.
-- A partial index (co-21) has a WHERE clause of its own -- it indexes only the
-- rows matching that predicate, staying much smaller than a full-table index when
-- the matching rows are a small minority.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS order_row CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE order_row(id INTEGER PRIMARY KEY, status TEXT NOT NULL);
                                    -- => status: mostly 'shipped', a small minority still 'pending'
INSERT INTO order_row(id, status)
SELECT n, CASE WHEN n % 100 = 0 THEN 'pending' ELSE 'shipped' END
FROM generate_series(1, 100000) AS n;
                                    -- => 100,000 rows; only 1% (1,000 rows) are 'pending'
 
-- CREATE INDEX ... WHERE status = 'pending' (co-21) indexes ONLY those 1,000 rows --
-- the other 99,000 'shipped' rows never enter this index at all.
CREATE INDEX idx_order_pending ON order_row(id) WHERE status = 'pending';
ANALYZE order_row;
 
-- pg_relation_size (a real byte count, not an estimate) proves the partial index is
-- dramatically smaller than a full index over the same 100,000 rows would be.
CREATE INDEX idx_order_full ON order_row(id);
                                    -- => a full, unfiltered index for direct size comparison below
SELECT
    pg_size_pretty(pg_relation_size('idx_order_pending')) AS partial_index_size,
    pg_size_pretty(pg_relation_size('idx_order_full'))    AS full_index_size;
                                    -- => partial_index_size is roughly 1% the size of full_index_size
 
-- A query whose WHERE clause matches the partial index's own predicate CAN use it.
EXPLAIN SELECT id FROM order_row WHERE status = 'pending';
                                    -- => Index Only Scan using idx_order_pending -- the small, matching index

 partial_index_size | full_index_size
---------------------+-----------------
 40 kB               | 2208 kB
(1 row)
 
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Only Scan using idx_order_pending on order_row  (cost=0.28..44.17 rows=993 width=4)
(1 row)

-- Example 43: Expression Index.
-- An expression index (co-21) indexes the OUTPUT of a function or expression, not
-- a raw column -- here, lower(email), so a case-insensitive lookup can use an index
-- even though the stored email values themselves keep their original casing.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS customer CASCADE;
 
-- => resets state -- this example is fully self-contained
-- A single wide table (100,000 rows) is generated below purely to give EXPLAIN
-- something realistic to reason about -- see Example 22 for the same generator pattern.
CREATE TABLE customer (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
 
-- 'User' || n || '@Example.com' -- capitalized deliberately so every stored
-- email has mixed case, making a case-sensitive plain index useless for a
-- case-insensitive lookup.
INSERT INTO
  customer (id, email)
SELECT
  n,
  'User' || n || '@Example.com'
FROM
  generate_series (1, 100000) AS n;
 
-- => emails stored with MIXED case on purpose -- 'User1@Example.com'
-- CREATE INDEX ... (lower(email)) (co-21) indexes lower(email), NOT email itself --
-- a plain index on email cannot serve a lower(email) = ... predicate at all.
-- Postgres computes lower(email) for every EXISTING row once, at index-build
-- time, then keeps it up to date incrementally on every future INSERT/UPDATE --
-- the expression is not re-evaluated at query time from a plain column index.
CREATE INDEX idx_customer_email_lower ON customer (lower(email));
 
-- ANALYZE refreshes the planner's statistics -- without it, the planner might
-- still choose a sequential scan simply because it doesn't yet know how
-- selective this new expression index actually is.
ANALYZE customer;
 
-- The query's WHERE clause must match the INDEXED EXPRESSION exactly: lower(email),
-- not email itself, for the planner to recognize it can use this index (co-21).
-- EXPLAIN alone (no ANALYZE) shows the planner's CHOSEN plan and its cost
-- ESTIMATE without actually running the query -- Example 23 contrasts this
-- with EXPLAIN ANALYZE, which executes the query and reports real timings.
EXPLAIN
SELECT
  id,
  email
FROM
  customer
WHERE
  lower(email) = 'user50000@example.com';
 
-- => Index Scan using idx_customer_email_lower -- the expression matched
-- => a plain WHERE email = 'user50000@example.com' (no lower()) would
-- => NOT match this index -- the stored email is 'User50000@Example.com'

                                        QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using idx_customer_email_lower on customer  (cost=0.42..8.44 rows=1 width=25)
   Index Cond: (lower(email) = 'user50000@example.com'::text)
(2 rows)

-- Example 44: Hash Index.
-- USING hash (co-20) builds a hash table instead of a sorted B-tree -- it supports
-- EQUALITY lookups only (no <, >, BETWEEN, no ORDER BY support), but each lookup
-- is a single hash computation rather than a tree descent.
SET
  client_min_messages TO WARNING;
 
DROP TABLE IF EXISTS customer CASCADE;
 
-- => resets state -- this example is fully self-contained
-- Same generate_series-seeded customer table pattern as Example 43 -- this time
-- with consistently lower-case emails, since the point here is index TYPE, not
-- case-insensitivity.
CREATE TABLE customer (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
 
-- Every email is unique (n is the series' own row number), which is exactly
-- the access pattern a hash index is built for: point lookups by exact key.
INSERT INTO
  customer (id, email)
SELECT
  n,
  'user' || n || '@example.com'
FROM
  generate_series (1, 100000) AS n;
 
-- => 100,000 rows -- email is unique per row, ideal for equality lookups
-- CREATE INDEX ... USING hash (co-20) -- explicitly requests a hash index over a
-- B-tree, appropriate here because THIS column is only ever queried by exact match.
-- USING hash is the ONLY part that differs from a plain CREATE INDEX -- omitting
-- it (as in Example 21) would default to a B-tree, which ALSO supports equality
-- but additionally supports ordering and range predicates a hash index cannot.
CREATE INDEX idx_customer_email_hash ON customer USING hash (email);
 
-- Refreshing statistics here matters just as much as it did for the B-tree
-- and expression-index examples -- the planner needs current selectivity
-- estimates to decide between the new hash index and a sequential scan.
ANALYZE customer;
 
-- Equality (=) can use a hash index -- this is the ONLY predicate shape it supports.
-- A single hash-table lookup, not a multi-level tree descent, is what makes
-- hash index equality lookups theoretically cheaper than B-tree ones at very
-- large table sizes -- the tradeoff is losing ordering/range support entirely.
EXPLAIN
SELECT
  id
FROM
  customer
WHERE
  email = 'user50000@example.com';
 
-- => Index Scan using idx_customer_email_hash -- equality served directly
-- A range predicate CANNOT use a hash index at all -- there is no B-tree ordering
-- to walk -- so the planner falls back to a sequential scan for this shape.
-- This is the DIRECT tradeoff hash indexes make: gaining O(1)-ish equality
-- lookups costs you EVERY other predicate shape a B-tree would have served --
-- less than, greater than, BETWEEN, LIKE 'prefix%', and ORDER BY all fall back
-- to a full scan here.
EXPLAIN
SELECT
  id
FROM
  customer
WHERE
  email > 'user50000@example.com';
 
-- => Seq Scan on customer -- the hash index is structurally unusable here

                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using idx_customer_email_hash on customer  (cost=0.00..8.02 rows=1 width=4)
   Index Cond: (email = 'user50000@example.com'::text)
(2 rows)
 
                          QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on customer  (cost=0.00..1985.00 rows=55001 width=4)
   Filter: (email > 'user50000@example.com'::text)
(2 rows)

-- Example 45: GIN Index on jsonb.
-- GIN (co-20, Generalized Inverted Index) indexes the INDIVIDUAL elements inside a
-- composite value -- for jsonb, that means every key and value becomes independently
-- searchable, which the @> containment operator below relies on.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS product CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- A single JSONB column stores a flexible, semi-structured attribute bag --
-- the kind of schema-optional data GIN indexes are specifically built to search.
CREATE TABLE product(id INTEGER PRIMARY KEY, attributes JSONB NOT NULL);
                                    -- => attributes: a flexible per-product bag of key/value pairs
INSERT INTO product(id, attributes)
SELECT
    n,
    jsonb_build_object(
        'color', (ARRAY['red', 'blue', 'green'])[1 + (n % 3)],
        'size',  (ARRAY['S', 'M', 'L'])[1 + (n % 3)]
    )
FROM generate_series(1, 100000) AS n;
                                    -- => 100,000 products, each with a color and size attribute
 
-- CREATE INDEX ... USING gin (co-20) indexes every key/value pair INSIDE each jsonb
-- document, enabling the @> "contains" operator to use the index efficiently.
-- Unlike a B-tree, which stores ONE entry per row, a GIN index stores one entry
-- PER KEY inside the jsonb document -- a product with 5 attributes contributes
-- 5 separate index entries, not 1.
CREATE INDEX idx_product_attributes_gin ON product USING gin (attributes);
ANALYZE product;
 
-- @> (co-20) tests whether the LEFT jsonb value CONTAINS the RIGHT one -- here,
-- "does this product's attributes include color: red?"
EXPLAIN SELECT id FROM product WHERE attributes @> '{"color": "red"}'::jsonb;
                                    -- => Bitmap Heap Scan + Bitmap Index Scan on idx_product_attributes_gin
                                    -- => the GIN index serves the containment predicate directly

                                          QUERY PLAN
-------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on product  (cost=254.24..1505.71 rows=33397 width=4)
   Recheck Cond: (attributes @> '{"color": "red"}'::jsonb)
   ->  Bitmap Index Scan on idx_product_attributes_gin  (cost=0.00..245.89 rows=33397 width=0)
         Index Cond: (attributes @> '{"color": "red"}'::jsonb)
(4 rows)

-- Example 46: BRIN Index on Timeseries.
-- BRIN (co-20, Block Range INdex) stores only a MIN/MAX summary per block RANGE,
-- not one entry per row -- tiny on disk, and effective specifically when a column's
-- values correlate with physical insertion order (append-only timestamps, exactly).
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS event_log CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE event_log(id INTEGER PRIMARY KEY, occurred_at TIMESTAMP NOT NULL);
                                    -- => occurred_at increases MONOTONICALLY with id -- append-only pattern
INSERT INTO event_log(id, occurred_at)
SELECT n, TIMESTAMP '2026-01-01 00:00:00' + (n || ' seconds')::INTERVAL
FROM generate_series(1, 500000) AS n;
                                    -- => 500,000 rows -- one every second, strictly increasing timestamps
 
-- CREATE INDEX ... USING brin (co-20) summarizes each block RANGE (many pages) with
-- just a min/max -- orders of magnitude smaller than a B-tree over the same column.
CREATE INDEX idx_event_log_brin ON event_log USING brin (occurred_at);
CREATE INDEX idx_event_log_btree ON event_log USING btree (occurred_at);
                                    -- => a B-tree over the SAME column, for a direct size comparison ONLY
ANALYZE event_log;
 
SELECT
    pg_size_pretty(pg_relation_size('idx_event_log_brin'))  AS brin_size,
    pg_size_pretty(pg_relation_size('idx_event_log_btree')) AS btree_size;
                                    -- => brin_size is dramatically smaller -- it stores block SUMMARIES, not rows
 
-- Drop the competing B-tree so the plan below is forced to show BRIN's OWN
-- behavior, not the planner simply preferring the (also valid) B-tree instead.
DROP INDEX idx_event_log_btree;
 
-- A range predicate on the correlated column uses BRIN effectively -- it scans
-- only the block RANGES whose min/max summary overlaps the requested window.
EXPLAIN SELECT COUNT(*) FROM event_log
WHERE occurred_at BETWEEN '2026-01-02 00:00:00' AND '2026-01-02 01:00:00';
                                    -- => Bitmap Heap Scan + Bitmap Index Scan on idx_event_log_brin

 brin_size | btree_size
-----------+------------
 24 kB     | 11 MB
(1 row)
 
                                                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3065.18..3065.19 rows=1 width=8)
   ->  Bitmap Heap Scan on event_log  (cost=12.87..3056.78 rows=3362 width=0)
         Recheck Cond: ((occurred_at >= '2026-01-02 00:00:00'::timestamp without time zone) AND (occurred_at <= '2026-01-02 01:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on idx_event_log_brin  (cost=0.00..12.03 rows=22727 width=0)
               Index Cond: ((occurred_at >= '2026-01-02 00:00:00'::timestamp without time zone) AND (occurred_at <= '2026-01-02 01:00:00'::timestamp without time zone))
(5 rows)

-- Example 47: Index Hurts Writes.
-- Every index (co-22) is a SEPARATE structure the engine must update on every
-- INSERT -- more indexes means more write work per row, even though NONE of that
-- work is visible in the INSERT statement's own syntax.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS few_indexes, many_indexes CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- Identical column shapes on both tables -- only the NUMBER of indexes differs,
-- isolating index count as the one variable this benchmark measures.
CREATE TABLE few_indexes(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER, c INTEGER, d INTEGER);
                                    -- => only the PRIMARY KEY's own index -- 1 index total
CREATE TABLE many_indexes(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER, c INTEGER, d INTEGER);
-- Four single-column B-tree indexes, one per non-key column -- each one is
-- ordinary and individually reasonable; the cost being measured is their SUM.
CREATE INDEX idx_many_a ON many_indexes(a);
CREATE INDEX idx_many_b ON many_indexes(b);
CREATE INDEX idx_many_c ON many_indexes(c);
CREATE INDEX idx_many_d ON many_indexes(d);
                                    -- => PRIMARY KEY + 4 more indexes -- 5 indexes total, SAME row shape
 
\timing on
                                    -- => measure both inserts under identical conditions
-- Identical row COUNT, identical VALUES generator -- \timing measures wall-clock
-- duration for the INSERT itself, index maintenance included.
INSERT INTO few_indexes(id, a, b, c, d)
SELECT n, n % 100, n % 200, n % 300, n % 400 FROM generate_series(1, 200000) AS n;
                                    -- => 200,000 rows into the 1-index table
 
INSERT INTO many_indexes(id, a, b, c, d)
SELECT n, n % 100, n % 200, n % 300, n % 400 FROM generate_series(1, 200000) AS n;
                                    -- => the SAME 200,000 rows into the 5-index table
\timing off

Time: 173.268 ms
Time: 708.023 ms

-- Example 48: Index Bloat, Observed.
-- Every UPDATE creates a NEW index entry (MVCC keeps the old row version alive
-- until nothing can see it, co-22) -- repeatedly updating the SAME rows bloats the
-- index with entries pointing at now-dead row versions, until a VACUUM/REINDEX cleans it up.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS counter_row CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- A single indexed INTEGER column is the whole setup -- deliberately simple so
-- the ONLY thing changing across this script is index size, not query shape.
CREATE TABLE counter_row(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);
INSERT INTO counter_row(id, value)
SELECT n, 0 FROM generate_series(1, 20000) AS n;
                                    -- => 20,000 rows -- these SAME rows get updated repeatedly below
CREATE INDEX idx_counter_value ON counter_row(value);
ANALYZE counter_row;
 
SELECT pg_size_pretty(pg_relation_size('idx_counter_value')) AS index_size_before;
                                    -- => a small, freshly-built index
 
-- 20 rounds of updating EVERY row (co-22): each UPDATE leaves the old index entry
-- as dead weight until vacuumed -- 20 x 20,000 = 400,000 dead entries accumulate.
-- PL/pgSQL's DO block runs an anonymous, one-off procedural loop -- there is no
-- plain-SQL way to repeat an UPDATE statement N times without either a loop
-- construct like this or N separate statements.
DO $$
BEGIN
    FOR i IN 1..20 LOOP
        UPDATE counter_row SET value = value + 1;
    END LOOP;
END $$;
 
SELECT pg_size_pretty(pg_relation_size('idx_counter_value')) AS index_size_bloated;
                                    -- => substantially larger -- dead entries from 20 rounds of updates
 
-- REINDEX (co-22) rebuilds the index from scratch, containing ONLY live entries --
-- every dead entry from the update churn above is discarded in the rebuild.
-- REINDEX takes an exclusive lock on the index for its duration (Postgres 12+
-- offers REINDEX CONCURRENTLY to avoid that, at the cost of more total work) --
-- acceptable here in a single-writer teaching script, riskier on a live table.
REINDEX INDEX idx_counter_value;
 
SELECT pg_size_pretty(pg_relation_size('idx_counter_value')) AS index_size_after_reindex;
                                    -- => back down close to the original "before" size

 index_size_before
--------------------
 152 kB
(1 row)
 
 index_size_bloated
---------------------
 2728 kB
(1 row)
 
 index_size_after_reindex
----------------------------
 152 kB
(1 row)

-- Example 49: EXPLAIN Nested Loop.
-- A Nested Loop join (co-24) is the simplest strategy: for EACH row of the outer
-- side, probe the inner side directly -- cheap when the inner probe is an indexed
-- lookup, and cheaper still when PostgreSQL can CACHE repeated inner lookups (Memoize).
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS book, author CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- Deliberately skewed cardinality: 5 authors, 50,000 books -- the LOW-cardinality
-- side (author) is exactly what makes Memoize's caching so effective below.
CREATE TABLE author(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES author(id));
INSERT INTO author(id, name) SELECT n, 'Author ' || n FROM generate_series(1, 5) AS n;
                                    -- => only 5 authors -- FEW distinct values on the join key
INSERT INTO book(id, title, author_id)
SELECT n, 'Book ' || n, 1 + (n % 5) FROM generate_series(1, 50000) AS n;
                                    -- => 50,000 books -- every book's author_id is one of only 5 values
CREATE INDEX idx_book_author_id ON book(author_id);
ANALYZE author;
ANALYZE book;
 
-- Force Nested Loop specifically so the plan is deterministic for this example --
-- production code should NEVER disable other join strategies; this is teaching-only.
-- Disabling the other two strategies is a blunt teaching tool -- it forces
-- the planner's hand so this example's EXPLAIN output is reproducible,
-- regardless of what statistics/cost constants your own Postgres has.
SET enable_hashjoin = off;
SET enable_mergejoin = off;
 
EXPLAIN SELECT a.name, COUNT(*) AS book_count
FROM author a
JOIN book b ON b.author_id = a.id
GROUP BY a.name;
                                    -- => Nested Loop: book (50,000 rows) is the OUTER side (Seq Scan)
                                    -- => author is the INNER side, probed via Index Scan on author_pkey
                                    -- => Memoize wraps the inner probe -- caches each author_id's lookup,
                                    -- => so only 5 UNIQUE index probes actually happen, not 50,000

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 HashAggregate  (cost=2219.88..2219.93 rows=5 width=17)
   Group Key: a.name
   ->  Nested Loop  (cost=0.14..1969.88 rows=50000 width=9)
         ->  Seq Scan on book b  (cost=0.00..819.00 rows=50000 width=4)
         ->  Memoize  (cost=0.14..0.16 rows=1 width=13)
               Cache Key: b.author_id
               Cache Mode: logical
               ->  Index Scan using author_pkey on author a  (cost=0.13..0.15 rows=1 width=13)
                     Index Cond: (id = b.author_id)
(9 rows)

-- Example 50: EXPLAIN Hash Join.
-- A Hash Join (co-24) builds an in-memory hash table from the SMALLER side, then
-- probes it once per row of the larger side -- effective when NEITHER side has a
-- usable index for the join condition, unlike Example 49's indexed Nested Loop.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS book_catalog, publisher CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- Neither table gets an index on the join column -- a deliberate choice that
-- takes Nested Loop and Merge Join both off the table as competitive options.
CREATE TABLE publisher(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO publisher(id, name) SELECT n, 'Publisher ' || n FROM generate_series(1, 500) AS n;
                                    -- => 500 publishers -- the "build" side for the hash table
CREATE TABLE book_catalog(id INTEGER PRIMARY KEY, title TEXT NOT NULL, publisher_id INTEGER NOT NULL);
INSERT INTO book_catalog(id, title, publisher_id)
SELECT n, 'Book ' || n, 1 + (n % 500) FROM generate_series(1, 100000) AS n;
                                    -- => 100,000 books -- the "probe" side, NO index on publisher_id
ANALYZE publisher;
ANALYZE book_catalog;
                                    -- => deliberately no index on book_catalog.publisher_id
 
-- Force Hash Join specifically so the plan is deterministic -- production code
-- should never disable other join strategies; this is teaching-only.
-- With no index on either join column, Postgres would likely pick Hash Join
-- anyway -- these SET statements exist mainly to make the choice deterministic
-- and self-documenting rather than to override a close planner decision.
SET enable_nestloop = off;
SET enable_mergejoin = off;
 
EXPLAIN SELECT p.name, COUNT(*) AS book_count
FROM publisher p
JOIN book_catalog bc ON bc.publisher_id = p.id
GROUP BY p.name;
                                    -- => Hash Join: publisher (500 rows) builds the in-memory hash table
                                    -- => book_catalog (100,000 rows) is scanned once, probing the hash table
                                    -- => Seq Scan on BOTH sides -- neither side has an index to exploit

                                    QUERY PLAN
-------------------------------------------------------------------------------------
 HashAggregate  (cost=2416.97..2421.97 rows=500 width=21)
   Group Key: p.name
   ->  Hash Join  (cost=15.25..1916.97 rows=100000 width=13)
         Hash Cond: (bc.publisher_id = p.id)
         ->  Seq Scan on book_catalog bc  (cost=0.00..1637.00 rows=100000 width=4)
         ->  Hash  (cost=9.00..9.00 rows=500 width=17)
               ->  Seq Scan on publisher p  (cost=0.00..9.00 rows=500 width=17)
(7 rows)

-- Example 51: EXPLAIN Merge Join.
-- A Merge Join (co-24) walks BOTH sides in sorted order simultaneously, like
-- merging two sorted lists -- cheap when both sides are ALREADY sorted (via an
-- index, as here) or when sorting them first is still cheaper than a hash table.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS order_row, customer CASCADE;
                                    -- => resets state -- this example is fully self-contained
-- Both sides get an index on the join column here -- customer via its PRIMARY
-- KEY, order_row via an explicit CREATE INDEX -- giving Merge Join two
-- already-sorted inputs to walk in lockstep.
CREATE TABLE customer(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO customer(id, name) SELECT n, 'Customer ' || n FROM generate_series(1, 20000) AS n;
                                    -- => 20,000 customers, PRIMARY KEY already sorts by id
CREATE TABLE order_row(id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL);
INSERT INTO order_row(id, customer_id)
SELECT n, 1 + (n % 20000) FROM generate_series(1, 100000) AS n;
                                    -- => 100,000 orders -- an index on customer_id gives a SECOND sorted input
CREATE INDEX idx_order_customer_id ON order_row(customer_id);
ANALYZE customer;
ANALYZE order_row;
 
-- Force Merge Join specifically so the plan is deterministic -- production code
-- should never disable other join strategies; this is teaching-only.
-- Disabling the other two strategies again forces a deterministic plan for
-- this teaching example -- Merge Join would otherwise have to compete against
-- Nested Loop's indexed lookups and Hash Join's build-then-probe approach.
SET enable_nestloop = off;
SET enable_hashjoin = off;
 
EXPLAIN SELECT c.name, COUNT(*) AS order_count
FROM customer c
JOIN order_row o ON o.customer_id = c.id
GROUP BY c.name;
                                    -- => Merge Join: BOTH inputs arrive sorted by the join key already --
                                    -- => customer via its PRIMARY KEY index, order_row via idx_order_customer_id
                                    -- => no separate hash table, no per-row index probe -- a single merged pass

                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=6250.70..6450.70 rows=20000 width=22)
   Group Key: c.name
   ->  Merge Join  (cost=0.58..5752.50 rows=99641 width=14)
         Merge Cond: (c.id = o.customer_id)
         ->  Index Scan using customer_pkey on customer c  (cost=0.29..659.29 rows=20000 width=18)
         ->  Index Only Scan using idx_order_customer_id on order_row o  (cost=0.29..3796.80 rows=100000 width=4)
(6 rows)

-- Example 52: Buffers in the Plan.
-- "Buffers" (co-23, shown by DEFAULT in PostgreSQL 18's EXPLAIN ANALYZE) reports
-- shared "hit" (found already in the buffer cache) vs "read" (fetched from disk) --
-- the single best signal for whether a query is actually I/O-bound.
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS book_catalog CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE book_catalog(id INTEGER PRIMARY KEY, isbn TEXT NOT NULL, price NUMERIC(6,2) NOT NULL);
INSERT INTO book_catalog(id, isbn, price)
SELECT n, 'ISBN-' || LPAD(n::TEXT, 9, '0'), (10 + (n % 90))::NUMERIC
FROM generate_series(1, 200000) AS n;
                                    -- => 200,000 rows -- large enough that shared_buffers may not hold it all
 
-- PostgreSQL just wrote these rows, so most pages are ALREADY in the shared
-- buffer cache from the INSERT itself -- expect "shared hit", not "shared read".
EXPLAIN (ANALYZE) SELECT COUNT(*) FROM book_catalog WHERE price > 50;
                                    -- => "Buffers: shared hit=1274" -- every page found already in cache
                                    -- => (PG 18 shows this line automatically -- no BUFFERS option written)
                                    -- => "shared read=N" would appear instead for pages NOT in cache,
                                    -- => e.g. right after a fresh server restart with an empty cache

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3040.61..3040.62 rows=1 width=8) (actual time=18.254..18.255 rows=1.00 loops=1)
   Buffers: shared hit=1274
   ->  Seq Scan on book_catalog  (cost=0.00..2930.20 rows=44165 width=0) (actual time=0.015..14.214 rows=108878.00 loops=1)
         Filter: (price > '50'::numeric)
         Rows Removed by Filter: 91122
         Buffers: shared hit=1274
 Planning:
   Buffers: shared hit=8
 Planning Time: 0.070 ms
 Execution Time: 18.272 ms
(10 rows)

-- Example 53: Stale Stats, Bad Plan.
-- A SKEWED column (co-25) -- one value overwhelmingly common, another genuinely
-- rare -- is exactly where missing statistics mislead the planner into the WRONG
-- physical strategy, not just an inaccurate row-count estimate (Example 25).
SET client_min_messages TO WARNING;
DROP TABLE IF EXISTS skewed_data CASCADE;
                                    -- => resets state -- this example is fully self-contained
CREATE TABLE skewed_data(id INTEGER PRIMARY KEY, category TEXT NOT NULL);
INSERT INTO skewed_data(id, category)
SELECT n, CASE WHEN n % 1000 = 0 THEN 'rare' ELSE 'common' END
FROM generate_series(1, 200000) AS n;
                                    -- => 200,000 rows: 'common' is 99.9%, 'rare' is only 0.1% (200 rows)
CREATE INDEX idx_skewed_category ON skewed_data(category);
                                    -- => the index exists, but NO ANALYZE has run yet on this NEW table
 
-- BEFORE ANALYZE (co-25): the planner has NO real distribution stats for this
-- table -- it cannot yet know 'rare' is genuinely rare (only 0.1% of rows).
EXPLAIN SELECT * FROM skewed_data WHERE category = 'rare';
                                    -- => rows=1000 estimated (PostgreSQL's generic no-stats guess: 0.5%
                                    -- => of 200,000) -- close-ish, but still a GUESS, not measured data
 
ANALYZE skewed_data;
                                    -- => ANALYZE (co-25) samples the table -- now it KNOWS the true
                                    -- => 99.9%/0.1% skew via the most-common-values statistics list
 
-- AFTER ANALYZE: the SAME query, now backed by real, measured selectivity.
EXPLAIN SELECT * FROM skewed_data WHERE category = 'rare';
                                    -- => rows=200 estimated -- matches the TRUE count exactly, because
                                    -- => 'rare' is now a tracked most-common-value with its real frequency

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on skewed_data  (cost=12.04..1129.03 rows=1000 width=36)
   Recheck Cond: (category = 'rare'::text)
   ->  Bitmap Index Scan on idx_skewed_category  (cost=0.00..11.79 rows=1000 width=0)
         Index Cond: (category = 'rare'::text)
(4 rows)
 
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using idx_skewed_category on skewed_data  (cost=0.29..16.49 rows=227 width=10)
   Index Cond: (category = 'rare'::text)
(2 rows)

# pyright: strict
# psycopg's native type stubs let pyright resolve every DB-API call below
# to a concrete type -- no third-party stub package or Any fallback needed.
"""Example 54: N+1 Reproduce."""
 
# time.perf_counter() (used below) is the standard-library choice for measuring
# short wall-clock durations -- monotonic, unaffected by system clock adjustments.
import time
 
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
# A single connection is enough here -- unlike Examples 26/27, this script
# demonstrates a WITHIN-one-session performance anti-pattern, not cross-session
# concurrency.
 
 
# setup() seeds a 1:1 author-to-book ratio (500 authors, 500 books) --
# deliberately the WORST case for N+1: every single book row needs its own
# separate author lookup, none of which can be batched or reused.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create an author/book pair sized to make N+1 overhead visible."""
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        cur.execute("DROP TABLE IF EXISTS book, author CASCADE")
        cur.execute("CREATE TABLE author(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        # author_id is NOT NULL -- every book is guaranteed to trigger exactly one
        # inner lookup below, with no NULL-author edge case to complicate the count.
        cur.execute(
            "CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, "
            "author_id INTEGER NOT NULL REFERENCES author(id))"
        )
        # String literals are split across two lines here purely for line-length --
        # Postgres and psycopg both see this as ONE ordinary SQL statement string.
        cur.execute(
            "INSERT INTO author(id, name) SELECT n, 'Author ' || n "
            "FROM generate_series(1, 500) AS n"
        )
        # 1 + (n % 500) cycles author_id through 1..500 in lockstep with book's own
        # n -- guaranteeing the promised 1 book per author, no author left un-referenced.
        cur.execute(
            "INSERT INTO book(id, title, author_id) "
            "SELECT n, 'Book ' || n, 1 + (n % 500) FROM generate_series(1, 500) AS n"
        )
        # => 500 authors, 500 books -- one book per author (co-26): worst case for N+1
    conn.commit()
 
 
# main() times ONLY the query-execution portion of the N+1 pattern -- setup()
# and connection handling run and complete before the perf_counter() clock starts.
def main() -> None:  # => the script's entry point
    conn = psycopg.connect(DSN)
    setup(conn)
 
    # Capturing the start time AFTER setup() completes isolates what this example
    # actually wants to measure: the cost of the N+1 QUERY pattern itself.
    started = time.perf_counter()
    with conn.cursor() as outer:
        # ORDER BY id makes book iteration order deterministic -- irrelevant to
        # correctness here, but it keeps repeated runs directly comparable.
        outer.execute("SELECT id, title, author_id FROM book ORDER BY id")
        books = outer.fetchall()
        # => 1 query for all books -- looks efficient so far
        # Leading underscores on _book_id/_title signal "deliberately unused" -- only
        # author_id is needed to drive the inner per-row lookup below; pyright --strict
        # would otherwise flag genuinely unused bindings depending on lint configuration.
        for _book_id, _title, author_id in books:
            # Opening a FRESH cursor per iteration (rather than reusing one) mirrors how
            # many ORMs implement lazy-loaded relationships -- one new cursor/statement per
            # accessed related object, which is the crux of the N+1 anti-pattern.
            with conn.cursor() as inner:
                # => N+1 (co-26): a SEPARATE round trip to the server for EVERY row
                # => above -- 500 extra queries, each paying full network + parse cost
                # %s with a separate parameter tuple is psycopg's parameterized-query syntax --
                # it prevents SQL injection and lets Postgres reuse a cached query plan across
                # calls, but does NOT reduce the round-trip COUNT this example is measuring.
                inner.execute("SELECT name FROM author WHERE id = %s", (author_id,))
                inner.fetchone()
    # Elapsed time here includes 501 total network round trips: 1 for the book
    # list plus 500 individual author lookups, each paying its own latency.
    elapsed = time.perf_counter() - started
    print(f"N+1 pattern: {len(books)} books, {len(books) + 1} total queries")
    # => Output: N+1 pattern: 500 books, 501 total queries
    print(f"Elapsed: {elapsed:.3f}s")
    # => Output: Elapsed: 0.106s (captured; absolute timing is machine-dependent)
 
    # A single connection.close() is sufficient -- every cursor above was already
    # closed automatically by its own `with` block on exit.
    conn.close()  # => always close what you open
 
 
# Example 55 and 56 both fix this exact N+1 pattern -- one via a JOIN, one via
# a single IN (...) batched lookup -- reusing this SAME schema and seed data.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script

N+1 pattern: 500 books, 501 total queries
Elapsed: 0.106s

# pyright: strict
# Same psycopg strict-typing setup as Example 54 -- only main()'s query strategy
# differs between the two files.
"""Example 55: N+1 Fix, JOIN."""
 
import time
 
# No new imports beyond Example 54's time and psycopg -- the fix needs no
# additional library support, just a different SQL statement.
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
# One connection is all the FIXED version needs too -- the fix is about QUERY
# shape, not about connection or session strategy.
 
 
# Byte-for-byte the SAME setup() as Example 54 -- an identical dataset is what
# makes the elapsed-time comparison between the two scripts meaningful.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create the SAME author/book shape as Example 54, for a fair comparison."""
    # Same cursor-per-call pattern as every earlier psycopg example in this topic --
    # see Example 26 for the underlying context-manager mechanics.
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        # Resetting both tables guarantees this script's 500/500 row counts and its
        # elapsed-time comparison against Example 54 both start from identical state.
        cur.execute("DROP TABLE IF EXISTS book, author CASCADE")
        cur.execute("CREATE TABLE author(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        # Every book row again has a NOT NULL author_id -- the JOIN below can rely on
        # finding a matching author for each and every book, no OUTER join needed.
        cur.execute(
            "CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, "
            "author_id INTEGER NOT NULL REFERENCES author(id))"
        )
        # Author rows are seeded identically to Example 54 -- 500 authors, sequential
        # ids -- so the JOIN below has exactly one matching author per book, always.
        cur.execute(
            "INSERT INTO author(id, name) SELECT n, 'Author ' || n "
            "FROM generate_series(1, 500) AS n"
        )
        # Same 1 + (n % 500) cycling as Example 54 -- every book maps to exactly one
        # of the 500 authors, with no book left unmatched.
        cur.execute(
            "INSERT INTO book(id, title, author_id) "
            "SELECT n, 'Book ' || n, 1 + (n % 500) FROM generate_series(1, 500) AS n"
        )
    # Committing here makes the seeded rows visible to the SAME connection's next
    # statement -- unlike Examples 26/27, there is only one session in play, so
    # this commit is about durability/visibility bookkeeping, not cross-session concurrency.
    conn.commit()
 
 
# main() runs the FIXED query strategy: one JOIN instead of one query-per-row
# -- everything else (setup, timing harness, print format) mirrors Example 54
# closely enough that the elapsed-time numbers are directly comparable.
def main() -> None:  # => the script's entry point
    # A single connect() call -- Example 54 also used just one connection, since
    # N+1 is a WITHIN-session anti-pattern, not a multi-session one.
    conn = psycopg.connect(DSN)
    # setup(conn) is called once, exactly as in Example 54, before any timing
    # begins.
    setup(conn)
 
    # Timing starts after setup(), exactly as in Example 54 -- so both scripts
    # measure only their OWN query-execution strategy, not connection/seed overhead.
    started = time.perf_counter()
    with conn.cursor() as cur:
        # => the FIX (co-26): pull book + author together in ONE query via JOIN --
        # => the database does the row-matching work it is built for, in one pass
        # JOIN ON a.id = b.author_id is the ordinary equi-join pattern used throughout
        # this topic's SQL examples -- the fix here is entirely about NOT issuing a
        # separate query per book, not about any exotic join syntax.
        cur.execute(
            "SELECT b.id, b.title, a.name FROM book b JOIN author a ON a.id = b.author_id "
            "ORDER BY b.id"
        )
        # fetchall() materializes the ENTIRE joined result set in one call -- the
        # server did the row-matching; the client just receives the finished rows.
        rows = cur.fetchall()
        # => exactly 1 round trip total, vs 501 in Example 54's N+1 version
    elapsed = time.perf_counter() - started
    print(f"JOIN fix: {len(rows)} books, 1 total query")
    # => Output: JOIN fix: 500 books, 1 total query
    print(f"Elapsed: {elapsed:.3f}s")
    # => Output: Elapsed: 0.001s (captured) -- roughly two orders of magnitude faster
    # Both this fixed version's elapsed time and Example 54's are captured on the
    # same class of hardware -- the RELATIVE gap between them is the point, not
    # either absolute number in isolation.
 
    conn.close()  # => always close what you open
 
 
# Run Example 54 and this file back to back to see the elapsed-time gap
# directly -- same data, same result set, radically different round-trip count.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script

JOIN fix: 500 books, 1 total query
Elapsed: 0.001s

# pyright: strict
# Same strict-typing setup as Examples 54-55 -- psycopg's own stubs cover
# every DB-API call here without any extra typing scaffolding.
"""Example 56: N+1 Fix, IN Clause."""
 
import time
 
# time is the only extra import beyond psycopg -- this fix needs no additional
# library support, just a different query shape and one in-memory dict.
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
 
 
# The THIRD identical copy of this setup() (see Examples 54 and 55) -- three
# scripts sharing one dataset is what makes their elapsed-time numbers a fair
# three-way comparison of N+1, JOIN, and batched-IN strategies.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create the SAME author/book shape as Examples 54-55, for a fair comparison."""
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        cur.execute("DROP TABLE IF EXISTS book, author CASCADE")
        cur.execute("CREATE TABLE author(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        # The FK still guarantees every book has a real author_id to look up -- no
        # NULL/missing-author edge case for the batched query below to special-case.
        cur.execute(
            "CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, "
            "author_id INTEGER NOT NULL REFERENCES author(id))"
        )
        # Same 500-author seed as Examples 54-55 -- ids 1 through 500, sequential,
        # no gaps.
        cur.execute(
            "INSERT INTO author(id, name) SELECT n, 'Author ' || n "
            "FROM generate_series(1, 500) AS n"
        )
        # Same 1 + (n % 500) author_id cycling as Examples 54-55 -- 500 books, one
        # per author, identical dataset shape across all three N+1 scripts.
        cur.execute(
            "INSERT INTO book(id, title, author_id) "
            "SELECT n, 'Book ' || n, 1 + (n % 500) FROM generate_series(1, 500) AS n"
        )
    conn.commit()
 
 
# main() demonstrates the SECOND fix strategy: batch every needed author_id
# into ONE query via ANY(...), rather than Example 55's JOIN or Example 54's
# one-query-per-row loop.
def main() -> None:  # => the script's entry point
    conn = psycopg.connect(DSN)
    setup(conn)
 
    # Timing again starts after setup() completes, matching Examples 54 and 55's
    # measurement boundary exactly.
    started = time.perf_counter()
    with conn.cursor() as outer:
        # Identical first query to Example 54's N+1 version -- fetch every book in
        # ONE round trip; the difference between all three scripts is entirely in
        # what happens NEXT.
        outer.execute("SELECT id, title, author_id FROM book ORDER BY id")
        books = outer.fetchall()
        # => 1 query for all books -- same starting point as Example 54's N+1 version
        # A set comprehension deduplicates author_ids BEFORE the query runs -- with
        # only 500 authors shared across 500 books there is no duplication here, but
        # a more skewed dataset (many books per author) would make this dedup step
        # essential for keeping the batched query small.
        author_ids = list({row[2] for row in books})
        # => collect the DISTINCT author_ids needed -- a set, so duplicates cost nothing
        with conn.cursor() as inner:
            # => the FIX (co-26): ONE batched query using ANY(%s) with a Python list
            # => param -- fetches every needed author row in a SINGLE round trip
            # ANY(%s) with a Python list parameter is psycopg's idiom for a parameterized
            # IN-list -- it avoids building a dynamic SQL string with one placeholder per
            # id, which would need to change shape based on how many ids happen to exist.
            inner.execute(
                "SELECT id, name FROM author WHERE id = ANY(%s)", (author_ids,)
            )
            # dict(...) over a list of (id, name) 2-tuples is a direct, idiomatic way to
            # turn a fetched result set into an id-keyed lookup table.
            authors = dict(inner.fetchall())
            # => an in-memory dict for O(1) local lookups -- no further database calls
        # This loop iterates the SAME 500 books as Example 54's version -- the
        # difference is that every author_id lookup below is now a local dict access,
        # not a fresh database round trip.
        for _book_id, _title, author_id in books:
            # The lookup result is not stored anywhere -- this loop exists purely to
            # prove every book's author IS present in the dict, with zero further queries.
            authors[author_id]
            # => local dict lookup, zero network cost -- this is where the win pays off
    elapsed = time.perf_counter() - started
    print(f"IN-clause fix: {len(books)} books, 2 total queries")
    # => Output: IN-clause fix: 500 books, 2 total queries
    print(f"Elapsed: {elapsed:.3f}s")
    # => Output: Elapsed: 0.002s (captured) -- comparable to Example 55's JOIN fix
 
    # One connection, one close() -- identical resource-cleanup shape to
    # Examples 54 and 55.
    conn.close()  # => always close what you open
 
 
# Compare this script's "2 total queries" against Example 54's 501 and
# Example 55's 1 -- three different trade-offs for solving the identical
# N+1 problem.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script

IN-clause fix: 500 books, 2 total queries
Elapsed: 0.002s

# pyright: strict
# Same strict-typing baseline as every other psycopg example in this topic --
# no extra stub configuration needed beyond the installed psycopg package.
"""Example 57: Repeatable Read Anomaly (Prevented)."""
 
# No time import needed here -- this example measures TRANSACTION ISOLATION
# behavior, not elapsed wall-clock performance.
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
 
 
# A single-row account table is the simplest possible setup for demonstrating
# a read-read-read anomaly window between two concurrent sessions.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create and seed a single-row account table for the isolation demo."""
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        cur.execute("DROP TABLE IF EXISTS account CASCADE")
        # NUMERIC(10,2) again avoids floating-point rounding error for money values --
        # the assertion below (first_read == second_read) must be an EXACT match.
        cur.execute(
            "CREATE TABLE account(id INTEGER PRIMARY KEY, balance NUMERIC(10,2) NOT NULL)"
        )
        # balance starts at 500.00 -- session B will change it to 400.00 mid-transaction
        # to test whether session A's REPEATABLE READ snapshot notices.
        cur.execute("INSERT INTO account(id, balance) VALUES (1, 500.00)")
    conn.commit()
 
 
# This is the DIRECT counterpart to Example 27's default-isolation anomaly --
# same interleaving, but wrapped in REPEATABLE READ instead of READ COMMITTED
# to show the SAME code pattern producing a DIFFERENT (correct) result.
def main() -> None:  # => the script's entry point
    session_a = psycopg.connect(DSN)  # => session A: reads under REPEATABLE READ
    session_b = psycopg.connect(DSN)  # => session B: writes between A's two reads
    setup(session_a)
 
    with session_a.cursor() as cur_a:
        # Explicitly requesting REPEATABLE READ matters -- Postgres's default (READ
        # COMMITTED) would let session B's mid-transaction commit become visible on
        # the very next SELECT, reproducing Example 27's bug instead of fixing it.
        cur_a.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
        # => explicit REPEATABLE READ (co-13) -- unlike Example 27's default READ
        # => COMMITTED, this takes ONE consistent snapshot for the WHOLE transaction
        cur_a.execute("SELECT balance FROM account WHERE id = 1")
        first_read = cur_a.fetchone()
        print(f"Session A first read: {first_read}")
        # => Output: Session A first read: (Decimal('500.00'),)
 
        # Session B is a COMPLETELY separate connection and transaction -- from
        # Postgres's point of view these two sessions have no relationship beyond
        # both touching the same row.
        with session_b.cursor() as cur_b:  # => session B: independent, commits mid-flight
            cur_b.execute("UPDATE account SET balance = 400.00 WHERE id = 1")
            session_b.commit()
            # => session B commits a DIFFERENT value WHILE session A's transaction
            # => is still open -- the SAME interleaving that broke Example 27
 
        cur_a.execute("SELECT balance FROM account WHERE id = 1")
        second_read = cur_a.fetchone()
        print(f"Session A second read: {second_read}")
        # => Output: Session A second read: (Decimal('500.00'),)
        # => UNCHANGED (co-14): REPEATABLE READ's snapshot was taken at BEGIN and
        # => never moves -- session B's commit is INVISIBLE until A's own transaction ends
        assert first_read == second_read
        # => proves the anomaly from Example 27 is GONE under REPEATABLE READ
        # Committing session A's transaction here ends its snapshot -- any FUTURE
        # query on this connection will take a brand-new one.
        session_a.commit()
 
    # A fresh cursor block does not by itself start a new transaction in
    # psycopg's default autocommit=False mode -- but the PRIOR commit() above
    # already closed the old transaction, so this next execute() implicitly
    # opens a new one with a fresh snapshot.
    with session_a.cursor() as cur_a:
        cur_a.execute("SELECT balance FROM account WHERE id = 1")
        after_commit = cur_a.fetchone()
        print(f"Session A read after commit: {after_commit}")
        # => Output: Session A read after commit: (Decimal('400.00'),)
        # => a NEW transaction takes a NEW snapshot -- now B's committed value IS visible
 
    session_a.close()  # => always close what you open
    session_b.close()  # => both sessions cleaned up
 
 
# Compare this script's assertion (first_read == second_read, PROVEN true)
# against Example 27, where the equivalent assertion would FAIL -- that
# contrast is the entire teaching point of REPEATABLE READ isolation.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script

Session A first read: (Decimal('500.00'),)
Session A second read: (Decimal('500.00'),)
Session A read after commit: (Decimal('400.00'),)

# pyright: strict
# Same strict-typing baseline as Example 57 -- no extra stub configuration
# needed beyond the installed psycopg package.
"""Example 58: Phantom Read (Prevented by PostgreSQL's Snapshot Isolation)."""
 
# No time import -- like Example 57, this measures ISOLATION behavior, not
# elapsed performance.
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
 
 
# A phantom read is a DIFFERENT anomaly from Example 57's non-repeatable read:
# instead of an EXISTING row changing value, a NEW row appears that matches
# a range condition the transaction already evaluated.
# The word "phantom" comes directly from the SQL-92 standard's terminology --
# it is the third classic isolation anomaly alongside dirty reads and
# non-repeatable reads.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create a small order table for the phantom-read demo."""
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        cur.execute("DROP TABLE IF EXISTS order_amount CASCADE")
        cur.execute(
            "CREATE TABLE order_amount(id INTEGER PRIMARY KEY, amount NUMERIC(10,2) NOT NULL)"
        )
        # Three starting rows, deliberately split around the amount > 100 boundary --
        # this makes the COUNT(*) below a clean, easy-to-follow number (2), not an
        # edge case at exactly 100.
        cur.execute(
            "INSERT INTO order_amount(id, amount) VALUES (1, 150.00), (2, 200.00), (3, 50.00)"
        )
        # => 2 rows already match "amount > 100" (id=1, id=2) -- id=3 does not
    conn.commit()
 
 
# This is Example 57's REPEATABLE READ demo generalized from a single-row
# UPDATE to a range-matching INSERT -- proving PostgreSQL's snapshot isolation
# blocks phantom rows the same way it blocks non-repeatable reads.
def main() -> None:  # => the script's entry point
    session_a = psycopg.connect(DSN)  # => session A: runs the SAME range query twice
    session_b = psycopg.connect(DSN)  # => session B: inserts a NEW matching row between reads
    setup(session_a)
 
    with session_a.cursor() as cur_a:
        # Same explicit ISOLATION LEVEL REPEATABLE READ as Example 57 -- Postgres's
        # default READ COMMITTED would let session B's new row show up immediately.
        cur_a.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
        # COUNT(*) with a range predicate, not a single-row lookup by id -- phantoms
        # are specifically about ROW SETS that satisfy a condition, not individual
        # row values.
        cur_a.execute("SELECT COUNT(*) FROM order_amount WHERE amount > 100")
        first_count = cur_a.fetchone()
        print(f"Session A first count: {first_count}")
        # => Output: Session A first count: (2,)
 
        with session_b.cursor() as cur_b:  # => session B: inserts a NEW row that matches the range
            cur_b.execute("INSERT INTO order_amount(id, amount) VALUES (4, 300.00)")
            session_b.commit()
            # => a fresh row matching "amount > 100" now exists and is COMMITTED --
            # => the SQL standard calls a newly-visible matching row a "phantom"
 
        # Re-running the IDENTICAL range query inside the same still-open transaction --
        # under the SQL standard's minimum REPEATABLE READ, this count would be
        # permitted to change to 3.
        cur_a.execute("SELECT COUNT(*) FROM order_amount WHERE amount > 100")
        second_count = cur_a.fetchone()
        print(f"Session A second count: {second_count}")
        # => Output: Session A second count: (2,)
        # => STILL 2 (co-14): the SQL standard's REPEATABLE READ still permits phantoms,
        # => but PostgreSQL's REPEATABLE READ is true snapshot isolation -- STRONGER than
        # => the standard requires -- so the new row stays invisible for this whole transaction
        assert first_count == second_count
        # => proves PostgreSQL's actual guarantee exceeds the SQL standard's minimum here
        session_a.commit()
 
    session_a.close()  # => always close what you open
    session_b.close()  # => both sessions cleaned up
 
 
# The row inserted by session B is NOT lost -- it becomes visible to session A
# the moment A starts a fresh transaction, exactly like Example 57's committed
# balance update.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script

Session A first count: (2,)
Session A second count: (2,)

# pyright: strict
# Same strict-typing baseline as Examples 57-58 -- no extra stub configuration
# needed beyond the installed psycopg package.
"""Example 59: Write Skew."""
 
# No time import -- write skew is a CORRECTNESS anomaly, not a performance one.
import psycopg
 
DSN = "host=localhost port=55432 dbname=asqp user=asqp password=asqp"
# => connection string -- readers should substitute their own PostgreSQL 18 instance
 
 
# Write skew is the most subtle of the three anomalies in Examples 57-59: no
# single row is read-then-written twice (that would be a lock-detectable
# conflict) -- instead, TWO DIFFERENT rows are each modified based on a shared
# read of the OTHER row's state, silently breaking a cross-row invariant.
def setup(conn: psycopg.Connection) -> None:  # => resets state -- fully self-contained
    """Create two on-call doctors; the invariant is: at least one stays on call."""
    with conn.cursor() as cur:
        cur.execute("SET client_min_messages TO WARNING")
        cur.execute("DROP TABLE IF EXISTS doctor_on_call CASCADE")
        # on_call is a plain BOOLEAN -- the invariant this example breaks is an
        # APPLICATION-level rule ("never let both go off call"), not a database
        # CHECK constraint, which is exactly why the database cannot catch it alone.
        cur.execute(
            "CREATE TABLE doctor_on_call(id INTEGER PRIMARY KEY, name TEXT NOT NULL, "
            "on_call BOOLEAN NOT NULL)"
        )
        cur.execute(
            "INSERT INTO doctor_on_call(id, name, on_call) VALUES "
            "(1, 'Dr. Alice', TRUE), (2, 'Dr. Bob', TRUE)"
        )
        # => BOTH doctors start on call -- the invariant "at least 1 on call" holds
    conn.commit()
 
 
# main() interleaves two REPEATABLE READ transactions so that each one reads
# the invariant as satisfied, then independently makes a change that -- taken
# together -- violates it.
def main() -> None:  # => the script's entry point
    session_a = psycopg.connect(DSN)  # => session A: will take Dr. Alice off call
    session_b = psycopg.connect(DSN)  # => session B: will take Dr. Bob off call
    setup(session_a)
 
    # Both cursors share this one `with` statement purely for readability -- there is
    # no synchronization between them; each connection's transaction proceeds fully
    # independently on the Postgres server.
    with session_a.cursor() as cur_a, session_b.cursor() as cur_b:
        cur_a.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
        cur_b.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
        # => BOTH transactions open BEFORE either writes -- each gets its OWN
        # => snapshot showing 2 doctors on call (co-14, co-15)
 
        # Each session independently checks the SAME invariant against its own
        # snapshot -- neither has any way to know the OTHER session is about to
        # act on an overlapping decision.
        cur_a.execute("SELECT COUNT(*) FROM doctor_on_call WHERE on_call = TRUE")
        count_seen_by_a = cur_a.fetchone()
        print(f"Session A sees on-call count: {count_seen_by_a}")
        # => Output: Session A sees on-call count: (2,)
 
        cur_b.execute("SELECT COUNT(*) FROM doctor_on_call WHERE on_call = TRUE")
        count_seen_by_b = cur_b.fetchone()
        print(f"Session B sees on-call count: {count_seen_by_b}")
        # => Output: Session B sees on-call count: (2,)
        # => BOTH sessions independently conclude "2 on call, safe for MY doctor to go off"
 
        # Session A writes row id=1 -- this UPDATE touches ONLY Dr. Alice's row, never
        # id=2, so it cannot conflict with anything session B is about to do.
        cur_a.execute("UPDATE doctor_on_call SET on_call = FALSE WHERE id = 1")
        session_a.commit()
        print("Session A took Dr. Alice off call and committed")
 
        # Session B writes the DIFFERENT row id=2 -- Postgres's REPEATABLE READ
        # conflict detection only fires a serialization error when two transactions
        # touch the SAME row; disjoint writes to different rows sail through cleanly.
        cur_b.execute("UPDATE doctor_on_call SET on_call = FALSE WHERE id = 2")
        session_b.commit()
        # => REPEATABLE READ only detects conflicts on the SAME row -- session A
        # => wrote id=1, session B wrote id=2 -- NO row overlap, so NO conflict is
        # => detected, and BOTH commits succeed (co-15) despite violating the invariant
        print("Session B took Dr. Bob off call and committed (no conflict detected)")
 
    # A brand-new transaction (fresh snapshot) is required here to see the TRUE
    # current state -- the earlier snapshots inside session A and B's transactions
    # are now closed and would still show stale, pre-update data.
    with session_a.cursor() as cur_check:
        cur_check.execute("SELECT COUNT(*) FROM doctor_on_call WHERE on_call = TRUE")
        final_count = cur_check.fetchone()
        print(f"Final on-call count: {final_count}")
        # => Output: Final on-call count: (0,)
        # => the invariant "at least 1 on call" is now VIOLATED -- classic write skew:
        # => two disjoint writes, each individually valid against its own stale snapshot
 
    session_a.close()  # => always close what you open
    session_b.close()  # => both sessions cleaned up
 
 
# Preventing write skew (not shown here) requires either SERIALIZABLE isolation
# (which detects this exact dependency pattern) or an explicit SELECT ... FOR
# UPDATE / advisory lock that forces the two sessions to conflict on purpose.
# SERIALIZABLE would abort one of the two transactions with a serialization
# failure, forcing the application to retry -- a strictly stronger guarantee
# than REPEATABLE READ provides for this cross-row scenario.
if __name__ == "__main__":  # => guards against running main() on `import example`
    main()  # => entry point -- runs everything above when executed as a script