# 3.5 The Masterclass Lab (Advanced Operations)
If the previous lab was about keeping the kitchen orderly, the **Masterclass Lab** is about surviving the worst possible dinner rush.
Under the hood, PostgreSQL uses **37 distinct physical execution nodes** to resolve queries. You have already met the basics: Sequence Scans, Index Scans, Hash Joins, and Aggregates. But when you write a complex, real-world query—spanning millions of rows, hierarchical data, and concurrent locking—Postgres accesses a much deeper, weirder toolkit.
> [!NOTE]
> **The Simulation**: To follow along with these exercises, you will need enough volume to force the planner into panic mode. Run `psql -f scripts/seed_large.sql` on the local `elephant_cafe_db` to spawn 100,000 orders and 10,000 animals. Then, let `ANALYZE` count them.
---
## Challenge 1: The Archival Panic (Bitmap Scans & Parallel Query)
**The Request**: "Count all orders that are either Pending or Cancelled, placed by Herbivores, since 2021."
### The Naive Solution
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders o
JOIN animals a ON o.animal_id = a.id
JOIN species s ON a.species_id = s.id
WHERE (o.status = 'Pending' OR o.status = 'Cancelled')
AND s.diet_type = 'Herbivore'
AND extract(year from o.order_time) > 2020;
```
### The Fallout
Because we used an `OR` condition and wrapped the `order_time` in `extract(year)`, the meticulous database engine cannot rely on a single, clean B-tree index. It panics and falls back to its "in-memory scratchpad" strategy:
```text
-- Result Snippet
-> Bitmap Heap Scan on orders o (cost=558.93..2295.07 rows=14585)
Recheck Cond: ((status = 'Pending') OR (status = 'Cancelled'))
Filter: (EXTRACT(year FROM order_time) > '2020'::numeric)
-> BitmapOr
-> Bitmap Index Scan on idx_orders_status (status = 'Pending')
-> Bitmap Index Scan on idx_orders_status (status = 'Cancelled')
```
1. **`Bitmap Index Scan`**: Instead of fetching the rows, Postgres merely builds an in-memory bitmap (a checklist of 1s and 0s) representing which rows match 'Pending', and another for 'Cancelled'.
2. **`BitmapOr`**: It logically merges the two checklists together.
3. **`Bitmap Heap Scan`**: It finally walks over to the table and pulls out the suitcases matching the merged checklist, applying the non-sargable `EXTRACT` filter post-fetch.
### The Lazy Fix
Refactor the `OR` to `IN()` and unwrap the date to enable better cost estimation.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders o
JOIN animals a ON o.animal_id = a.id
JOIN species s ON a.species_id = s.id
WHERE o.status IN ('Pending', 'Cancelled')
AND s.diet_type = 'Herbivore'
AND o.order_time >= '2021-01-01';
```
```text
-- Result:
Aggregate (cost=2729.34..2729.35 rows=1 width=8) (actual time=5.980..5.981 rows=1 loops=1)
Buffers: shared hit=857
-> Hash Join (cost=805.72..2679.34 rows=20001 width=0) (actual time=1.183..5.605 rows=20000 loops=1)
Hash Cond: (o.animal_id = a.id)
Buffers: shared hit=857
-> Bitmap Heap Scan on orders o (cost=532.13..2018.24 rows=50002 width=4) (actual time=0.388..2.307 rows=50000 loops=1)
Recheck Cond: (status = ANY ('{Pending,Cancelled}'::order_status[]))
Filter: (order_time >= '2021-01-01'::timestamp)
Heap Blocks: exact=736
Buffers: shared hit=782
-> Bitmap Index Scan on idx_orders_status (cost=0.00..519.63 rows=50007 width=0) (actual time=0.357..0.357 rows=50000 loops=1)
Index Cond: (status = ANY ('{Pending,Cancelled}'::order_status[]))
Buffers: shared hit=46
```
---
## Challenge 2: The Supply Chain Labyrinth (Recursive CTEs)
**The Request**: "Trace a dish's ingredient lineage all the way down the supply chain."
### The Naive Solution
Using `WITH RECURSIVE` enables powerful graph traversal. But adding a non-sargable string cast outside the CTE acts as an Optimization Fence.
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE supply_chain AS (
SELECT id, ingredient_id FROM supply_deliveries
UNION ALL
SELECT sd.id, sd.ingredient_id FROM supply_deliveries sd
JOIN supply_chain sc ON sd.id = sc.id + 1
)
SELECT * FROM supply_chain sc
WHERE sc.id::text = '1';
```
### The Fallout
Postgres recursively builds the *entire* supply chain in memory (100,000+ rows) before it throws 99% of it away.
```text
-- Result Snippet
CTE Scan on supply_chain sc (cost=3609.22..6386.73 rows=505)
Filter: ((id)::text = '1'::text)
CTE supply_chain
-> Recursive Union
-> Seq Scan on supply_deliveries
-> Hash Join
-> WorkTable Scan on supply_chain sc_1
-> Seq Scan on supply_deliveries sd
```
A **`WorkTable Scan`** is Postgres reading its own temporary CTE results to feed back into the **`Recursive Union`**. Because of `sc.id::text`, the filter is not pushed down.
### The Lazy Fix
Push the exact filter directly into the base condition of the CTE.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE supply_chain AS (
SELECT id, ingredient_id FROM supply_deliveries WHERE id = 1
UNION ALL
SELECT sd.id, sd.ingredient_id FROM supply_deliveries sd
JOIN supply_chain sc ON sd.id = sc.id + 1
)
SELECT * FROM supply_chain;
```
```text
-- Result:
CTE Scan on supply_chain (cost=243.55..245.57 rows=101 width=12) (actual time=0.013..50.087 rows=1000 loops=1)
Buffers: shared hit=8006
CTE supply_chain
-> Recursive Union (cost=0.28..243.55 rows=101 width=12) (actual time=0.012..49.992 rows=1000 loops=1)
Buffers: shared hit=8006
-> Index Scan using supply_deliveries_pkey on supply_deliveries (cost=0.28..8.29 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=6
-> Hash Join (cost=0.33..23.43 rows=10 width=12) (actual time=0.026..0.050 rows=1 loops=1000)
Hash Cond: (sd.id = (sc.id + 1))
Buffers: shared hit=8000
```
---
## Challenge 3: The Analytics Abyss (Window Functions)
**The Request**: "Give me the top 3 largest deliveries per quarter."
### The Naive Solution
Using `row_number() OVER (PARTITION BY ...)` is standard data warehousing. But the partition key determines how the engine groups data.
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, quantity_kg,
row_number() OVER (PARTITION BY extract(quarter from delivery_time) ORDER BY quantity_kg DESC) as rank
FROM supply_deliveries
ORDER BY rank LIMIT 3;
```
### The Fallout
```text
-- Result Snippet
-> WindowAgg (cost=70.33..92.83 rows=1000 width=54)
-> Sort (cost=70.33..72.83 rows=1000 width=46)
Sort Key: (EXTRACT(quarter FROM delivery_time)), quantity_kg DESC
```
Because `EXTRACT` is dynamic, the **`WindowAgg`** node forces an expensive **`Sort`** of all deliveries in memory. If your `work_mem` is small, this will spill to disk and cause terrible performance.
### The Lazy Fix
By partitioning on a predictable truncation like `date_trunc('quarter')`, the meticulous planner avoids deep mathematical evaluation for every tuple, optimizing the group-and-sort execution path.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, quantity_kg, delivery_time,
row_number() OVER (PARTITION BY date_trunc('quarter', delivery_time) ORDER BY quantity_kg DESC) as rank
FROM supply_deliveries
ORDER BY rank LIMIT 3;
```
```text
-- Result:
Limit (cost=105.75..105.76 rows=3 width=38) (actual time=0.473..0.473 rows=3 loops=1)
Buffers: shared hit=11
-> Sort (cost=105.75..108.25 rows=1000 width=38) (actual time=0.473..0.473 rows=3 loops=1)
Sort Key: (row_number() OVER (?))
Sort Method: top-N heapsort Memory: 25kB
-> WindowAgg (cost=70.33..92.83 rows=1000 width=38) (actual time=0.332..0.434 rows=1000 loops=1)
-> Sort (cost=70.33..72.83 rows=1000 width=30) (actual time=0.332..0.356 rows=1000 loops=1)
Sort Key: (date_trunc('quarter'::text, delivery_time)), quantity_kg DESC
```
---
## Challenge 4: The Pantry Deadlock (ModifyTable & LockRows)
**The Request**: "Apply a 10% price markup to all dishes that contain 'Spices'."
### The Naive Solution
We are about to write changes to disk. This means acquiring locks. Let's accidentally use a non-sargable subquery (`lower()`).
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
UPDATE dishes
SET price = price * 1.10
WHERE id IN (
SELECT d.id FROM dishes d
JOIN dish_ingredients di ON d.id = di.dish_id
JOIN ingredients i ON di.ingredient_id = i.id
WHERE lower(i.category::text) = 'spice'
);
```
### The Fallout
Because `lower()` forces a full pass to find matching dishes, the **`ModifyTable`** node (the node that actually commits updates to disk) is fed a fat, slow pipeline of rows from a massive `Hash Semi Join`. This operation will lock rows unpredictably and block access to the `dishes` table.
```text
-- Result:
Update on dishes (cost=5.49..6.63 rows=0 width=0) (actual time=0.196..0.197 rows=0 loops=1)
Buffers: shared hit=67
-> Hash Semi Join (cost=5.49..6.63 rows=1 width=38) (actual time=0.162..0.165 rows=5 loops=1)
Hash Cond: (dishes.id = d.id)
Buffers: shared hit=47
-> Seq Scan on dishes (cost=0.00..1.10 rows=10 width=17)
-> Hash (cost=5.47..5.47 rows=1 width=26)
-> Nested Loop (cost=3.15..5.47 rows=1 width=26)
-> Hash Join (cost=3.01..5.16 rows=1 width=16)
Hash Cond: (di.ingredient_id = i.id)
-> Seq Scan on dish_ingredients di
-> Hash
-> Seq Scan on ingredients i
Filter: (lower((category)::text) = 'spice'::text)
```
### The Lazy Fix
By removing the `lower()` cast, the planner drives the entire process via a crisp parameter pushdown. Postgres will find the specific dishes and update them surgically via Index lookups, locking only what it needs for microscopically short durations.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
UPDATE dishes
SET price = price * 1.10
WHERE id IN (
SELECT d.id FROM dishes d
JOIN dish_ingredients di ON d.id = di.dish_id
JOIN ingredients i ON di.ingredient_id = i.id
WHERE i.category = 'Spice'
);
```
```text
-- Result:
Update on dishes (cost=6.05..7.33 rows=0 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Buffers: shared hit=19
-> Hash Semi Join (cost=6.05..7.33 rows=10 width=38) (actual time=0.021..0.022 rows=5 loops=1)
Hash Cond: (dishes.id = d.id)
Buffers: shared hit=4
-> Seq Scan on dishes
-> Hash
-> Hash Join
Hash Cond: (di.dish_id = d.id)
-> Hash Join
Hash Cond: (di.ingredient_id = i.id)
-> Seq Scan on dish_ingredients di
-> Hash
-> Seq Scan on ingredients i
Filter: (category = 'Spice'::ingredient_category)
```
---
## Challenge 5: The Grand Reconciliation (Merge Join)
**The Request**: "Count how many days we received a delivery on the exact same day an order was placed."
### The Naive Solution
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM supply_deliveries sd
JOIN orders o ON to_char(sd.delivery_time, 'YYYY-MM-DD') = to_char(o.order_time, 'YYYY-MM-DD');
```
### The Fallout
Joining 100,000 orders to deliveries is massive. The `to_char()` function destroys any inherent ordering in the data. Postgres must materialize and sort millions of derived strings into temporary files (notice the `external sort Disk: 3240kB`).
```text
-- Result Snippet
-> Merge Join (cost=10108.65..20118.65 rows=500000 width=0)
-> Sort (cost=67.83..70.33)
Sort Key: (to_char(sd.delivery_time, 'YYYY-MM-DD'::text))
Sort Method: quicksort Memory: 71kB
-> Sort (cost=10040.82..10290.82)
Sort Key: (to_char(o.order_time, 'YYYY-MM-DD'::text))
Sort Method: external sort Disk: 3240kB
```
### The Lazy Fix
Using native datetime geometry (`date_trunc`) allows the engine to compare physical bits efficiently, drastically cutting memory and disk overhead.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM supply_deliveries sd
JOIN orders o ON date_trunc('day', sd.delivery_time) = date_trunc('day', o.order_time);
```
```text
-- Result:
Aggregate (cost=21368.65..21368.66 rows=1 width=8) (actual time=101.080..101.081 rows=1 loops=1)
Buffers: shared hit=744, temp read=5311 written=736
-> Merge Join (cost=10108.65..20118.65 rows=500000 width=0) (actual time=23.392..83.915 rows=936280 loops=1)
Merge Cond: ((date_trunc('day'::text, sd.delivery_time)) = (date_trunc('day'::text, o.order_time)))
Buffers: shared hit=744, temp read=5311 written=736
-> Sort (cost=67.83..70.33)
Sort Key: (date_trunc('day'::text, sd.delivery_time))
Sort Method: quicksort Memory: 64kB
-> Sort (cost=10040.82..10290.82)
Sort Key: (date_trunc('day'::text, o.order_time))
Sort Method: external sort Disk: 2944kB
```
**The Lesson**: The 37 Operations in PostgreSQL are a toolbox. Your poorly-written predicates force the planner to use the chainsaw (Hash Joins, Materialized Sorts) when a scalpel (Index Only Scans, Nested Loops) would have sufficed.
---
## Challenge 6: The Lateral Latency
**The Request**: "Show me the top 3 items for every animal that has placed more than 15 orders."
### The Naive Solution
Using a subquery or a window function with a total sort can be a nightmare for the parser.
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.name, top_dishes.dish_name
FROM animals a
JOIN (
SELECT animal_id, dish_id, row_number() OVER (PARTITION BY animal_id) as rank
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
) top_dishes ON a.id = top_dishes.animal_id
WHERE top_dishes.rank <= 3;
```
### The Fallout
Postgres must calculate the rank for *every single order item* in the entire database before it can filter. This means a massive **WindowAgg** and **Sort** operation that consumes the Warming Rack (Shared Buffers).
### The Lazy Fix
Use a **LATERAL Join**. This allows Postgres to iterate over the animals and, for each one, perform a surgical, indexed-backed lookup of exactly three items.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.name, top_dishes.dish_id
FROM animals a
CROSS JOIN LATERAL (
SELECT oi.dish_id
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.animal_id = a.id
LIMIT 3
) top_dishes
WHERE (SELECT count(*) FROM orders WHERE animal_id = a.id) > 15;
```
**The Reward**: The plan shifts to a **Nested Loop** with a **SubPlan**. As we saw in our testing, while the total buffer count might be high, the "actual time" per row is microscopic. Postgres fetches exactly what it needs and stops.
---
## Challenge 7: The Shadow of the Unknown (Anti-Joins)
**The Request**: "Find the count of animals who have never placed an order."
### The Naive Solution
The `NOT IN` trap is a classic. If the subquery returns even a single `NULL`, the entire query returns zero results.
```sql
-- ❌ The Naive Solution
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM animals
WHERE id NOT IN (SELECT animal_id FROM orders WHERE animal_id IS NOT NULL);
```
### The Fallout
Because `NOT IN` has weird semantics around NULLs, Postgres often falls back to a **Hashed SubPlan** or a full **Sequential Scan** with a filter.
```text
-- Result Snippet:
-> Seq Scan on animals (cost=3971.00..4368.21 rows=9968 width=0) (actual time=20.536..21.786 rows=10000 loops=1)
Filter: (NOT (hashed SubPlan 1))
```
### The Lazy Fix
Use `NOT EXISTS`. It has cleaner semantics and allows Postgres to use a **Hash Anti Join** or an **Index Anti Join**, which are significantly faster and safer.
```sql
-- ✅ The Lazy Fix
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM animals a
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.animal_id = a.id);
```
**The Reward**: Postgres uses a **Hash Right Anti Join**, scanning the orders table once and build a hash table. The animals are then checked against this table in a single pass.
## Challenge 8: The Scent Library (GIN Indexes)
**The Request**: "Find all ingredients that have both 'Spicy' and 'Fruity' scent profiles."
### The Naive Solution
Searching an array using `ANY` or `unnest` filter logic. While readable, this forces the engine to perform a scalar comparison for every element in every row.
```sql
-- ❌ The Naive Solution
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS)
SELECT count(*) FROM flavors
WHERE 'Fruity' = ANY(scent_notes)
AND 'Spicy' = ANY(scent_notes);
```
### The Fallout
Because a standard B-Tree index cannot "see" inside an array, the elephant is forced into a **Sequential Scan**. For a library of thousands of flavor profiles, this means checking every physical suitcase to see if your scents are hidden inside.
```text
-- Result:
Aggregate (actual time=0.015..0.016 rows=1 loops=1)
-> Seq Scan on flavors (actual time=0.008..0.012 rows=0 loops=1)
Filter: (('Fruity'::scent_primary = ANY (scent_notes)) AND ('Spicy'::scent_primary = ANY (scent_notes)))
```
### The Lazy Fix
Create a **GIN (Generalized Inverted Index)**. Unlike a B-Tree, which maps a key to a row, a GIN index maps the *contents* of a column (like individual array elements) to the rows that contain them.
```sql
-- ✅ The Lazy Fix
CREATE INDEX idx_flavors_scent ON flavors USING GIN (scent_notes);
-- Re-run using the Array Containment Operator (@>)
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS)
SELECT count(*) FROM flavors
WHERE scent_notes @> ARRAY['Fruity', 'Spicy']::scent_primary[];
```
**The Reward**: The plan shifts to a **Bitmap Index Scan** on the GIN index. Instead of scanning the table, Postgres consults the inverted index to find the exact list of rows containing both scents and intersects them instantly in memory.
```text
-- Result:
Aggregate (actual time=0.008..0.009 rows=1 loops=1)
-> Bitmap Heap Scan on flavors (actual time=0.004..0.005 rows=0 loops=1)
Recheck Cond: (scent_notes @> '{Fruity,Spicy}'::scent_primary[])
-> Bitmap Index Scan on idx_flavors_scent (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (scent_notes @> '{Fruity,Spicy}'::scent_primary[])
```
**The Lesson**: B-Trees are for sorting and ranges; GIN is for membership. When your data is multi-valued (arrays, tags, JSONB attributes), the Inverted Index is how the elephant remembers where every individual scent is shelved.
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| [[Chapter 3/3.4 - The Performance Lab (Exercises)\|3.4 The Performance Lab (Exercises)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 4/4.0 - Safety Without Sweating (The Write-Ahead Log)\|Chapter 4 - Safety Without Sweating (The Write-Ahead Log)]] |