Indexes & Query Plans

Most slow queries are slow for a boring reason: the database is reading far more data than it needs to. An index is a separate, ordered structure that maps column values to row locations, so the engine can jump to the rows you asked for instead of reading the whole table. On a million-row table, the difference is roughly 8,000 page reads versus about 3. But an index is not free. It costs disk space, slows every write, competes for memory, and the planner is free to ignore it whenever it judges a different route cheaper.

This chapter explains how that machinery works, so you can predict what the database will do instead of guessing. You will see how a B-tree keeps lookups shallow, why the order of columns in a composite index decides which queries it can serve, and how a covering index can answer a query without touching the table at all. Then you will learn to read EXPLAIN output: what the planner estimated, what actually happened, and where those two numbers disagree, which is usually where the problem lives.

By the end you will be able to choose an index shape from a query's filters and sort order, tell when a sequential scan is the correct answer, spot the changes that silently disable an index (a function around a column, an implicit cast, a leading wildcard), and find indexes that cost more than they give. You will also have a repeatable method: find the query that matters, read its plan, locate the node where the estimate breaks, and fix the cheapest thing first.

A relational table is stored as a heap: a pile of fixed-size pages holding rows in whatever order they arrived. Nothing in that layout is sorted by value. If you ask for WHERE email = 'ada@example.com', the heap has no way to tell you which page holds that row, because insertion order says nothing about email addresses.

An index is the fix. It is a separate, ordered, redundant data structure that sits beside the table and maps column values to row locations. It is separate because it lives in its own pages on disk, ordered because its keys are kept sorted so you can search them quickly, and redundant because every fact in it already exists in the table. The database maintains it automatically on every write. You never write to an index yourself.

Without an index, the only access path is a full table scan. The engine reads every page of the heap and throws away the rows that do not match. The work is O(N) in the table size, no matter how few rows match. Finding one row among a million costs the same as finding none.

The first example mimics this in plain Python. The heap is a list of 100,000 email addresses in a scrambled order, and the scan must look at every entry because it cannot know the value is unique.

heap = [f"user{(i * 7919) % 100000:05d}@example.com" for i in range(100000)]
target = "user04242@example.com"

examined = 0
matches = 0
for email in heap:
    examined += 1
    if email == target:
        matches += 1

print("first heap rows:", heap[:3])
print("matches:", matches)
print("rows examined:", examined)

A full scan: work grows with the table, not with the answer

One matching row, 100,000 rows examined. The first three heap rows show the problem: the heap is not in email order, so no early shortcut exists.

Suppose a users table has 1 million rows and its heap occupies about 8,334 pages. A full scan reads all 8,334 of them to answer a one-row lookup. One statement changes that:

-- 1M users, heap = 8334 pages
CREATE INDEX idx_users_email ON users (email);

-- the same lookup now descends the index:
SELECT * FROM users WHERE email = 'ada@example.com';

The query text does not change. Only the access path does.

The index is a balanced tree whose nodes are pages, and each node holds hundreds of sorted keys. One million entries fit in roughly three levels, so the lookup reads about 3 index pages (plus one heap visit for the row itself) instead of 8,334. The next section opens up the tree. For now, the key idea is that sorted keys let you halve the search space at every step, or better.

The next example sorts the same emails into an index of (value, heap position) pairs. It then finds the target with a binary search. This is a simplification of what a B-tree does, but it shows the shape of the saving.

import bisect
import math

heap = [f"user{(i * 7919) % 100000:05d}@example.com" for i in range(100000)]
target = "user04242@example.com"

index = sorted((email, pos) for pos, email in enumerate(heap))
keys = [entry[0] for entry in index]

slot = bisect.bisect_left(keys, target)
print("first sorted keys:", keys[:3])
print("found in index:", keys[slot] == target)
print("heap row it points to:", heap[index[slot][1]])
print("worst-case probes:", math.ceil(math.log2(len(keys) + 1)))

Ordered entries turn a 100,000-row scan into a handful of probes

An index is not free. You buy read speed with write cost and disk space. Because the index is a redundant copy of column values in sorted order, every INSERT, UPDATE of an indexed column, and DELETE has to change the table and also every index that covers the affected columns. A table with five indexes does six structural updates per inserted row. An index on a column that changes constantly taxes every write, forever.

Whether an index pays for itself depends on two properties of the data. Selectivity is the fraction of rows a predicate matches. A predicate that matches 1 row out of 1 million is highly selective and index-friendly. One that matches 40% of the table has low selectivity, and the index usually loses, because following the index into the heap for that many rows costs more than reading the heap straight through.

Cardinality is the number of distinct values in a column. A user_id on a 1M-row table has very high cardinality, nearly one value per row. An is_deleted flag has a cardinality of 2. Low-cardinality columns tend to give low selectivity for the common value, so they make poor index candidates on their own.

total = 100000
rows = [{"user_id": i, "is_deleted": i % 10 < 4} for i in range(total)]

for column in ("user_id", "is_deleted"):
    print(column, "cardinality:", len({row[column] for row in rows}))

one_user = sum(1 for row in rows if row["user_id"] == 4242)
deleted = sum(1 for row in rows if row["is_deleted"])
print(f"user_id = 4242      matches {one_user / total:.3%} of rows")
print(f"is_deleted = True   matches {deleted / total:.3%} of rows")

The same table, two very different predicates

Which columns deserve an index? Look at the shape of your queries, not the shape of the schema. An index helps when the engine must find, join, order or group rows by a column. It does nothing for a column that only appears in the SELECT list, because the engine reads that value after it has already found the row.

Even a well-chosen index comes with no guarantee. The database never promises to use an index. A query planner decides at run time which access path looks cheapest, and it bases that decision on statistics about the table: row counts, distinct values and value distributions. The same query can use an index today and a full scan next month, because the statistics, and therefore the planner's estimates, changed as the data grew.

When a database says it has a B-tree index, it almost always means a B+tree. The tree has two kinds of node, and they do different jobs. Internal nodes hold only separator keys and pointers to child nodes. They are signposts, not data. Every real key lives in a leaf node at the bottom, next to the pointer that locates its row.

A lookup starts at the root. At each internal node the database compares your search value with the separators, picks the one child whose range contains it, and descends. When it reaches a leaf, it either finds the key or knows the key does not exist.

The leaves are also linked to each other in a doubly-linked list, in key order. This is what makes range scans and ORDER BY cheap. The database descends once to find the first matching key, then walks sideways along the leaf chain. It never climbs back up to the root and descends again.

SELECT id, created_at
FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY created_at;

One descent, then a sideways walk. The rows come out already sorted, so no Sort node is needed.

Each node is sized to exactly one disk page. Reading a node is therefore one I/O, and the depth of the tree is the number of I/Os a lookup costs. That is why the design is built to keep the tree shallow.

A page of several KB holds a lot of small entries. With a compact key and an 8-byte child pointer, one internal node can point to hundreds of children. That number is the fan-out, written f. Every level multiplies the reachable entries by f, so the tree stays very shallow.

The code below finds the smallest number of levels whose capacity (f raised to the number of levels) covers the row count. It is a simplified model, but it matches how real indexes behave.

This gives the lookup cost O(log_f N) page reads, where f is the fan-out. A logarithm with a base in the hundreds grows so slowly that, in practice, the cost is almost constant. A thousand-fold increase in table size adds about one level.

A B+tree is always balanced: every leaf sits at the same depth. It stays that way because it grows from the top, not the bottom. When an insert lands in a full node, the node splits into two halves, and a separator key is pushed up into the parent. If the parent is also full, it splits too, and the split can reach the root. Only a root split makes the tree one level taller.

def split_leaf(keys, new_key, capacity=4):
    keys = sorted(keys + [new_key])
    if len(keys) <= capacity:
        return keys, None, None
    mid = len(keys) // 2
    return keys[:mid], keys[mid:], keys[mid]

left, right, sep = split_leaf([10, 20, 30, 40], 25)
print("left :", left)
print("right:", right)
print("push up:", sep)

A leaf with room for four keys receives a fifth.

Notice that 25 is still present in the right leaf. In a B+tree the separator is a copy used for routing, because every real key must remain in a leaf. In the other direction, deletes may leave a node nearly empty, and the database can then merge it with a sibling and remove a separator from the parent. Many engines are lazy about this, which matters for the bloat discussed on the last page.

A leaf entry is a key plus a way to find the row. What that second part is depends on the engine, and the difference changes how lookups perform.

In Postgres the table is a heap, an unordered pile of pages. Every index is a separate structure whose leaves hold TIDs that point into the heap. Finding a row costs the index descent plus one heap page read.

A clustered index is not stored beside the table. It is the table. The leaf nodes contain the full rows, sorted in key order. InnoDB and SQL Server work this way. Because the rows can only be physically sorted one way, a table has only one clustered index. In InnoDB that is the primary key.

The payoff is that a primary-key lookup ends at the leaf with the row already in hand, and a primary-key range scan reads neighbouring rows from neighbouring pages.

A secondary index in a clustered engine cannot store a physical address, because rows move when pages split. Instead its leaves store the primary key value. Every lookup through a secondary index therefore costs two tree walks.

Because the tree is kept in sorted order, any question that can be answered by finding a position and walking along the sorted keys is cheap. Every operator in the table below fits that pattern, and the last two rows do not.

The prefix case works because all strings starting with ad sit next to each other in sorted order. Finding where they begin and where they end is two position searches. The sketch below uses a sorted list to show the same idea.

An index on email is sorted by the stored text. A query on lower(email) asks about a different value, and the order of the lowercased values is not the order of the stored ones. The planner cannot descend the tree for it, so it falls back to scanning every row. The same applies to date(created_at), price * 1.2 and any other function or expression wrapped around the indexed column.

-- Cannot seek on an index over (email): the function hides the column
SELECT id FROM users WHERE lower(email) = 'ada@example.com';

-- Fix 1: index the expression itself (it must match the query exactly)
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Fix 2: leave the column bare and move the work to the constant side
SELECT id FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

Either index the exact expression, or rewrite the predicate so the column stands alone.

Indexes do not stay as tidy as a fresh build. Deleting or updating rows leaves dead entries behind, and pages that were split earlier are rarely merged back. The result is leaves that are mostly empty space. That is index bloat: more pages to read for the same data, less fan-out in effect, and more cache wasted on empty bytes.

VACUUM orders;
REINDEX INDEX CONCURRENTLY idx_orders_created;

Routine cleanup first, a rebuild only when an index is clearly much larger than its live data.

The B-tree from the previous section is the default for good reason: it keeps keys ordered, so it answers equality, ranges, ORDER BY and prefix matches. But ordering is an assumption, and some questions are not about order at all. "Does this array contain 'sql'?", "which stores are within 2 km?" and "which rows were written last Tuesday?" each want a different structure. Postgres ships several, and other engines have their own versions.

A hash index runs the key through a hash function and uses the result to jump straight to a bucket. An equality lookup is O(1), with no tree to descend. The price is that a hash scatters neighbouring keys across unrelated buckets, so there is no range support and no ordering. WHERE id < 100, ORDER BY and MIN/MAX can't use it. A B-tree on a high-fan-out tree is already only 3-4 page reads for an equality lookup, so the hash index rarely wins by enough to justify giving up everything else it can't do.

A GIN (Generalized Inverted Index) works like the index at the back of a book. For a column that holds many values per row, such as an array, a JSONB document or a tsvector of words, it stores each element once, together with the list of rows that contain it. A row with ten tags therefore appears in ten index entries, instead of being one entry as in a B-tree. This is what makes containment queries fast.

CREATE INDEX idx_posts_tags ON posts USING gin (tags);
CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);
CREATE INDEX idx_docs_fts ON docs USING gin (to_tsvector('english', body));

SELECT id FROM posts WHERE tags @> ARRAY['sql'];
SELECT id FROM events WHERE payload @> '{"type": "signup"}';

Each operator (@>, @@) is one GIN can answer; a B-tree on these columns could not.

GiST (Generalized Search Tree) is a balanced tree framework where the data type decides what the internal nodes mean. For geometry, each node stores a bounding box that covers everything beneath it. This supports overlap and containment tests, nearest-neighbour ordering (ORDER BY location <-> point), and range types such as "does this booking overlap that one?". There is no single total order for 2-D points, so none of this fits a B-tree.

A BRIN (Block Range Index) doesn't point at rows at all. For each run of table blocks (128 pages by default) it keeps just the min and max of the column. A query skips every block range whose min/max can't contain the value, and scans the rest. The index is so small that it is often thousands of times smaller than a B-tree on the same column.

The catch is that BRIN works only when the physical row order correlates with the column. An append-only created_at on a log table is the classic fit: each block range holds a narrow, distinct slice of time. If rows are shuffled, every range's min/max spans nearly the whole domain, nothing can be skipped, and the index is useless.

A bitmap index (Oracle, and many analytics and column-store engines) keeps, for each distinct value, a bit vector with one bit per row. For a low-cardinality column such as region or status, combining predicates is just an AND or OR of bit vectors, which is extremely fast on a read-mostly warehouse where queries are WHERE region = 'EU' AND tier = 'gold' over millions of rows.

The weakness is lock granularity. A single bitmap entry covers a large range of rows, so changing one row locks that whole entry, and concurrent writers touching different rows end up waiting on each other. That is why bitmap indexes belong in OLAP systems with batch loads and are terrible under concurrent writes in OLTP. Don't confuse them with Postgres's *Bitmap Index Scan* plan node, which is a temporary in-memory structure built during one query and has no such locking problem.

Often a query touches a small, well-defined slice of a table: the pending jobs among millions of finished ones. A partial index adds a WHERE clause to the definition, so only matching rows are indexed. The index is dramatically smaller, cheaper to maintain (rows outside the slice cost no index writes) and more likely to stay in cache.

CREATE INDEX idx_jobs_pending
ON jobs (created_at)
WHERE status = 'pending';

-- the query must imply the index predicate to use it
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at LIMIT 20;

If only 0.4% of jobs are pending, this index is about 250 times smaller than a full one.

A plain index on email stores the raw email values, in raw order. WHERE lower(email) = 'ada@example.com' compares a different value, so the planner cannot use it. An expression (functional) index stores the result of the expression instead. The query's expression must match the index's expression exactly.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)")
db.execute("CREATE INDEX idx_email ON users (email)")

def seeks(sql):
    plan = db.execute("EXPLAIN QUERY PLAN " + sql).fetchall()
    return any(row[3].startswith("SEARCH") for row in plan)

q = "SELECT id FROM users WHERE lower(email) = 'ada@example.com'"
print("plain index seeks:", seeks(q))
db.execute("CREATE INDEX idx_email_lower ON users (lower(email))")
print("expression index seeks:", seeks(q))

SQLite supports expression indexes too, so the idea can be tried with only the standard library.

A unique index is a B-tree that refuses to hold two equal keys. That makes it do two jobs with one structure: it enforces the constraint (the engine checks the index on every write) and it serves fast lookups on the same column. UNIQUE constraints and primary keys are implemented this way, so you should not add a separate plain index on the same column; it would just duplicate the work.

There is a subtlety about missing values. In most engines a NULL means "unknown", and two unknowns are not known to be equal, so NULLs are treated as distinct by a unique index. You can insert any number of rows with a NULL in a UNIQUE column. (SQL Server is a notable exception, and Postgres 15+ offers NULLS NOT DISTINCT if you want the opposite.)

Unique, partial and expression can be combined. CREATE UNIQUE INDEX ON users (lower(email)) makes e-mail uniqueness case-insensitive, and CREATE UNIQUE INDEX ON subscriptions (user_id) WHERE active allows one active subscription per user while keeping any number of cancelled ones.

The shape of the question picks the structure. Start from the operator in your WHERE clause, not from the data type of the column.

An unusual index type is not a faster one. A GIN index is built for columns with many elements per row. Put one on a plain integer column (which needs the btree_gin extension just to be allowed) and you pay GIN's costs without any of its benefits: each row becomes a single-element posting list, lookups go through an extra layer, and writes are slower because GIN batches updates in a pending list. The result is slower and larger than the B-tree it replaced, and it still can't do ranges or ordering as well.

A composite index (also called a multi-column index) is a single B-tree whose keys are tuples of several columns. It sorts entries by the first column. Rows with equal first values are ordered by the second column, and any remaining ties are broken by the third. Nothing else about the B-tree changes: the same pages, the same descent, the same linked leaves. Only the sort key is wider.

The best mental model is a phone book sorted by (last_name, first_name). All the Smiths sit together, and inside that block the Adas come before the Beas. That layout makes two lookups cheap: all the Smiths, and Smith/Ada in particular. It makes a third lookup, every Ada in the city, no cheaper than reading the whole book. Ada entries are scattered across every surname block.

from bisect import bisect_left, bisect_right

TOP = chr(0x10FFFF)
book = sorted([('Smith', 'Bea'), ('Lee', 'Ada'), ('Smith', 'Ada'),
               ('Taylor', 'Ada'), ('Jones', 'Ada'), ('Smith', 'Cy'),
               ('Lee', 'Bea')])

lo, hi = bisect_left(book, ('Smith', '')), bisect_right(book, ('Smith', TOP))
print('Smith:', book[lo:hi])

lo, hi = bisect_left(book, ('Smith', 'Ada')), bisect_right(book, ('Smith', 'Ada'))
print('Smith/Ada:', book[lo:hi])

adas = [row for row in book if row[1] == 'Ada']
print(f'All Adas: {len(adas)} found, {len(book)} entries examined')

A sorted list plus binary search behaves like a two-column index.

The two Smith lookups are binary searches that jump straight to a contiguous slice. The Ada lookup has no such slice to jump to, so it has to examine every entry. This asymmetry is the whole story behind column order.

Because the sort is lexicographic, an index can only be searched efficiently by a leftmost prefix of its columns: the first column, the first two, or all of them. An index on (a, b, c) therefore serves queries that constrain a, or a and b, or a, b and c. A query that mentions only b or only c gets no seek, because matching entries are scattered throughout the tree. At best the engine scans the whole index, which is still far cheaper than the table but is no seek.

Column order is not cosmetic, because the tree can only seek through a contiguous run of entries. The rule of thumb is: put columns compared with = first, then the column compared with a range (>, <, BETWEEN). Take WHERE status = 'paid' AND created_at > ?. With the index on (status, created_at), all paid entries sit together and are sorted by date inside that block, so the engine seeks to the cutoff date and reads to the end of the block. With (created_at, status), the entries after the cutoff are ordered by date first, and paid rows are mixed in among every other status.

from bisect import bisect_right

TOP = chr(0x10FFFF)
rows = [(s, d) for s in ('new', 'paid', 'void') for d in range(1, 1001)]
by_status = sorted(rows)                    # index (status, created_at)
by_date = sorted((d, s) for s, d in rows)   # index (created_at, status)

# WHERE status = 'paid' AND created_at > 990
lo = bisect_right(by_status, ('paid', 990))
hi = bisect_right(by_status, ('paid', 10**9))
print(f'(status, created_at): touched {hi - lo}, matched {hi - lo}')

lo = bisect_right(by_date, (990, TOP))
tail = by_date[lo:]
matched = sum(1 for d, s in tail if s == 'paid')
print(f'(created_at, status): touched {len(tail)}, matched {matched}')

Count how many index entries each ordering has to look at.

Both orderings return the same 10 rows, but the wrong one reads three times as many entries and throws most of them away. With three statuses the gap is 3x. With fifty statuses it would be 50x. The cost of the wrong order grows with how many other values sit alongside the one you want.

The reason is mechanical. Equality on the first column narrows the search to one contiguous block, and inside that block the second column is sorted, so equality or a range on it narrows further. A range on a column leaves a span of different values for that column, and within that span the *next* column is no longer globally sorted. It is sorted only within each single value of the range column. So once the index reaches a range predicate, the columns after it can no longer be used for seeking. They can still be used to filter entries without visiting the table, or to cover the query's select list, but they cannot shrink the scanned region.

The same logic explains the classic feed query. With an index on (tenant_id, created_at), the tenant's entries form one contiguous block already sorted by date. The engine seeks to the tenant, starts at the newest entry, walks backward along the linked leaves, and stops after 20 rows. There is no sort step at all, and the work does not depend on how large the tenant is.

CREATE INDEX idx_events_tenant_created
ON events (tenant_id, created_at);

SELECT id, created_at
FROM events
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;

A bounded index scan: seek once, read 20 entries backward, no Sort node in the plan.

Since an index on (a, b) is already sorted by a first, every query that a standalone index on (a) could serve works just as well on the composite. The single-column index is redundant. It adds nothing to reads and still costs a write on every insert and update, plus space in the buffer pool, so drop it.

The reverse does not hold. (a, b) does not replace an index on (b), because b is not a leftmost prefix. If you filter by a in some queries and by b in others, you need two indexes, (a, b) and (b), and each one must be justified by a real query.

An index can also hand rows back already ordered, which removes a Sort node. It can only do this for the order it was built in, or for that order read backward. An index on (a ASC, b DESC) supports ORDER BY a ASC, b DESC and its exact reverse, ORDER BY a DESC, b ASC. It does not support ORDER BY a ASC, b ASC, because walking forward gives b descending inside each a, and walking backward flips a as well.

A gotcha worth knowing: an IN (...) list on the leading column is treated like several equalities. The engine performs one seek per listed value, and inside each landing spot the next column is still sorted, so it can be seeked too. A query such as WHERE tenant_id IN (7, 9) AND created_at > ? on (tenant_id, created_at) still gets a bounded range read per tenant. A true range such as tenant_id > 7 would not allow this.

from bisect import bisect_right

rows = [(t, d) for t in (7, 8, 9) for d in range(1, 101)]
index = sorted(rows)          # index (tenant_id, created_at)

# WHERE tenant_id IN (7, 9) AND created_at > 95
touched = 0
for t in (7, 9):
    lo = bisect_right(index, (t, 95))
    hi = bisect_right(index, (t, 10**9))
    touched += hi - lo
print(f'seeks: 2, entries touched: {touched}')

One seek per IN value, and created_at is still used to bound each one.

Only 10 of the 300 entries are read. Tenant 8 is never visited, and for tenants 7 and 9 only the dates after the cutoff are touched. Be aware that an ORDER BY created_at across the whole IN list is a different matter, because the two tenant blocks are each sorted but not merged. The engine may need a merge step or a sort for that.

A common piece of folklore says to put the most selective, highest-cardinality column first. As a general rule it is wrong. What matters is the shape of the query: which columns are compared with equality, which has a range, and what the ORDER BY needs. A low-cardinality column like status makes an excellent leading column when every query filters it by equality, because that equality carves out a block in which the range or sort column is already ordered. Ordering by selectivity alone gives you a few percent here and there. Matching the query shape can turn a scan-and-filter into a bounded read, or remove a sort entirely.

Each added column makes every index entry wider. Wider entries mean fewer keys fit in each page, so internal nodes have lower fan-out, the tree may need an extra level, and more pages compete for the buffer pool. Writes also touch the index more often, since any update to an indexed column modifies the entry. A composite should therefore contain exactly the columns that its queries use to seek, order, or filter, and no more.

A normal index lookup is a two-step trip. The engine descends the B-tree to find matching entries, then follows each entry's pointer into the table (the heap) to fetch the columns the index does not hold. Those heap visits are usually the expensive part, because matching rows are scattered across many pages and each one is a separate random read.

A covering index removes the second step. If the index contains every column the query touches (in SELECT, WHERE, ORDER BY and JOIN), the engine can answer from the index leaves alone and never opens the heap at all.

Every engine supports this idea but each names it differently, so you need to recognise three labels when reading plans.

Whether a query is covered depends on every column it touches, not just the SELECT list. A column that appears only in the WHERE clause still has to be found somewhere, and if the index lacks it the engine must visit the heap to check it. The table below uses an index on posts (user_id, created_at, title).

The obvious way to cover a query is to add the missing columns to the index key. That works, but every key column becomes part of the sort order, widens every entry in the internal nodes, and shrinks the fan-out of the tree. Postgres and SQL Server offer a cleaner tool: the INCLUDE clause, which stores extra columns in the leaf entries only.

CREATE INDEX idx_orders_customer
ON orders (customer_id)
INCLUDE (total, status);

customer_id is the sort key; total and status ride along in the leaf

Here the tree is still ordered by customer_id alone, so the upper levels stay narrow and shallow. The leaves carry total and status as payload, so a query that filters by customer and reads those two columns is covered.

The limits matter. Because included columns are not ordered, the engine cannot seek on them or use them to avoid a sort. They only satisfy the SELECT list (and a filter applied after the seek, which is read straight from the leaf). A query such as WHERE status = 'paid' alone gets no help from the index above.

MySQL's InnoDB has no INCLUDE clause, but it gives you one covered column for free. Every secondary index entry stores the primary key value so the engine can find the clustered row. That means the primary key is implicitly part of every secondary index.

-- InnoDB, with a secondary index on email
SELECT id FROM users WHERE email = 'ada@example.com';
-- Extra: Using index  (id is already in the index entry)

already covered without any extra column

In Postgres, a covering index is necessary for an Index Only Scan but not sufficient. The index entries do not record whether a row version is visible to your transaction, so the engine must know that from somewhere. It consults the visibility map, a compact per-page bitmap marking heap pages whose rows are all visible to everyone. For an all-visible page it trusts the index and skips the heap. For any other page it falls back to fetching the heap row to check.

You see the cost in EXPLAIN ANALYZE, which reports how many times the fallback happened. A table with heavy recent writes can show an Index Only Scan node that is secretly doing a heap read for most rows.

The fix is routine maintenance. VACUUM (or autovacuum catching up) sets the all-visible bits, after which Heap Fetches drops toward zero and the scan really does skip the heap.

The best case is dramatic. Suppose a query matches 5,000 rows spread over the heap. A plain index scan pays up to 5,000 random page reads. A covering index turns that into one descent followed by a sequential walk along adjacent leaf pages, because the leaves are a linked list in key order. Thousands of scattered reads become a short contiguous range read.

The price is paid on every write. The index is wider, so it takes more disk and more buffer-pool space, and each insert writes a bigger entry. Updating an included column used to touch only the heap; now it must also rewrite the index entry. In Postgres it also blocks the cheap HOT update, a heap-only tuple update that writes the new row version on the same heap page without touching any index (explained fully later in this chapter). Once an indexed or included column changes, every index has to be updated too.

This is why SELECT * defeats covering by definition: no practical index holds every column of a wide table. Narrow the column list to what the page or API really needs first, and only then ask whether an index could cover it.

A worked case: a user's feed shows the 20 newest post titles. The existing index is on (user_id), so each request finds the user's rows, fetches them from the heap, and sorts them. Replace it with a key that serves the filter and the order, plus the title as payload.

DROP INDEX idx_posts_user;

CREATE INDEX idx_posts_feed
ON posts (user_id, created_at)
INCLUDE (title);

SELECT title FROM posts
WHERE user_id = 7
ORDER BY created_at DESC
LIMIT 20;

seek on user_id, read created_at in order, take title from the leaf

The equality column comes first, the ordering column second, and the displayed column rides as payload. The engine seeks to the user, walks 20 leaf entries backwards, and returns titles with no Sort node and no heap read. Prove it by comparing buffer counts before and after, and confirm Heap Fetches is low.

When you send a query, you describe the result you want, not the steps to get it. The database turns that description into a plan, and the part that does this is the query planner (also called the optimizer). The planner is cost-based. It enumerates many candidate plans, estimates a cost for each one, and runs the cheapest. The order in which you wrote your joins, filters and subqueries is not a recipe it follows, so putting the selective condition first does not make the planner apply it first.

Cost is not milliseconds. It is an abstract unit assembled from a few I/O and CPU constants, and the planner only needs the units to be consistent so it can rank plans against each other. In Postgres the three that matter most are listed below. Reading one sequential page is the baseline of 1.0, and everything else is expressed relative to it.

The core formula is simple: estimated rows x per-row cost = plan cost. A sequential scan pays for every page of the table plus CPU for every row. An index scan pays a random page read for roughly each matching row, plus CPU for those rows only. The example below applies the three defaults to a table of 10,000 pages and 1,000,000 rows. It is a simplification, since a real planner also caps repeated visits to the same page, but it shows why the winner flips as more rows match.

SEQ_PAGE = 1.0
RANDOM_PAGE = 4.0
CPU_TUPLE = 0.01

pages = 10_000
rows = 1_000_000

seq = pages * SEQ_PAGE + rows * CPU_TUPLE
print(f'seq scan: {seq:.2f}')

for matched in (10, 10_000, 250_000):
    idx = matched * RANDOM_PAGE + matched * CPU_TUPLE
    print(f'index scan, {matched:>7} rows: {idx:.2f}')

Toy cost model using the Postgres default constants

Ten matching rows make the index scan about 500 times cheaper, so the planner uses it. At 10,000 matching rows (only 1% of the table) the index plan already costs twice the sequential scan, because each match is a random read. The planner is not being lazy when it ignores your index here. It is doing the arithmetic.

The formula needs a row count, and the planner cannot count rows by running the query, because that would be the very work it is trying to price. It relies on statistics gathered ahead of time: the total row count, the number of distinct values per column, a most-common-values (MCV) list with each value's frequency, and a histogram that divides the remaining values into equal-population buckets. These are collected by ANALYZE in Postgres and ANALYZE TABLE in MySQL, and Postgres also refreshes them in the background through autovacuum.

-- Postgres: refresh statistics for one table
ANALYZE orders;

-- MySQL
ANALYZE TABLE orders;

-- Postgres: what the planner knows about a column
SELECT n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Collect and inspect statistics

For status = 'shipped' the planner looks the value up in the MCV list and multiplies its frequency by the table's row count. For a range such as created_at > '2026-01-01' it reads the histogram. The result is the estimated rows that feed the cost formula. This is why a bad row estimate is the single most common cause of a bad plan: every later decision (which index, which join method, which join order) is priced from that number, so a wrong estimate makes the wrong plan look cheapest.

The classic way estimates go wrong is stale statistics. Imagine you bulk-load 10 million rows into a table that was nearly empty when ANALYZE last ran. The planner still believes the table holds a few hundred rows. At that size a nested loop is genuinely the cheapest join, so it picks one, and the query then runs it across 10 million rows. The plan was correct for the table the planner thought it had.

A second source of bad estimates is correlated predicates. For WHERE city = 'Paris' AND country = 'France', the planner estimates each condition separately and multiplies the fractions, because it assumes the columns are independent. They are not: every Paris row is in France, so the second condition removes nothing. Multiplying two small fractions gives a much smaller number than the truth. The example uses 2 million rows where 0.5% are Paris and 2% are France.

rows = 2_000_000
paris = 10_000
france = 40_000

estimate = paris * france // rows
actual = paris

print(f'independence estimate: {estimate} rows')
print(f'actual rows: {actual}')
print(f'off by a factor of {actual // estimate}')

Independence assumption: multiply the two fractions

An estimate of 200 rows against 10,000 real ones is enough to tip the planner into a nested loop or the wrong index, exactly as stale statistics do. There are two fixes. The first is extended statistics: in Postgres, CREATE STATISTICS tells the planner to track how columns relate, so it stops multiplying them blindly. The second is to reshape the query so the redundant predicate disappears, for example by filtering on city_id alone when the city already implies the country.

CREATE STATISTICS addr_city_country (dependencies)
ON city, country FROM addresses;

-- statistics objects are filled in by ANALYZE
ANALYZE addresses;

Teach Postgres that country depends on city

The default random_page_cost = 4.0 encodes an old fact: on a spinning disk, jumping to a random page means moving a physical head, so it costs several times more than reading the next page in order. On an SSD that gap mostly vanishes. If your data lives on SSD or NVMe and the setting is still 4.0, the planner overprices every index scan and reaches for sequential scans too often. Lowering it to about 1.1 makes index scans correctly attractive again.

-- try it in one session first
SET random_page_cost = 1.1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- then make it permanent for a fast tablespace
ALTER TABLESPACE fast_ssd SET (random_page_cost = 1.1);

Tell the planner the disk is an SSD

Access paths are not chosen in isolation. For a query with joins, the planner decides join order and join method in the same search where it picks scans, because the cheapest scan for a table depends on how that table is used in the join. An index changes which joins are even viable. A nested loop with an indexed inner table is cheap when the outer side is small, a merge join becomes free when indexes already supply sorted input, and a hash join needs neither.

Sometimes you still cannot get the plan you want. Most databases offer planner hints for that case. MySQL has FORCE INDEX, Oracle has comment hints such as INDEX, and Postgres has no hints in core but offers them through the pg_hint_plan extension. A hint pins one plan, and it keeps that plan after the data changes, long after the plan has stopped being the right one.

-- MySQL
SELECT * FROM orders FORCE INDEX (idx_orders_customer)
WHERE customer_id = 42;

-- Oracle
SELECT /*+ INDEX(o idx_orders_customer) */ *
FROM orders o WHERE customer_id = 42;

-- Postgres with pg_hint_plan
/*+ IndexScan(o idx_orders_customer) */
SELECT * FROM orders o WHERE customer_id = 42;

The same intent in three dialects

Planning costs time, so applications often avoid repeating it. A prepared statement is parsed once and then executed many times with different parameter values, and the database may reuse the plan between executions. That is a good trade until the plan was built for an unrepresentative value. This problem is called parameter sniffing: the plan is shaped by the first value it sniffed, and every later value pays for that choice.

Suppose customer_id = 1 is a huge customer with 2 million orders, while most customers have a dozen. Planned for the huge customer, the query picks a sequential scan, which is right for that value. Reused for a customer with 12 orders, it scans the whole table to find 12 rows. The reverse also happens: a plan built for a tiny customer uses an index scan and then crawls when the huge customer arrives.

Postgres handles this with the plan_cache_mode setting, available since version 12. In the default mode, auto, the first few executions each get a custom plan built for the actual parameter value. After that the server may switch to a generic plan that ignores parameter values, if it is not estimated to cost much more than the custom plans. Setting plan_cache_mode to force_custom_plan makes every execution re-plan with the real value. This costs a little planning time and removes the sniffing risk. Setting it to force_generic_plan does the opposite and always reuses one plan.

PREPARE orders_for (int) AS
  SELECT * FROM orders WHERE customer_id = $1;

-- plan each execution for its own parameter value
SET plan_cache_mode = force_custom_plan;

EXECUTE orders_for(1);    -- huge customer: seq scan is fine
EXECUTE orders_for(7342); -- tiny customer: index scan, re-planned

Prepared statement with per-value planning

A query plan is the planner's written answer to the question "how will I get these rows?". EXPLAIN prints that answer. Plain EXPLAIN only plans the query. It never runs it, so every number is an estimate built from table statistics. EXPLAIN ANALYZE plans the query and then actually executes it, and it adds the real timings and real row counts next to the estimates.

Because EXPLAIN ANALYZE executes the statement, running it on an UPDATE or DELETE changes your data. The fix is to open a transaction, analyze inside it, and roll back. You get the real timings and the table stays untouched.

BEGIN;
EXPLAIN ANALYZE
  DELETE FROM orders WHERE created_at < '2024-01-01';
ROLLBACK;

The delete runs for real inside the transaction, then ROLLBACK undoes it.

A plan is a tree, drawn with indentation. Each node takes rows from its children, does its job, and hands rows to its parent. The most indented nodes run first, so you read the plan from the bottom up and from the deepest level outward. The top line is the final step, not the first.

Here the Seq Scan is the deepest node, so it runs first and filters the table. Its rows go up into Sort, and the sorted stream goes up into Limit, which keeps the first twenty.

Every node line packs four numbers into one pair of parentheses. Once you can split them apart, plans stop looking like noise.

Seq Scan on orders  (cost=0.00..18334.00 rows=1000000 width=44)

One node, estimates only.

Cost is not milliseconds. It is a unit built from the planner's constants for page reads and per-row CPU work, so it only makes sense when comparing plans for the same query. width is the size of the row after the node's projection, so selecting fewer columns shrinks it.

Startup cost is the work that must finish before the node can hand over its first row. A sequential scan can emit a row right away, so its startup cost is 0. A Sort cannot emit anything until it has consumed every input row. A Hash node must build the entire hash table first. Both show a large startup cost, close to their total.

This explains the planner's behavior with LIMIT. If you only want 20 rows, a plan that streams rows from an index already in the right order pays almost nothing up front and can stop early. A plan that scans and sorts everything pays its full startup cost for 20 rows. The planner sees this and prefers low-startup plans when a small limit is present.

With ANALYZE each node gets a second set of parentheses holding what really happened.

Index Scan using idx_items_order on items  (cost=0.43..8.45 rows=1 width=32)
  (actual time=0.021..3.140 rows=998 loops=12)

Estimate on the first line, measurement on the second.

actual time is startup..total in milliseconds, rows is the real count, and loops is how many times the node ran. The catch is that both the time and the row count are averages per loop. A node on the inner side of a nested loop runs once per outer row, so you must multiply by loops to get its real contribution.

nodes = [
    ('Seq Scan on orders', 3.140, 12),
    ('Index Scan on items', 0.021, 1000),
]
for name, per_loop_ms, loops in nodes:
    print(f'{name}: {per_loop_ms:.3f} ms x {loops} loops = {per_loop_ms * loops:.1f} ms')

Total time is per loop, so multiply.

Once estimates and actuals sit side by side, the plan tells you what is wrong. Three signals are worth learning first.

Compare rows= in the estimate with rows= in the actual part of the same node. When they are close, the planner understood your data. When they are far apart, such as an estimate of rows=1 against an actual of rows=90000, the planner chose its plan on false beliefs. That mismatch is the smoking gun for stale or missing statistics. A planner that expects one row happily picks a nested loop, and then discovers that it must run 90,000 times.

Nested Loop  (cost=0.43..16.48 rows=1 width=60)
             (actual time=0.040..412.900 rows=90000 loops=1)

rows=1 against rows=90000: the statistics are wrong.

Some nodes print an extra line, Rows Removed by Filter: N. It means the database read N rows and threw them away because they failed a condition it could not use to seek. A large N next to a small rows= is wasted I/O. It usually points to a missing index, or to an index whose column order does not match the query, so the condition became a filter instead of a seek.

Seq Scan on orders  (actual time=0.018..412.300 rows=42 loops=1)
  Filter: (customer_id = 7)
  Rows Removed by Filter: 999958

One million rows read to keep 42.

Cost numbers only approximate disk work. To see the real thing, ask for buffer counts with EXPLAIN (ANALYZE, BUFFERS). Each node then reports shared hit (page found in the cache) and shared read (page fetched from disk or the OS). Two plans can have similar times on a warm cache while one reads ten times more pages, and that one will be slow on a cold cache.

MySQL prints a flat table instead of an indented tree, but the questions are the same: what access path, which index, how many rows. EXPLAIN gives one row per table in the query. A real EXPLAIN ANALYZE that executes the query and reports timings exists since MySQL 8.0. Before that you only get estimates.

Read type from best to worst: const, then ref, then range, then index, then ALL. A column showing ALL on a large table, or Using filesort and Using temporary on a hot query, is where to start looking. Using index is the one Extra value you want to see.

The text format is built for human eyes. If you want a program to inspect the plan, use EXPLAIN (FORMAT JSON) in Postgres, which returns the same tree as nested objects with fields such as Node Type, Total Cost and Plan Rows. A CI job can save the plan for important queries, compare it with the previous run, and fail the build when a node type changes, say from Index Scan to Seq Scan, or when the estimated cost jumps by a large factor.

EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 7;

Plain EXPLAIN keeps CI cheap and deterministic; add ANALYZE only against a safe copy of the data.

Every EXPLAIN plan is a tree built from a small set of node types. Learn the dozen or so you will meet constantly and any plan becomes readable. The leaves of the tree are always scan nodes, which decide how rows get out of a table. We start there.

A Seq Scan (a full table scan in other engines) reads every heap page from start to finish and tests each row against the filter. It never touches an index. Each page is cheap because the disk and the OS read-ahead love sequential access. It is the correct choice when the query wants a large fraction of the table, or when the table is only a few pages. It becomes the wrong choice when a handful of rows match in a huge table, because you still pay to read all of it.

EXPLAIN SELECT * FROM orders WHERE status <> 'cancelled';

Most rows match, so reading everything is the cheapest plan

An Index Scan descends the B-tree to the first matching entry, then for every match follows the stored TID to the heap and fetches the row. The index narrows the search to a few leaf entries, but the heap visits are random I/O: one visit per matching row, and neighbouring index entries can point at far-apart pages. That is why it shines on a few rows and fades as the match count grows.

If every column the query needs lives in the index leaves, the engine can skip the heap entirely. That node is an Index Only Scan. The number to check under EXPLAIN ANALYZE is Heap Fetches. A value of 0 means the index really answered the query alone. A large value means pages are not marked all-visible and the engine is still going to the heap, so VACUUM is overdue.

Between a few random heap hits and reading the whole table sits a third strategy. A Bitmap Index Scan walks the index and collects the TIDs of all matches into an in-memory bitmap, without touching the heap. Then a Bitmap Heap Scan sorts that bitmap by physical page and reads each needed page exactly once, in order. Random access becomes ordered access, and a page holding twenty matches is read once rather than twenty times.

Because bitmaps are just sets of locations, they can be merged. BitmapAnd keeps the pages present in both inputs, and BitmapOr keeps those present in either. This is how two separate single-column indexes cooperate on one query: each produces its own bitmap, the engine combines them, and the heap is read once for the result. It also makes an OR across two indexed columns workable, where a single composite index could not help.

Seeing Recheck Cond on a Bitmap Heap Scan is normal, not a warning. The bitmap keeps exact row locations while it fits in work_mem. If it grows too large, the engine degrades it to remember only whole pages (a lossy bitmap). Then every row on those pages must be re-tested against the condition, which is what the recheck does. EXPLAIN ANALYZE shows this as Heap Blocks: exact=… lossy=…. Many lossy blocks suggest raising work_mem.

Join nodes sit above scans and combine two inputs. The planner picks among three algorithms, and each is the right answer in a different situation. The names tell you the mechanics, so the decision is mostly about input sizes and available indexes.

A Nested Loop takes each row from the outer input and probes the inner side for matches. The total work is roughly outer rows multiplied by the cost of one probe. That makes it excellent when the outer side is tiny and the inner side has an index on the join key, because each probe is a few page reads. It is catastrophic when the outer side is large, or when the inner side has no index and each probe becomes a full scan of the inner table.

Take 3 outer rows joined to an indexed inner table: 3 probes at about 3 page reads each is roughly 9 reads. Now take 1,000,000 outer rows joined to an inner table with no index on the key: that is 1,000,000 full scans of the inner table, an effectively endless query. A stale row estimate (the planner believed the outer side had 3 rows) is the classic way a plan lands in the second case.

Notice loops=3 on the inner node: it ran once per outer row. In ANALYZE output always multiply the inner node's time by its loops. A huge loops count on a slow inner node is the signature of a nested loop gone wrong.

A Hash Join builds an in-memory hash table from the smaller input, then streams the larger input past it, probing for each row. No index is needed, which makes it the workhorse for big unindexed joins. The cost is memory. If the hash table exceeds work_mem, the join splits into batches and spills to disk, shown as Batches: 4 (or any value above 1). A Merge Join requires both inputs sorted on the join key and walks them in lockstep. It is nearly free when indexes already deliver that order, and expensive when it must add Sort nodes first.

A Sort node orders its input in memory when it fits in work_mem, using a quicksort. When it does not fit, the engine writes sorted runs to temporary files and merges them. EXPLAIN ANALYZE reports this plainly. A line such as Sort Method: external merge Disk: 24MB means work_mem was exceeded and the sort went to disk. You have two ways out: raise work_mem for that session or query, or give the planner an index that already supplies the order so the Sort node disappears altogether.

SET work_mem = '64MB';   -- session only, then re-run EXPLAIN ANALYZE
-- or remove the sort entirely:
CREATE INDEX idx_events_created ON events (created_at);

Two fixes: more memory, or an index that provides the order

The same memory limit governs hash tables, which is why Batches: 4 on a Hash Join and Disk: 24MB on a Sort are two views of the same problem. Raising work_mem blindly is risky, because the limit applies per node and per connection; a plan with several sorts and hashes can use many multiples of it.

When you see Gather at the top of a subtree with Parallel Seq Scan beneath it, the engine launched worker processes. Each worker scans a share of the table's pages, and Gather collects their output into one stream. The plan shows Workers Planned and, under ANALYZE, Workers Launched. This changes the scan-versus-index comparison: a seq scan split across 8 workers can genuinely beat an index scan that must do random heap visits on one core, especially when a big fraction of the table matches.

The usual framing, 'an index scans some rows and a seq scan scans all of them', is the wrong comparison. Both plans end up reading pages of the heap. What differs is the access pattern. A seq scan walks the heap front to back, which is exactly what disks, OS read-ahead and caches are built for. An index scan takes a pointer from a leaf entry and jumps to that heap page for one row, then takes the next pointer and jumps again, in index order rather than disk order.

Per page, a sequential read is roughly 4-10x cheaper than a random one. Postgres encodes the low end of that range in its default cost constants: seq_page_cost = 1.0 against random_page_cost = 4.0. So the real question is never 'how many rows do I touch?' but 'how many pages do I touch, and in what order?'

A heap page holds many rows, often around a hundred for a narrow table. If the matching rows are scattered through the heap, a 10% match means that almost every page contains at least one hit. The index scan therefore visits nearly every heap page anyway. It does so once, at random, one row at a time, and a page that was evicted from cache in between may even be read twice. A seq scan reads the same pages in a single ordered pass and wins by a wide margin.

The small model below makes the effect concrete. It assumes a table of one million rows at 100 rows per page, so 10,000 pages. A seq scan costs one unit per page. An index scan costs four units per page it fetches. The 'scattered' column uses the expected number of distinct pages hit when the matching rows are spread at random. The 'clustered' column assumes the matching rows sit next to each other, so only the fraction of pages that holds them is read.

ROWS = 1_000_000
PER_PAGE = 100
PAGES = ROWS // PER_PAGE
SEQ, RANDOM = 1, 4

def scattered_pages(matches):
    # expected distinct pages hit when matches land on random pages
    return round(PAGES * (1 - (1 - 1 / PAGES) ** matches))

print(f"{'matched':>7}  {'seq scan':>8}  {'scattered':>9}  {'clustered':>9}")
for permille in (1, 10, 50, 100, 250):
    matches = ROWS * permille // 1000
    scattered = scattered_pages(matches) * RANDOM
    clustered = PAGES * permille // 1000 * RANDOM
    print(f"{permille / 10:>6}%  {PAGES * SEQ:>8}  {scattered:>9}  {clustered:>9}")

Cost in page-cost units for the same query, depending on where the matching rows live

Read the 10% row first. The scattered index scan touches all 10,000 pages and pays the random price for each, so it costs four times what the seq scan costs. The clustered index scan reads only 1,000 pages and costs 4,000, far below the seq scan. The only difference between those two columns is where the matching rows physically live, which is covered under physical correlation below. Also notice that this crude model lets the scattered index scan lose already at 1%. Real planners do better than that because of caching and bitmap scans, which later parts of this section cover.

Put those effects together and a rule of thumb emerges. The point where a seq scan starts to beat the index-based plans usually falls somewhere between 5% and 25% of the table, and it is a range because it depends on the data and the hardware, not a constant.

Correlation is the statistic that decides which column of the cost table above you are in. When Postgres runs ANALYZE, it records for each column how closely the sorted order of its values matches the physical order of the rows in the heap. The number is called correlation and lives in pg_stats. It runs from -1 to 1, where values near either end mean the heap is almost perfectly ordered (ascending or descending) and values near 0 mean the order on disk has nothing to do with the column.

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('created_at', 'customer_id');

Illustrative values for a typical orders table

The reason for those two values is how the table was filled. Orders are appended as they arrive, so created_at follows the physical order almost exactly. Customers place orders at random moments, so rows for one customer end up spread across the whole heap.

The planner takes this into account when it costs an index scan. A 5% range on created_at can therefore be a cheap index scan, while a 5% selection on customer_id of the same size is better served by a bitmap scan or a seq scan. Same table, same selectivity, very different best plan.

If a column matters enough, you can force the heap into its order. CLUSTER rewrites the whole table in the order of a chosen index, which pushes the correlation of that column to nearly 1. Run ANALYZE afterwards so the planner sees the new statistics.

The cost is steep. CLUSTER takes an ACCESS EXCLUSIVE lock, so reads and writes on the table block until it finishes, and it needs enough free disk space to hold a second copy of the table and its indexes. It is also a one-time rewrite, not a property of the index. New rows are placed wherever there is room, and updated rows get a new version elsewhere, so the order decays as the table changes.

Take a table of a few hundred rows that fits in one or two pages. A seq scan reads those two pages and is done. An index scan must read the root page, a leaf page, and then the heap page for each match, which is as many pages as the whole table or more. No selectivity makes the index worthwhile here. When the planner ignores your index on such a table, it is correct and the right response is to leave it alone.

EXPLAIN SELECT * FROM countries WHERE code = 'FR';
-- Seq Scan on countries  (cost=0.00..5.75 rows=1 width=40)
--   Filter: (code = 'FR'::text)
-- 300 rows, 2 pages: 2 * 1.0 page cost + 300 * 0.01 row cost + filter work

Illustrative plan for a 300-row table that has an index on code

Everything so far assumed that the query needs all of its matching rows. A LIMIT breaks that assumption. If an index already delivers rows in the requested order, the executor can walk the index, fetch the first 20 heap rows and stop. A plan without that index has to read the whole table, sort everything, and only then hand back 20 rows. The first plan does work proportional to the limit; the second does work proportional to the table.

EXPLAIN SELECT * FROM events ORDER BY created_at DESC LIMIT 20;
-- Limit  (cost=0.43..3.10 rows=20 width=64)
--   ->  Index Scan Backward using idx_events_created on events
--         (cost=0.43..133420.55 rows=1000000 width=64)

Illustrative plan. The Limit node's total cost is a small slice of the index scan's total

The planner can see this because of startup cost: the index scan can produce its first row almost immediately, while the sort cannot produce anything until it has consumed its input. One caveat applies. If the query also has a filter that rarely matches, the ordered walk may have to read far more than 20 rows before it finds 20 that qualify, and a bad estimate of that filter is a classic cause of a LIMIT query that is slower than expected.

Between the two extremes sits a plan that takes the best part of each. A bitmap index scan first reads the index and collects the locations of all matching rows without touching the heap. The bitmap heap scan then visits the heap pages in physical order, once per page, and checks the matching rows on each one. Random jumps become one forward sweep over only the pages that matter.

This is why the 5-25% band belongs to bitmap scans. Each page is read at most once, never revisited, and in an order that looks much more like a sequential pass. The planner also lowers the per-page price as the fraction of the table it fetches grows, which is why a bitmap plan degrades gracefully towards a seq scan instead of falling off a cliff. The same mechanism lets BitmapAnd and BitmapOr combine two separate indexes before the heap is touched.

EXPLAIN SELECT * FROM orders WHERE customer_group = 12;
-- Bitmap Heap Scan on orders  (cost=1240.50..9800.20 rows=62000 width=48)
--   Recheck Cond: (customer_group = 12)
--   ->  Bitmap Index Scan on idx_orders_group
--         Index Cond: (customer_group = 12)

Illustrative plan for a filter matching about 6% of an uncorrelated column

Here are the two basic access paths next to each other. Neither is the better one; each has a shape of workload it fits.

The decision the planner makes can be written down as a few questions. Asking them in this order is also a good way to sanity-check a plan you did not expect.

Sometimes EXPLAIN ANALYZE shows a seq scan you did not expect. Before you change anything, ask the planner what the index plan would have cost. Turn seq scans off for your session only, run the same query again, and compare. The setting does not forbid seq scans; it adds a very large penalty to them, so the planner uses the index plan whenever one is possible. Compare the cost and the actual time of the forced plan with the original, not the penalized seq scan figure.

SET enable_seqscan = off;      -- this session only
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 90;
RESET enable_seqscan;          -- always reset

A diagnostic, run on production-shaped data

In this example the forced index plan is both estimated and measured to be worse, so the planner was right and you can stop. If the forced plan had been faster in reality while its estimate looked higher, the planner's numbers are off. That points to stale statistics (run ANALYZE), to a random_page_cost of 4.0 on a machine with SSDs (a value near 1.1 fits better), or to correlation that the planner cannot see. Wrapping the experiment in a transaction with SET LOCAL is another safe way to keep the change from outliving the test.

Everything so far has treated an index as a gift: pay some disk, get faster reads. The bill arrives on the write side. An index is a second copy of part of your table, kept in sorted order, and the database must keep that copy correct on every change. A table with 8 indexes does 9 structural updates per INSERT: one for the heap (the table itself) and one B-tree insertion for each index.

The count of structural updates understates the cost, because each of those updates is also written to the WAL (Postgres) or redo log (MySQL, Oracle). A single logical row insert becomes nine page modifications and nine sets of log records, all of which must be flushed to disk and shipped to replicas.

The small program below turns that into throughput. It assumes a steady 2,000 inserts per second and counts the structural updates the storage engine has to perform as secondary indexes are added.

inserts_per_second = 2000
for secondary in (0, 2, 5, 8):
    structural = 1 + secondary
    print(f"{secondary} secondary indexes: {structural} structural updates per INSERT, {inserts_per_second * structural} per second")

The heap counts as one update, each index adds one more.

Updates and deletes pay too. A DELETE marks entries dead in every index, and an UPDATE that changes an indexed column removes the old entry and inserts a new one in that index. Postgres has one important shortcut here, and extra indexes make it harder to use.

A HOT update (heap-only tuple) lets Postgres write the new row version on the same heap page and leave every index alone, because the existing index entries still lead to the right page. It only works when no indexed column changed and the new tuple fits on the same page. Each extra index widens the set of columns that count as indexed, so more ordinary updates fail the first test and fall back to the full, expensive path.

INDEXED = {"email", "status", "created_at"}

def update_kind(changed, indexed, page_free, new_size):
    if set(changed) & indexed:
        return "index entries rewritten"
    if new_size > page_free:
        return "index entries rewritten (page full)"
    return "HOT, indexes untouched"

print("last_seen, room on page ->", update_kind(["last_seen"], INDEXED, 200, 120))
print("status changes ->", update_kind(["status"], INDEXED, 200, 120))
print("last_seen, page is full ->", update_kind(["last_seen"], INDEXED, 60, 120))
INDEXED.add("last_seen")
print("last_seen, newly indexed ->", update_kind(["last_seen"], INDEXED, 200, 120))

A model of the two HOT conditions, not Postgres itself.

Indexes are not only on disk. To be useful they have to be cached in the buffer pool, the same memory that holds your hot table pages. Index pages that get touched are pulled in and compete for space. Even a rarely-used index still gets its pages loaded on every write, so it keeps evicting table pages your real queries need. The cost shows up as a lower cache hit ratio and more physical reads everywhere, not as a slow query you can point at.

An index pays off when it narrows the search to a small fraction of the table. A boolean has two values, and a status column with three values splits the table into three huge groups. Asking for one group returns a third or half of the rows, and at that fraction a sequential scan wins, so the planner ignores the index. You still pay for it on every write.

The exception is a skewed column where you only ever query the rare value, for example 0.4% of jobs that are still pending. Do not index the whole column then. Use a partial index that stores only the rows you search for, which keeps it tiny and means most writes never touch it.

Every index on a table is a candidate access path. At planning time the optimizer has to cost each plausible one, and with joins it multiplies those choices across tables and join orders. A table with dozens of indexes adds measurable planning time to every query that touches it, which hurts most for short queries that run thousands of times a second.

A B-tree keeps keys in sorted order, so a new key must land in the leaf where it belongs. With a sequence or a timestamp, new keys always go to the right-most leaf, which stays in cache and fills up neatly. With a random UUID (v4), each insert lands on an arbitrary leaf somewhere in the tree. That leaf is likely not in memory, so it must be read first. When it is full it splits, leaving two half-empty pages. The result is write amplification across the whole tree, a bloated index and poor cache use. In InnoDB the damage is bigger, since the primary key is the table and every secondary index also stores the key.

The fix is to keep the uniqueness of a UUID but add a time-ordered prefix. UUIDv7 and ULID both start with a timestamp, so new keys sort after old ones and behave like a sequence. Recent Postgres versions ship a uuidv7() function, and libraries exist for most languages. If you do not need globally unique ids generated outside the database, a plain bigint identity column is smaller still.

Because of the leftmost-prefix rule, an index on (a, b) can answer everything an index on (a) can. The narrow one is subsumed: it costs writes, space and cache, and buys nothing. Exact duplicates happen too, usually when two people add the same index under different names, or an ORM and a migration both create it.

The rule only works in one direction. (a, b) does not replace an index on (b), and it does not replace a UNIQUE index on (a), which enforces a constraint. Check before dropping.

Indexes get added during incidents and almost never removed. After a few years of changed queries, a table carries indexes that nobody has used in months. Postgres counts index scans for you, and MySQL exposes a ready-made view.

Loading millions of rows into a table with live indexes means inserting each row into each tree one at a time, in arbitrary order, with a log record for every step. Building an index from scratch is far cheaper: the database sorts all the keys once and writes the tree bottom-up in packed pages. For a large initial load or a big backfill, the usual result is that dropping secondary indexes, loading, then recreating them is several times faster than loading with the indexes live.

This only makes sense when the table is offline or can tolerate slow queries while the indexes are missing. On a live system serving traffic, dropping indexes is not an option.

A plain CREATE INDEX takes a lock that blocks writes to the table for as long as the build runs. On a large table that can be minutes, and every INSERT, UPDATE and DELETE queues behind it. Postgres offers CREATE INDEX CONCURRENTLY, which builds the index without blocking writes by scanning the table more than once. MySQL has online DDL, and external tools such as pt-online-schema-change or gh-ost do the same job for large tables.

The trade-off is that the concurrent build is slower and can fail partway, for example on a uniqueness violation or a cancelled session. It then leaves behind an INVALID index. It is not used for queries, but it is still maintained on every write, so it costs you without helping you.

On a write-heavy OLTP table, treat anything past about 5 indexes with suspicion. This is a heuristic, not a limit: a read-mostly reporting table can carry many more. What matters is that every index can be tied to a real query, and that the query gains more than the writes lose.

An index is a sorted copy of one column's values (or several columns' values). The planner can only use it when the query compares the stored value itself against something. The moment the query compares a *transformed* version of the column, the sorted order of the raw values says nothing about the order of the transformed ones, and the planner has no choice but to read every row. Nothing errors and nothing warns you. The query just gets slower as the table grows.

The first three mistakes are all versions of this: something happens to the column before the comparison. The test to apply is simple. Is the column standing alone on one side of the operator, with the same type as the thing on the other side?

YEAR(created_at) produces a new value for every row, and the index on created_at is not sorted by that value. The database would have to compute the function for each row to find the matches, which is a full scan. Rewrite the filter as a half-open range on the bare column, and the index can seek straight to 2026-01-01 and read forward until it passes 2027-01-01.

-- Scans the whole table: the function hides created_at
SELECT id FROM orders WHERE YEAR(created_at) = 2026;

-- Seeks: the bare column is compared with constants
SELECT id FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

Use a half-open range (>= start, < next start) so no boundary row is lost or double counted.

If you genuinely cannot avoid the function, you can index the function's result instead. The fix is an index on that exact expression, covered under Case-insensitive search below. The expression in the query must match the one in the index definition.

Arithmetic is just another function. price * 1.2 > 100 forces the database to multiply every price before comparing. Algebra is on your side: move the math to the constant side so the column stands alone.

-- Cannot seek on price
SELECT id FROM products WHERE price * 1.2 > 100;

-- Same rows, index-friendly (100 / 1.2 = 83.333...)
SELECT id FROM products WHERE price > 100 / 1.2;

The right-hand side is a constant, so it is computed once per query, not once per row.

When the two sides of a comparison have different types, the database converts one of them. If it converts the column, you have the same problem as a function call, because the conversion is applied to every row. MySQL is the classic offender. Comparing a varchar column to a bare number makes MySQL convert each stored string to a number, so an index on that column cannot be used.

-- phone is VARCHAR(20) with an index
SELECT id FROM users WHERE phone = 5551234;     -- every row cast, full scan
SELECT id FROM users WHERE phone = '5551234';   -- index seek

Match the literal's type to the column's type. Quote string values.

The same thing happens across joins when the two columns differ in character set or collation. If orders.email is utf8mb4 and customers.email is utf8mb3, MySQL must convert one side to compare them, and the converted column loses its index. Mixed integer widths are not the concern people think. MySQL compares INT with BIGINT natively, so that join keeps its index.

The next group is not about transforming the column. The query shape itself asks something a sorted structure cannot answer cheaply. A B-tree finds values by descending from the root, comparing against separator keys. If the question gives no starting point for that descent, there is nothing to seek on.

LIKE 'term%' has a known prefix, so the tree can jump to the first entry starting with term and read forward. LIKE '%term' begins with an unknown, so there is no place to start. It is the phone-book problem: you can find everyone named Smith, but not everyone whose name ends in -son. Two real remedies exist when you truly need this search.

• Trigram index (pg_trgm in Postgres): indexes every three-character fragment of the text, so substring and suffix matches can use it.
• Reverse-column index: store or index the reversed string, then search LIKE 'mret%' for the suffix term. This only helps suffix search, not matching in the middle.

WHERE lower(email) = 'ada@x.com' is a function call on the column, so a plain index on email is skipped. Give the planner something that matches the query. One option is an expression index on exactly lower(email). Another is a citext column, or a case-insensitive collation, so the plain comparison is already case-insensitive and a normal index works.

-- The expression must match the query text exactly
CREATE INDEX idx_users_email_lower ON users (lower(email));

SELECT id FROM users WHERE lower(email) = 'ada@x.com';  -- seeks
SELECT id FROM users WHERE upper(email) = 'ADA@X.COM';  -- does not

An index on lower(email) cannot answer upper(email). The two expressions are different.

Suppose you have an index on (a, b) and run WHERE a = 1 OR b = 2. Rows matching only b = 2 are scattered through the whole index, so a single seek cannot collect them. You have two good ways out. Give each column its own index and let the planner combine them with a bitmap OR, or rewrite the query as two index-friendly queries joined with UNION ALL.

-- Each branch can use its own index
SELECT id FROM t WHERE a = 1
UNION ALL
SELECT id FROM t WHERE b = 2 AND a IS DISTINCT FROM 1;

The second branch excludes rows the first already returned, so UNION ALL does not duplicate them.

status <> 'done' or id NOT IN (...) describe almost the whole table, minus a few rows. An index is only worth using when it picks out a small fraction of rows. When the answer is "nearly everything", reading the table sequentially is cheaper, and the planner will choose a seq scan. It is right to do so. Do not try to force an index here. If the real need is a small set, say so positively instead, for example status IN ('new', 'active') or a partial index on the rare value.

A composite index on (a, b) is sorted by a first, and by b only within each value of a. A query filtering on b alone gets no useful ordering: the matching entries are spread across every a group. At best the database scans the entire index and filters. Either put the column you always filter on first, or add a separate index for the query that filters on b.

An index can hand back rows in its own order, or exactly the reverse of it, without sorting. With an index on (a ASC, b DESC), both ORDER BY a ASC, b DESC and ORDER BY a DESC, b ASC are free. But ORDER BY a ASC, b ASC is neither, because the second column would need to be read in the opposite direction within each group. A separate Sort node appears in the plan and the whole result must be sorted before the first row is returned.

LIMIT 20 OFFSET 100000 sounds like "jump to row 100,000". The database cannot do that. Entries in an index have no row numbers, so it must walk and discard the first 100,000 entries before it reaches the 20 you want. Page 1 is fast, page 5,000 is slow, and the cost climbs linearly with the offset.

Keyset pagination (also called the seek method) replaces the row count with a bookmark: the sort values of the last row you showed. The next page asks for rows strictly before that bookmark, so the index can descend straight to it. The cost stays constant no matter how deep the user goes. Include a unique tiebreaker such as id, or rows sharing the same timestamp will be skipped or repeated.

-- Degrades with depth: walks 100,020 entries
SELECT id, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Constant cost: seeks straight to the bookmark
SELECT id, created_at FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The two ? values are created_at and id of the last row on the previous page. An index on (created_at, id) serves both the filter and the order.

The sketch below counts the entries each approach touches over 200,000 sorted ids. The offset version must walk past everything before its page. The keyset version uses a binary search, which stands in for the index descent, and then reads just the page.

Both approaches return the same rows. Keyset's one trade-off is that users move forward and backward through pages rather than jumping to an arbitrary page number, which is what infinite scroll and "next" buttons need anyway.

Indexes are created by migrations, and migrations get skipped, fail halfway, or get applied by hand on one environment and not another. A query that is fast on your laptop proves only that your laptop has the index. Before you start debugging a slow query, check what the production table really has.

\d orders              -- Postgres: columns, indexes, constraints
SHOW INDEX FROM orders; -- MySQL: one row per indexed column

Also look for an INVALID index in Postgres, left behind by a failed CREATE INDEX CONCURRENTLY. It exists but is never used.

When a query that should use an index does not, walk this list in order. Each step takes seconds to check and rules out one of the mistakes above.

Index tuning goes wrong when it starts from a hunch such as "the orders table feels slow". This section gives a fixed order of work that starts with evidence and ends with evidence. It has eight steps, plus two checks on what an index costs once it exists.

The database already records which statements cost the most. In Postgres, the pg_stat_statements extension keeps a row per normalised statement, with call count and cumulative execution time. Sort it by total_exec_time and the top rows are where your server actually spends its effort.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The ten statements that consume the most database time

MySQL has the same information in two places. The slow query log captures statements above a time threshold, and performance_schema keeps aggregated per-statement statistics. Whichever engine you use, the principle is the same: let the server tell you what is expensive.

The slowest single execution is the one people notice, but it is rarely the one that matters. What matters is calls multiplied by mean time. A 5ms lookup that runs 2 million times a day uses far more database time than a 3 second report that runs once an hour.

queries = [
    ('lookup_user', 5, 2_000_000),
    ('hourly_report', 3000, 24),
]
ranked = sorted(queries, key=lambda q: q[1] * q[2], reverse=True)
for name, ms, calls in ranked:
    secs = ms * calls / 1000
    print(f'{name:<14} {ms:>5} ms x {calls:>9,} = {secs:>9,.0f} s')

Total time per day for each query

The small query costs about 140 times more database time. Shaving 1ms off it saves more than making the report instant would. This is why the ranking comes before any EXPLAIN.

Once you have a target, run it with EXPLAIN (ANALYZE, BUFFERS). The plan is only as meaningful as the data under it. On a dev table with 1,000 rows the planner correctly decides that a sequential scan is cheapest, so the plan tells you nothing about production. Use a restored copy, a replica, or a data set with the same row counts, value distribution and physical layout.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 4821 AND status = 'open'
ORDER BY created_at DESC LIMIT 20;
ROLLBACK;

ANALYZE really executes the statement, so wrap anything that writes in a transaction you roll back

BUFFERS adds the shared hits and reads for each node. These are the I/O the query actually did, and they are a steadier measure than the cost numbers, which are only estimates.

Every node in an ANALYZE plan shows rows= for the estimate and rows= again inside the actual section. Scan the plan for the node where those two numbers are furthest apart. That subtree is where the planner's picture of your data is wrong, and wrong estimates lead to wrong join orders and wrong scan choices above it.

Nested Loop  (cost=0.43..812.10 rows=3 width=44)
             (actual time=0.05..2210.40 rows=88000 loops=1)
  -> Index Scan using idx_orders_customer on orders
       (rows=3) (actual rows=88000 loops=1)

An estimate of 3 rows against 88,000 actual is the smoking gun

Remember that actual time is per loop. If a node shows loops=12, multiply by 12 for its real cost. Ignore the parts of the plan whose estimates are close, even if they look long, and focus on the one subtree that is off.

A large estimate gap usually means the statistics are stale or too coarse, for example after a bulk load. Refreshing them costs seconds and adds nothing to maintain, while a new index taxes every write for as long as it exists. So try the cheap fix first.

ANALYZE orders;
-- then re-run the EXPLAIN and see whether the gap closed

MySQL equivalent: ANALYZE TABLE orders

If the gap closes and the plan becomes fast, you are done and no index was needed. If two columns are correlated, such as city and country, plain ANALYZE will not fix the estimate, and CREATE STATISTICS on those columns is the next thing to try. Only when the estimates are sound and the plan is still slow do you design an index.

Read the query's predicates in a fixed order. Columns compared with equality come first, because they let the index seek to one narrow slice. Then the single range column, because a range ends the seek. Then the columns in the ORDER BY, so the rows come out already sorted and no Sort node is needed. Finally, INCLUDE the payload columns that only appear in the SELECT list. They ride along in the leaf pages without widening the sort key.

CREATE INDEX CONCURRENTLY idx_posts_status_created
ON posts (status, created_at)
INCLUDE (title);

Equality, then range or sort, then the payload

CONCURRENTLY avoids a long write lock on a live table, at the price of a slower build. If the build fails it can leave an INVALID index behind that you must drop.

After the index exists, run the same EXPLAIN (ANALYZE, BUFFERS) again. A plan that now says Index Scan instead of Seq Scan is not proof of improvement, because an index scan can be slower on a poorly correlated table. Compare the actual time and the buffer reads before and after. If you built a covering index, look for Index Only Scan with Heap Fetches: 0, and run VACUUM if the number is high.

One good plan in your session does not prove that production traffic uses the index. Check again after a week of real load. The counter idx_scan in pg_stat_user_indexes records how many times each index has been scanned.

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'posts'
ORDER BY idx_scan;

A count of 0 after a full week means the index is dead weight; drop it

An index belongs to the table, not to one query. A new index gives the planner another path to cost for every query on that table, and it may now choose that path for a query you never looked at, sometimes wrongly. After adding one, re-check the other heavy statements on the same table in pg_stat_statements, and compare their mean times before and after.

Every index occupies disk and, more importantly, competes with table pages for the buffer pool. Measure it rather than assuming it is small.

SELECT pg_size_pretty(pg_relation_size('idx_posts_status_created'));

Compare the result with your buffer pool budget

A 6 GB index on a server with an 8 GB buffer pool will push hot table pages out of memory and can slow queries that have nothing to do with it. A wider key from extra columns or INCLUDE payload makes the index bigger, so only include what the query needs.

An index is a separate, ordered, redundant copy of some columns, kept next to the table and updated by the database for you. The B+tree is the default because one ordered structure serves equality lookups, ranges and ORDER BY. Internal nodes hold only separator keys. All real entries sit in the leaves, which are linked sideways, so a range scan descends once and then walks along the leaves. Fan-out is in the hundreds, so a million rows need about 3 levels and a billion need 4 to 5. The upper levels stay cached in RAM, so a lookup costs about 3 to 4 page reads.

A composite index behaves like a phone book sorted by last name, then first name. You can find every Smith, or Smith/Ada, but you cannot find every Ada without reading the whole book. So the column order decides which queries the index can seek. Put the equality columns first (IN counts as equality). Then add at most one range column. Then add the columns you sort by. Once the index reaches the range column, seeking stops, and later columns can only filter or cover.

-- WHERE tenant_id = ? AND created_at > ? ORDER BY created_at DESC LIMIT 20
CREATE INDEX idx_feed ON posts (tenant_id, created_at);

Equality column first, then the range and sort column: a bounded scan with no Sort node.

The leftmost prefix rule follows from this. An index on (a, b, c) serves queries that filter on a, on a, b, or on a, b, c. It never helps a query that filters only on b or only on c. Because of this, (a, b) makes a separate (a) index redundant, but it does not replace an index on (b).

A covering index holds every column the query touches, so the engine never visits the heap. Use INCLUDE for payload columns that are only read. They live in the leaf but not in the sort key, so the key stays narrow and the tree stays shallow. In Postgres an index-only scan still consults the visibility map, so run VACUUM. Otherwise you will see Heap Fetches above 0 and lose the benefit. SELECT * defeats covering by definition.

CREATE INDEX idx_posts_feed ON posts (user_id, created_at) INCLUDE (title);

EXPLAIN (ANALYZE, BUFFERS)
SELECT title FROM posts WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20;
-- expect: Index Only Scan, Heap Fetches: 0

Key = equality then sort; payload in INCLUDE.

EXPLAIN shows the plan the planner estimates. EXPLAIN ANALYZE actually runs the query and measures it. The planner chooses by cost, using statistics for row counts, distinct values and histograms. So a wrong row estimate usually causes a bad plan. Read the plan from the most indented node upward, and compare rows= in the estimate with rows= in the actual figures. A node that estimated 1 row and returned 90,000 is where the plan went wrong.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

-- Look for:
--   rows=1 (estimate) vs rows=90000 (actual)  -> stale or missing statistics
--   Rows Removed by Filter: large             -> missing or misordered index
--   actual time is PER loop: multiply by loops=
--   Sort Method: external merge / Batches > 1 -> spilling past work_mem

Wrap EXPLAIN ANALYZE on INSERT, UPDATE or DELETE in BEGIN ... ROLLBACK, because it really executes.

The real contrast is sequential I/O against random I/O, not scanning everything against scanning a part. An index scan on 10% of the rows can touch nearly every heap page, one at a time, and lose to a single sequential pass. The exact crossover also moves with row width, physical correlation and the storage medium. On SSDs, lowering random_page_cost from 4.0 toward 1.1 lets the planner price index scans more fairly.

Every index taxes three things. It taxes writes, because each insert, update or delete also updates every index and writes extra WAL, and indexed-column updates defeat HOT updates. It taxes RAM, because index pages compete with hot table pages in the buffer pool. It taxes the planner, which has more candidate paths to cost on every parse. Justify each index with a real query. On a write-heavy table, be suspicious beyond about five.

SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- drop candidates; re-check over a full business cycle first

MySQL equivalent: sys.schema_unused_indexes.

Dead weight shows up in a few forms: indexes on boolean or three-value columns (the planner scans the table anyway), duplicates under different names, and narrow indexes fully covered by a wider one. Random UUID primary keys also split pages across the whole tree, so prefer UUIDv7, ULID or a sequence. Build new indexes with CREATE INDEX CONCURRENTLY so writes are not blocked.

Most index failures are not missing indexes. They are queries written in a way that stops an existing index from being used, and the database does not warn you.

Two rules hold the method together. Fix the statistics before you add the index, and fix the query before you force the plan. Adding an index to compensate for stale statistics leaves you with a permanent write cost for a one-time problem. Pinning a plan with hints hides a query that is written in a form the planner cannot use. Work through the steps below in order and stop at the first one that solves the problem.

CREATE INDEX CONCURRENTLY idx_jobs_feed
  ON jobs (status, created_at)
  INCLUDE (title);
-- equality, then range or sort, then INCLUDE the payload

SELECT pg_size_pretty(pg_relation_size('idx_jobs_feed'));
-- weigh the size against your buffer pool

The index shape template, built without blocking writes.

After adding an index, run EXPLAIN ANALYZE again and compare timings and buffer reads. A new plan shape alone proves nothing. Also check that other queries on the same table did not regress.

Work through each question before you open the answer. They ask you to predict what a planner does or to spot what is wrong, not to repeat definitions.

• An index is an ordered, redundant structure; a B+tree serves equality, ranges and ORDER BY in about 3 to 4 page reads.
• Composite order is the biggest lever: equality columns first, then one range column, then sort columns, and (a, b, c) never helps a query on b or c alone.
• Covering indexes skip the heap: put payload in INCLUDE to keep the key narrow, and VACUUM so index-only scans really do skip it.
• EXPLAIN estimates and EXPLAIN ANALYZE measures; the gap between estimated and actual rows is where bad plans start.
• Seq scan wins on big result fractions and tiny tables, index scan on high selectivity and LIMIT, and bitmap heap scan owns the middle.
• Every index taxes writes, RAM and the planner, so drop whatever idx_scan = 0 shows nobody uses.
• Functions on the column, implicit casts, leading wildcards and OFFSET pagination silently disable indexes; fix statistics before adding an index, and fix the query before forcing a plan.