# Why Is My Query Slow? (Indexes & EXPLAIN) > A query that's instant on your laptop crawls in production because the database is reading every row to find matches; an index lets it jump straight to them, and EXPLAIN lets you see which one is happening. --- # Why Is My Query Slow? (Indexes & EXPLAIN) You wrote a query. On your laptop, against a few hundred rows of test data, it returned instantly. You shipped it. Then production - with ten million real rows - turned that same query into a thirty-second hang, a timeout, a page at 2am. Nothing about the *query* changed. The only thing that changed was the size of the table. This is the most common performance surprise in all of databases, and it has a single, learnable cause. Once you understand the one mental model behind it, most "my query is slow" problems stop being mysteries and become a short checklist: see exactly what the database is doing, understand *why* it's slow, and fix it with a precise change instead of guessing. ## How to read this - **In a hurry, query already slow?** Skim [Phase 1](01-the-full-table-scan.md) for the cause, then jump to [Phase 3: Reading EXPLAIN](03-reading-explain.md) to diagnose your actual query and add the right index. - **Want it to finally make sense?** Read in order. Each phase builds on the last: the problem (Phase 1), the fix (Phase 2), and how to see both with your own eyes (Phase 3). ## The phases 1. **[The Full-Table Scan](01-the-full-table-scan.md)** - why a query that's instant on 100 rows crawls on 10 million. Without help, the database reads *every row* to find your matches. This one idea explains most slowness. 2. **[Indexes](02-indexes.md)** - what an index actually is: a separate, sorted structure that lets the database jump straight to matching rows, like the index at the back of a book. How to create one, and the real cost of having too many. 3. **[Reading EXPLAIN](03-reading-explain.md)** - how to *see* what the database is doing. Reading `EXPLAIN` and `EXPLAIN ANALYZE`, telling a scan from an index lookup, comparing estimated vs. actual rows, and spotting the missing index. Then: measure, fix, re-check. > This guide is about the everyday 90% - the slow query you can fix by understanding scans and adding the right index. Deep profiling (composite-index ordering, partial and covering indexes, query-planner tuning, lock contention) is a future *performance* topic; we'll point there when it's the right next step. Related reading: [SQL Joins Explained](/guides/sql-joins-explained) and [Relationships & Keys](/guides/relationships-and-keys) - joins and keys are exactly the columns you'll most often want to index. --- # The Full-Table Scan Let's name the feeling first, because it's a specific kind of betrayal. You tested the query. It worked. It was *fast*. Then the same query, untouched, falls over in production. It's easy to suspect the database is broken, or that prod is "just slow," or that you did something subtly wrong. None of those is the real story. Here's the real story: the query was always doing the same expensive thing. On your laptop that expense was too small to notice. In production it isn't. Once you can see *what* expensive thing it's doing, the surprise disappears - and so does the fear. ## What the database actually does to find a row When you ask for specific rows - say, the user whose email is `ada@example.com` - the database has to *find* them. If you haven't given it any help, it has exactly one strategy: start at the first row of the table, look at it, check if it matches, move to the next, and repeat until it has checked **every single row**. This is called a **full-table scan** (PostgreSQL calls it a *sequential scan* or *seq scan*; MySQL calls it a *full table scan*). Same idea everywhere: read the whole table, top to bottom, because it has no faster way to know where your row lives. 📝 **Terminology.** A *full-table scan* (a.k.a. *sequential scan* / *seq scan*) means the database reads every row in the table to answer your query. It's not a bug - it's the database's fallback when it has no shortcut to your data. **Why people get this wrong.** The intuitive picture is that the database "knows where things are," like looking up a word in a dictionary. It doesn't - not by default. A plain table is an unordered pile of rows. Asking an unordered pile "which of you has email `ada@example.com`?" has no clever answer. You have to ask each row, one at a time. The database is doing the dumbest possible thing not because it's dumb, but because nothing has told it where to look. ## Why 100 rows lies to you **What it does in real life.** A full-table scan costs roughly one unit of work *per row*. So the cost grows in a straight line with the size of the table: ```text Finding one matching row, no index - work grows with the table: table size rows the DB must read feels like ---------- --------------------- ---------- 100 rows up to 100 instant 10,000 rows up to 10,000 still fine 1,000,000 rows up to 1,000,000 a noticeable pause 10,000,000 rows up to 10,000,000 timeout / 2am page ``` (The row counts are exact; the "feels like" column is a rough illustration, not a benchmark - actual timing depends on your hardware, row size, and caching.) On your laptop, scanning 100 rows is so fast it's indistinguishable from instant. That's the trap: **small data hides the scan.** The query and the production query are doing the identical thing - reading the whole table - but "the whole table" went from 100 rows to ten million. Nothing got slower. The table got bigger, and the cost was always proportional to the table. ```text The same query, two table sizes: LAPTOP (100 rows) [r][r][r] ... [r] ← scan all 100, done before you blink ▲──────── checked every row ───────▲ PRODUCTION (10,000,000 rows) [r][r][r][r][r][r][r][r] ... ... ... [r][r][r] ▲──────────── checked every one of ten million rows ────────────▲ (this is the hang) ``` ## Seeing it with your own eyes You don't have to take this on faith. Every major database can tell you its plan for a query - we'll cover that tool properly in [Phase 3](03-reading-explain.md), but here's a first taste so you can connect the idea to something concrete: ```sql EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com'; ``` ```console QUERY PLAN ----------------------------------------------------------------- Seq Scan on users (cost=0.00..189431.00 rows=1 width=124) Filter: (email = 'ada@example.com'::text) ``` *What just happened:* The database told you its plan in the first two words: **`Seq Scan`**. That means "I'm going to read the whole `users` table and filter as I go." The `cost=...189431.00` is the planner's own estimate of how much work that is (an abstract unit, not seconds, and the exact number here is illustrative) - and it's large *because the table is large*. The `rows=1` at the end is how many rows it expects to *return*. Read that line again: it expects to return **one** row, but its plan is to **read the entire table to find it**. That gap - read millions, return one - is the whole problem in a single line. ⚠️ **Gotcha.** A full-table scan isn't always wrong. If your query genuinely needs *most* of the rows (`SELECT * FROM users` with no filter, or `WHERE status = 'active'` when 90% of users are active), reading the whole table really is the fastest plan - jumping around to fetch nearly everything is slower than a clean sweep. The scan only becomes a problem when you're reading millions of rows to return a handful. "Read a lot, return a little" is the smell. **Why this saves you later.** Once "fast on my laptop, dying in prod" has a name - *the table grew and the scan grew with it* - you stop blaming the database, the network, or yourself. You start asking the one productive question: *how do I let the database find these rows without reading all of them?* That's exactly what an index is for, and it's next. ## Recap 1. **To find rows, a database with no help reads every row** - a full-table scan (a.k.a. sequential scan / seq scan). 2. **A plain table is an unordered pile.** There's no shortcut to a specific row unless you build one. 3. **Scan cost grows with table size**, roughly one unit of work per row - so it's invisible on 100 rows and brutal on 10 million. 4. **Small test data hides the scan.** "Fast on my laptop, slow in prod" is almost always the same query meeting a much bigger table. 5. **The smell is "read a lot, return a little."** A scan that reads millions to return a handful is the thing to fix. A scan that returns most of the table is often fine. The fix is to give the database a shortcut - a separate, sorted structure it can jump around in instead of reading everything. That's an index, and it's the heart of the next phase. --- # Indexes In the last phase, you watched the database read ten million rows to return one. The obvious wish is: *I just want it to know where that row is.* That wish has a name, and it's been the answer for fifty years. It's an **index** - and the analogy that makes it click is sitting on your bookshelf. Think about how you find one topic in a 900-page textbook. You don't read all 900 pages. You flip to the **index** at the back, find the topic alphabetically, and it tells you "page 412." Then you go straight to page 412. The book's pages are in *book order*; the index is a *second, sorted copy of just the keywords* with pointers to where they live. A database index is exactly this, for your table. ## What an index actually is An index is a **separate, sorted structure** stored alongside your table. It contains the values from one column (say, `email`), kept in sorted order, and next to each value a pointer to the full row it came from. The table itself stays an unordered pile; the index is the sorted "back of the book" that tells the database where each value lives. Because the index is *sorted*, the database doesn't read it top to bottom either. It uses the same trick you use with a physical index: it can jump to roughly the middle, see whether your value is before or after, throw away half, and repeat. Each step halves what's left. 📝 **Terminology.** Most database indexes are **B-trees** (balanced trees). You don't need the internals - the working mental model is "a sorted structure the database can jump into and narrow down by halving, instead of reading from the start." That halving is why a B-tree lookup stays fast even as the table grows huge: doubling the rows adds only one more step, not double the work. ```mermaid flowchart TD subgraph SCAN[FULL-TABLE SCAN no index] Q1[query: email = ada] --> R[read every row in order, check each] R --> F1[found, after reading the whole table] end subgraph IDX[INDEX LOOKUP B-tree] Q2[query: email = ada] --> J[jump into sorted index, narrow by halving] J --> P["index entry points to row #738114"] P --> F2[jump straight to that one row] end ``` **Why people get this wrong.** A common picture is that an index "sorts the table" or "makes the table faster." It doesn't touch the table's order at all. It's an *additional* object - extra data on disk - that the database consults *first* to find out which rows it needs, and only then goes to the table for those specific rows. Two structures: the pile (table) and the sorted lookup (index). ## Creating one You create an index on the column(s) you search by. The syntax is nearly identical across PostgreSQL, MySQL, and SQLite: ```sql CREATE INDEX idx_users_email ON users (email); ``` ```console CREATE INDEX ``` *What just happened:* The database read through the `users` table once, pulled out every `email` value with a pointer back to its row, sorted them, and saved that as a new structure named `idx_users_email`. From now on, a query filtering on `email` can consult this sorted index to find matching rows in a few hops instead of scanning the table. The naming (`idx__`) is a convention, not a requirement - but a consistent convention saves you later when you're trying to remember what indexes exist. The database's planner now has a *choice* for `WHERE email = ...`: scan the whole table, or use the index. For a query that returns a few rows out of millions, it will pick the index - and the same query that timed out becomes instant. (In Phase 3 you'll confirm the switch with `EXPLAIN`.) ## The cost - why you don't index everything If indexes make reads fast, why not put one on every column? Because indexes are not free, and the bill comes due on the other side of the ledger. ⚠️ **Gotcha - indexes slow down writes.** An index is a *second copy* of that column's data, kept sorted. Every time you `INSERT`, `UPDATE`, or `DELETE` a row, the database must update the table *and* update every index on that table to keep them in sync. One index, modest cost. Ten indexes on a hot table, and every single write now does eleven pieces of work instead of one. On a write-heavy table, over-indexing can make the whole thing *slower*, not faster. ⚠️ **Gotcha - indexes use disk and memory.** Each index is real, stored data - sometimes a significant fraction of the table's own size. More indexes means more disk used, and more of your database's memory cache spent holding index pages instead of actual rows. So indexing is a trade: **faster reads in exchange for slower writes and more space.** That trade is almost always worth it for the columns you actually search by, and almost always a waste for the ones you don't. 💡 **The rule of thumb:** index the columns you **filter** on (`WHERE`), **join** on (`ON`), and **sort** on (`ORDER BY`) - those are the lookups that benefit. Don't index columns you only ever read back as output, columns you rarely filter by, or low-value columns "just in case." Index for the queries you actually run. This is also where it connects to the rest of your schema. The columns you join on are typically your foreign keys (see [Relationships & Keys](/guides/relationships-and-keys)), and the columns in your join conditions (see [SQL Joins Explained](/guides/sql-joins-explained)) are prime index candidates - an unindexed join column is one of the most common causes of a slow join. A primary key, worth noting, is already indexed for you automatically; you don't need to add one for it. **Why this saves you later.** Knowing the trade means you can answer the next "slow query" calmly. You won't carpet-bomb the table with indexes (and quietly wreck write performance), and you won't refuse to add the one index that would fix everything. You'll add *the right index for the query that's actually slow* - which is exactly what Phase 3 teaches you to identify. ## Recap 1. **An index is a separate, sorted structure** with pointers back to rows - the "back of the book," not a reordering of the table. 2. **It's usually a B-tree**, so the database finds a value by jumping and halving, staying fast even as the table grows. 3. **`CREATE INDEX idx_users_email ON users (email);`** builds one; the planner then *chooses* whether to use it. 4. **Indexes cost you on writes and disk** - every write must update every index, and each index takes real space. 5. **Index what you filter, join, and sort on** - not every column, not "just in case." Index for the queries you run. You now know the disease (the scan) and the cure (the right index). The last skill is the one that ties them together: *seeing* which one your database is using, so you stop guessing. That's `EXPLAIN`, and it's next. Watch it animated: [database indexes](/explainers/Indexes.dc.html) --- # Reading EXPLAIN Up to now you've been reasoning about what the database *might* be doing. This phase hands you the flashlight. `EXPLAIN` is how you stop guessing and *see* the database's actual plan for your query - whether it's about to scan ten million rows or jump straight to the three you want. It's the single most useful database-performance skill there is, and it's far less intimidating than its output first looks. ## The two commands There are two tools, and the difference matters: - **`EXPLAIN`** asks the database, *"What's your plan for this query?"* It does **not** run the query. It returns the plan and the planner's *estimates* (how much work it thinks it'll be, how many rows it expects). Safe and instant - even for a query that would take a minute to run. - **`EXPLAIN ANALYZE`** *actually runs the query* and reports what really happened - real timings and real row counts, side by side with the estimates. Use this when you want the truth, not a forecast. ⚠️ **Gotcha.** `EXPLAIN ANALYZE` **executes the query**. For a `SELECT` that's harmless. But `EXPLAIN ANALYZE` on an `UPDATE`, `DELETE`, or `INSERT` will *really modify your data*. If you must analyze a write, wrap it in a transaction and roll it back (`BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;`), or test against a copy. ## Reading a plan: the scan you're trying to escape Here's the slow query from Phase 1, before any index, using `EXPLAIN ANALYZE` so we see real numbers (PostgreSQL syntax; MySQL and SQLite have their own `EXPLAIN` output but the same concepts apply): ```sql EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ada@example.com'; ``` ```console QUERY PLAN ------------------------------------------------------------------------------------------------- Seq Scan on users (cost=0.00..189431.00 rows=1 width=124) (actual time=210.044..844.120 rows=1 loops=1) Filter: (email = 'ada@example.com'::text) Rows Removed by Filter: 9999999 Planning Time: 0.071 ms Execution Time: 844.182 ms ``` *What just happened:* (the numbers here are illustrative, but this is exactly the shape you'll see) Read it top-down, line by line: - **`Seq Scan on users`** - the headline. The database read the entire table. This is the thing Phase 1 warned you about, confirmed in writing. - **`cost=0.00..189431.00 rows=1`** - the *estimate*: a large abstract cost, and an expectation of returning 1 row. - **`actual time=... rows=1`** - what *really* happened: it returned 1 row, as expected. - **`Rows Removed by Filter: 9999999`** - the smoking gun. To return that one row, the database looked at ten million rows and *threw away* 9,999,999 of them. Read a lot, return a little - the exact smell from Phase 1. - **`Execution Time: 844.182 ms`** - the bottom line. Nearly a second to find one row. That `Rows Removed by Filter` line, sitting in the millions next to a `rows=1` result, is your missing-index alarm going off. ## The same query, after the index Now add the index from Phase 2 and ask again: ```sql CREATE INDEX idx_users_email ON users (email); EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ada@example.com'; ``` ```console QUERY PLAN ------------------------------------------------------------------------------------------------------------- Index Scan using idx_users_email on users (cost=0.43..8.45 rows=1 width=124) (actual time=0.041..0.043 rows=1 loops=1) Index Cond: (email = 'ada@example.com'::text) Planning Time: 0.098 ms Execution Time: 0.069 ms ``` *What just happened:* (illustrative numbers, real shape) The headline changed from `Seq Scan` to **`Index Scan using idx_users_email`** - the database is now using your index to jump straight to the row. Three tells confirm the win: - **`Index Cond`** (not `Filter`) - it used the index *condition* to locate rows, rather than reading everything and filtering. There's no `Rows Removed by Filter` line at all, because it never touched the rows it didn't need. - **`cost=0.43..8.45`** - the estimated cost dropped from ~189,000 to single digits. - **`Execution Time: 0.069 ms`** vs. `844 ms` before - same query, same data, same result. The only change was giving the database a shortcut. 📝 **Terminology - the scan types you'll see.** `Seq Scan` = read the whole table (Phase 1's villain). `Index Scan` = use an index to find rows, then fetch them from the table. `Index Only Scan` = the answer was entirely inside the index, so the table wasn't touched at all (the fastest case). In MySQL's `EXPLAIN`, the rough equivalents show up in the `type` column: `ALL` is a full scan (bad for big tables), while `ref`, `range`, and `eq_ref` mean an index is being used. ## Estimated vs. actual: spotting a plan gone wrong The most powerful habit `EXPLAIN ANALYZE` gives you is comparing the planner's **estimate** to the **actual** result. The planner chooses its plan based on statistics about your data. When those statistics are stale or skewed, the estimate can be badly wrong - and a wrong estimate leads to a wrong plan. ```text estimate rows=1 actual rows=1 → matched. planner trusts itself, good plan. estimate rows=1 actual rows=2,400,000 → way off. planner thought "few rows, use the index" but there were millions - now it's doing millions of slow index lookups. ouch. ``` When estimated and actual row counts are wildly apart, that mismatch - not the scan type alone - is often the real cause of a slow query. The usual fix is to refresh the planner's statistics (`ANALYZE users;` in PostgreSQL, `ANALYZE TABLE users;` in MySQL). If a plan stays bad after that, you're at the edge of this guide - planner internals and forcing plans are a deeper *performance* topic. ## The loop: measure, fix, re-check This is the whole discipline, and it's a loop you can run on any slow query without guessing: ```mermaid flowchart TD M["1. MEASURE: EXPLAIN ANALYZE the slow query
find the Seq Scan + millions of Rows Removed by Filter
note the WHERE / JOIN / ORDER BY columns"] F["2. FIX: CREATE INDEX on the column(s)
you filter / join / sort on"] R["3. RE-CHECK: EXPLAIN ANALYZE again
confirm Index Scan, removed-rows gone,
Execution Time dropped"] N["Not fixed? index may not apply (function on column,
leading-wildcard LIKE '%foo'), stale estimates → ANALYZE,
or the scan was correct all along"] M --> F --> R R -->|still slow| N N --> M ``` ⚠️ **Gotcha - always re-check, never assume.** Creating an index does *not* guarantee the database will use it. If your `WHERE` wraps the column in a function (`WHERE lower(email) = ...` won't use a plain index on `email`), or uses a leading wildcard (`LIKE '%example.com'`), or the planner decides a scan is genuinely cheaper, your shiny new index sits unused. The only way to know it worked is step 3: run `EXPLAIN ANALYZE` again and *see* `Index Scan` with a lower execution time. Measure the fix; don't trust it. 🪖 **War story.** The classic version: someone "fixes" a slow query by adding three indexes they *think* are relevant, ships it, and the query is still slow - but now writes are slower too. The fix was always one index, and `EXPLAIN ANALYZE` would have named it in ten seconds: one `Seq Scan`, one `Rows Removed by Filter: 4000000`, one column to index. Measure first. The plan tells you exactly which index to add - you don't have to guess. The next time a query is slow, you have a procedure instead of a panic. `EXPLAIN ANALYZE` it, look for the scan and the removed-rows count, add the one index the plan points at, and run it again to prove the fix. That loop turns "the database is mysteriously slow" - the worst kind of 2am problem - into a five-minute, evidence-backed fix. ## Recap 1. **`EXPLAIN` shows the plan; `EXPLAIN ANALYZE` runs the query** and shows real timings and row counts. (Careful with `ANALYZE` on writes - it executes them.) 2. **`Seq Scan` + a big `Rows Removed by Filter`** = the database is reading everything to return a little. That's your missing-index alarm. 3. **`Index Scan` with an `Index Cond`** = it's using your index to jump to rows. That's the win you're looking for. 4. **Compare estimated vs. actual rows.** A large mismatch points at stale statistics - run `ANALYZE` - and is often the real culprit behind a bad plan. 5. **Run the loop: measure → add the right index → re-check.** Always confirm with `EXPLAIN ANALYZE` that the index is actually used and the time actually dropped. Don't assume. That's the everyday skill: see the scan, add the index the plan points at, prove it worked. When this loop *doesn't* solve it - composite-index ordering, lock contention, planner tuning - you've reached the deeper end, and that's where the future *performance* guides will pick up. ## Try it yourself Ask SQLite how it plans to run a query (real EXPLAIN output): ```sql runnable EXPLAIN QUERY PLAN SELECT * FROM books WHERE author_id = 3; ```