# 6.5 The Kitchen Chaos (Case Study)  In Chapter 3, we met the **[[Chapter 3/3.3 - The Waiters|Waiters]]** who execute the Head Chef's service plan. Now, we analyze a real **Service Receipt** (`EXPLAIN ANALYZE`) to bridge the theoretical strategy with the harsh reality of the **[[Chapter 6/6.0 - The Waiting Game|Waiting Game]]**. ## The Filter at the Source (Predicate Pushdown) Imagine the elephant needs to find all plates (rows) belonging to "Sarah." In a perfect service shift, the Head Chef uses a **Predicate Pushdown**. He doesn't just tell the Runners to "Bring up everything." Instead, he shouts down the kitchen laundry chute: _"Runners! Only bring up plates where the name tag says 'Sarah'!"_ This ruthlessly minimizes the volume of data that ever reaches the higher, more expensive layers of execution. Because the filter is "pushed down" to the bottom of the kitchen team, the Runners (using an **[[Chapter 2/2.1 - The B-Tree|Index Scan]]**) only have to carry a few plates up the stairs. The room remains quiet, and the CPU is cool. ## The SARGability Trap But what if you ask for `upper(name) = 'SARAH'`? Suddenly, the Runners are paralyzed. They have the Librarian's Map for `name`, but they don't have one for `upper(name)`. They can't "push down" the filter because they don't know which plate is which until they open it and perform the `upper()` transformation themselves. It’s like trying to find a blue teapot by painting everything red first! This is a **Non-SARGable** predicate. The Head Chef is forced to order a **[[Chapter 3/3.3 - The Waiters#The Runner (Sequential Scan)|Sequential Scan]]**. Now, every single plate in the depot must be carried up the elevator. - **The Wait Event**: You will see a spike in **`IO:DataFileRead`** as the staff members wait at the **[[Chapter 6/6.2 - The Frozen Pantry Wait|IO Elevator]]** for a never-ending stream of containers. - **The Sweat**: The CPU will hit 100% as the staff members frantically perform the `upper()` calculation on every single row. ## Kitchen Friction (The Hamster Wheel) The most brutal service shift happens during a **JOIN**. ### The Smooth Hand-off (Nested Loop + Index) If you join `Users` to `Orders`, and `Orders` has an index on `user_id`, the staff member for `Users` pulls a plate and hands it to the `Orders` Runner. The Runner uses the map to instantly find the matching orders. It's a quick high-five. ### The Hamster Wheel (Nested Loop - Index) If `Orders` has no index, the worker is trapped on the **[[Chapter 3/3.3 - The Waiters#The Assembler (Joining)|Hamster Wheel]]**. For every single user plate, he must walk the entire `Orders` depot from start to finish. "No room! No room!" - **The Result**: Total silence. No "Wait Events" are recorded because the staff member isn't waiting for the system—he's just running in circles as fast as his legs can carry him. This is **Pure CPU Sweat**. ## The Resource Spill (I/O Wait) Finally, imagine a **[[Chapter 3/3.3 - The Waiters#The Assembler (Joining)|Hash Join]]**. The staff member builds a massive "Buffet Wall" (in `work_mem`) to sort the plates. If the Head Chef underestimated the number of plates, the wall grows too large for the staff member's counter. He is forced to scream in frustration and start dumping the plates into temporary files on the floor. - **The Wait Event**: You will see **`IO:BufFileWrite`** and **`IO:BufFileRead`**. Even though your query didn't change any data, you are now suffering from massive Frozen Pantry waits simply because the staff member's counter was too small. By reading the Service Receipt, you can determine if your query is slow because the service plan was impossible (Logistics) or because the depot is too crowded (Friction). "It’s a victory for the curious!" --- [[Chapter 6/6.4 - Workloads|← 6.4 - Workloads]] | [[Chapter 6/6.0 - The Waiting Game|↑ 6.0 - The Waiting Game]] | [[Chapter 7/7.0 - The Cloud Scales|7.0 - The Cloud Scales →]]