# 6.5 The Query Battle (Case Study)
In Chapter 3, we met the **[[Chapter 3/3.3 - The Soldiers|Soldiers]]** who execute the General's battle plan. Now, we will look at a real **Battle Report** (`EXPLAIN ANALYZE`) to see how their performance in the field relates to the **[[Chapter 6/6.0 - The Waiting Game|Waiting Game]]**.
## The Filter at the Source (Predicate Pushdown)
Imagine the elephant needs to find all suitcases (rows) belonging to "Sarah."
In a perfect battle, the General uses a **Predicate Pushdown**. He doesn't just tell the Scouts to "Bring up everything." Instead, he shouts down the laundry chute: _"Scouts! Only bring up suitcases where the name tag says 'Sarah'!"_
Because the filter is "pushed down" to the bottom of the army, the Scouts (using an **[[Chapter 2/2.1 - The B-Tree|Index Scan]]**) only have to carry a few suitcases 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 Scouts 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 suitcase is which until they open it and perform the `upper()` transformation themselves.
This is a **Non-SARGable** predicate. The General is forced to order a **[[Operations/SeqScan|Sequential Scan]]**. Now, every single suitcase in the warehouse must be carried up the elevator.
- **The Wait Event**: You will see a spike in **`IO: DataFileRead`** as the soldiers wait at the **[[Chapter 6/6.2 - Disk Wait|IO Elevator]]** for a never-ending stream of containers.
- **The Sweat**: The CPU will hit 100% as the soldiers frantically perform the `upper()` calculation on every single row.
## Join Friction (The Hamster Wheel)
The most brutal battle 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 soldier for `Users` pulls a suitcase and hands it to the `Orders` Scout. The Scout 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 Soldiers#1. The Matchmakers (Joining)|Hamster Wheel]]**. For every single user suitcase, he must walk the entire `Orders` warehouse from start to finish.
- **The Result**: Total silence. No "Wait Events" are recorded because the soldier 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 **[[Operations/HashJoin|Hash Join]]**. The soldier builds a massive "Jellybean Wall" (in `work_mem`) to sort the suitcases.
If the General underestimated the number of suitcases, the wall grows too large for the soldier's desk. He is forced to scream in frustration and start dumping the jellybeans 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 Disk I/O waits simply because the soldier's desk was too small.
By reading the Battle Report, you can determine if your query is slow because the plan was impossible (Logistics) or because the warehouse is too crowded (Friction).
---
[[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 →]]