---image: "Math and Computation/Databases/Postgres/assets/chap_4_operations.png" publish: true description: "The Grand Operations of PostgreSQL: The scouts, matchmakers, and organizers that physically execute the Query Planner's lazy battle plans." --- ![[chap_4_operations.png]] # Chapter 4: The Grand Operations (The Soldiers) The [[QueryPlanner|Query Planner]] (the Lazy General) sits in his tent, stares at the statistics, and declares exactly how he wants your SQL query executed to waste the least amount of effort possible. But the General doesn't actually _do_ the work. He just draws a map. A Directed Acyclic Graph of `Operations`. These Operations are the ground troops. They are the mindless soldiers who march into the database to find your tuples, glue them together, sort them into piles, and bring them back to you. When you ask Postgres to `EXPLAIN` a query, it's just handing you the general's battle map, showing you exactly which soldiers were dispatched and in what order. The grand army of Postgres operations can be broadly grouped into four distinct units: ## 1. The Scouts (Finding the Data) Before you can sort or join data, you actually have to get up and go find it. The scouts are dispatched to explore the `Pages` on disk. - If the General is feeling unimaginative, he sends a [[SeqScan]] to just wildly flip through every page in the book. - If he's feeling precise, he sends an [[IndexScan]] or [[IndexOnlyScan]] to use a specific cheat sheet. - For weird, fragmented treasure maps, he hands out bitmap coordinates to a [[BitmapIndexScan]] and tells the [[BitmapHeapScan]] to go dig up those specific coordinates. - For temporary data, he sends a [[CTEScan]], [[SubqueryScan]], or a [[ValuesScan]]. ## 2. The Matchmakers (Joining the Data) SQL is meant for joining tables. The matchmakers take two massive stacks of tuples dumped by the scouts and attempt to glue them together. - The ultimate lazy matchmaker is the [[HashJoin]]. It magically memorizes one pile of tuples in `Memory`, and then just throws the second pile against it to see what sticks. - The most brutally precise is the [[MergeJoin]], which sorts both piles alphabetically and zippers them together perfectly. - The desperate, exhausted last resort is the [[NestedLoop]]. It's the equivalent of checking every single item in pile A against every single item in pile B, one by one. ## 3. The Organizers (Sorting and Grouping) Once the data is found and joined, the organizers arrange it into presentation-ready piles so you aren't handed a crumpled mess. - Want the largest numbers first? The [[Sort]] soldier physically shuffles the tuples into order, sweating buckets if they don't fit in his tiny desk [[Memory]]. - Did you use `GROUP BY`? The [[Aggregate]] or [[Hash]] operations toss tuples into buckets to squash them down into single lines of data. - Are you scrolling through a list and only want the top 10 rows? The [[Limit]] soldier acts as the bouncer, immediately kicking the 11th tuple out the door so you don't even have to look at it. ## 4. The Modifiers (Changing the World) If your SQL isn't a `SELECT`, then you're asking the database to actually change reality. - The [[ModifyTable]] operation takes the tuples you fed it and obediently inscribes them onto pages. And if you ask it to DELETE something, it rolls its eyes, puts a lock on the [[Tuple]], and pulls out its Sharpie ([[MVCC]]).