![[chap_2_indexes.png]] # Chapter 2: The Mighty Indexes (Building Cheat Sheets) Reading a whole book just to find one single name is the absolute opposite of lazy. Imagine you ask Postgres to find the name "Sarah". If you provide no other help besides pointing to a massive `users` [[Table]] containing ten million rows, the elephant will let out a heavy sigh, start at page one, and read every... single... line. This nightmare scenario is called a `Sequential Scan`, and the elephant absolutely despises it. To avoid this mind-numbing manual labor, the elephant allows you to construct **Indexes**. An index is essentially a tiny, highly specialized cheat sheet. Instead of rewriting the entire book, the index only stores the *one specific thing* you care about, alongside a direct map right to the page where the rest of the actual data is buried. Because there are many different ways to be lazy, Postgres has developed many different flavors of cheat sheets. The most common is the [[BTree|B-Tree]]. It acts exactly like the alphabetized index at the back of a textbook, perfectly designed for answering questions like "greater than" or "equal to". But what if you need an exact, rapid match? For that, the elephant will use a [[Hash]] index—a magical dictionary where knowing the exact word drops you instantly onto the precise page without scanning the alphabet. However, things get strange when you ask the elephant to find something that isn't just a word or a number. How do you build a cheat sheet for the abstract concept of "nearby"? The [[GiST]] index handles this by acting as a sieve for people drawing overlapping circles and boxes (like geographic mapping or full-text search). For data that doesn't overlap perfectly, like phone trees or IP addresses, the elephant reaches for [[SPGiST|SP-GiST]]. And what if a single record contains many distinct ingredients, like a recipe or a JSON document? The [[GIN]] index acts exactly like a cookbook's ingredient list, mapping a single grain of "Salt" back to every recipe that requires it. Even among cheat sheets, there is a hierarchy of laziness. The absolute laziest of them all is [[BRIN]]. It refuses to look at actual records at all. Instead, it simply glances at the outside of a massive filing cabinet and writes a note saying, "Prices between $10 and $50 are probably somewhere in this block." It is incredibly tiny, delightfully vague, and requires almost zero effort to build. Each index is a trade-off. They require some effort to write today, but the elephant is more than happy to scribble a quick sticky note now if it guarantees avoiding a 10-mile hike tomorrow.