Indexes and Query Plans: What the Planner Is Actually Deciding

Composite column order, covering indexes, why an index gets ignored and how to read EXPLAIN — measured on one PostgreSQL 18 table in pages touched, including three rules of thumb the measurements contradict.

An index is a bet: you pay on every write, forever, to make some reads cheap. Most advice about indexes is about the reads. This part measures both sides on one table, and three things everyone repeats turn out to be wrong: that a composite index is useless unless the query uses its leading column, that OR prevents index use, and that a covering index avoids the table.

The table is 200,000 orders. Every number is block accesses from EXPLAIN (ANALYZE, BUFFERS) — pages the plan read or found in cache, counted each time they are touched, so a page visited twice counts twice. Write-ahead log bytes are quoted wherever something is written, because blocks exclude the log, and the log is where a write’s cost lands. A whole-table scan is 2,273 pages, so that’s the number everything competes against.

Try this first

You have a composite index on (customer_id, status). A query filters on status only — the second column, not the first.

Does the planner use the index? Write down yes or no, and what you think it costs.

Column order: the rule is real, and much weaker than you were told

The rule everyone learns is that a composite index serves queries that use its leading column, and is useless otherwise. Here are the same three queries against (customer_id, status), against (status, customer_id), and against no index at all:

Query filters on	customer_id, status	status, customer_id	no index
customer_id and status	3 (1 search)	4 (1 search)	2,273 (seq scan)
customer_id only	3 (1 search)	28 (5 searches)	2,273 (seq scan)
status only	261 (1 search)	6 (1 search)	2,273 (seq scan)

Block accesses, and what EXPLAIN reported as Index Searches. Each composite index is about 252 pages against the table’s 2,273. Customer 82 has 10 orders, 10 of them refunded; 2,000 of the 200,000 orders are refunded.

Three things in that table.

The leading column matters. Filtering on customer_id alone costs 3 blocks when it leads and 28 when it trails; filtering on status alone costs 6 when it leads and 261 when it trails.

The “wrong” order is not useless. Querying status alone against (customer_id, status) cost 261 blocks against an index of 252 pages — it read essentially the whole index, once, top to bottom. (Index Searches: 1 only tells you it descended once; it’s the block count against the index size that shows it then walked the lot.) That is still nearly nine times better than the 2,273-page table scan, because the index is far narrower than the table. An index in the wrong order is a smaller table, not a wasted one.

And PostgreSQL 18 can skip. Querying customer_id alone against (status, customer_id) cost 28 blocks, and EXPLAIN reported Index Searches: 5. The leading column status has four distinct values, so instead of reading the index end to end the executor searches once per value and repositions itself between them — the searches beyond four are those repositioning probes. That’s a skip scan, new in PostgreSQL 18, and it means a low-cardinality leading column is no longer fatal.

It is worth reading what the manual actually says, because the version everyone repeats isn’t it. The documented rule is about equality against inequality, not about selectivity: “equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will always be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they’ll always save visits to the table proper, but they do not necessarily reduce the portion of the index that has to be scanned.”

That last clause is exactly our 261-page result: the constraint was checked in the index, which saved every visit to the table, but it didn’t narrow how much index had to be read.

“Put the most selective column first” appears nowhere in the PostgreSQL 18 documentation. Lead with the column you use with equality. And skip scan is an outright counter-example to the selectivity version, since it works better when the leading column has few distinct values — the manual’s own condition is that “there are so few distinct x values that the planner expects the scan to skip over most of the index”.

One more piece of documented advice, easy to miss: “Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.”

Covering indexes, and the thing that spoils them

If an index holds every column a query needs, the query never has to visit the table. PostgreSQL calls that an index-only scan, and it is the cheapest read there is.

Index	Index pages	Blocks	Plan
on customer_id alone	232	12	Bitmap Heap Scan, Bitmap Index Scan
on (customer_id, total)	773	4	Index Only Scan
on customer_id INCLUDE (total)	773	4	Index Only Scan

The composite index and the INCLUDE index cost the same 4 blocks — and, worth noting because the usual pitch for INCLUDE is that it saves space, they are exactly the same size: 773 pages each, against 232 for the bare index. Carrying a payload column costs what carrying it costs.

What INCLUDE actually buys is put more carefully by the manual. Non-key columns “duplicate data from the index’s table and bloat the size of the index, thus potentially slowing searches”, but “Suffix truncation always removes non-key columns from upper B-Tree levels”, so the upper levels stay small. The column also can’t be used for searching or ordering. That’s the real trade, and it isn’t “smaller”.

Now the catch, and it is the part that gets left out of every “add a covering index” tip. An index doesn’t record which rows are visible to your transaction: “Visibility information is not stored in index entries, only in heap entries; so at first glance it would seem that every row retrieval would require a heap access anyway. And this is indeed the case, if the table row has been modified recently.”

What saves it is the visibility map, a bitmap of which heap pages hold only rows visible to everyone. The scan checks that first: “If it’s set, the row is known visible and so the data can be returned with no further work. If it’s not set, the heap entry must be visited to find out whether it’s visible, so no performance advantage is gained over a standard index scan.”

And here is the sentence that decides whether your covering index works on a busy table: “Visibility map bits are only set by vacuum, but are cleared by any data-modifying operations on a page.”

Which means an index-only scan is only as good as your last VACUUM:

State of the visibility map	Blocks	Heap fetches
after one update to those rows, no VACUUM	5	10
after VACUUM	4	0

One update to the rows that query reads — customer 82’s 10 orders, against the INCLUDE index — and the “index-only” scan performed 10 heap fetches. A VACUUM put it back to zero.

Don’t read too much into the size of that penalty: those 10 fetches cost exactly one extra block, because ten new row versions happened to land on one heap page. On a real table the updated rows are scattered and it is closer to a page per fetch. The mechanism is the lesson, not this magnitude.

The manual’s own summary is suitably hedged: an index-only scan “will be a win only if a significant fraction of the table’s heap pages have their all-visible map bits set”. So Index Only Scan in a plan is not a promise of zero table I/O — Heap Fetches on the same line is the number that tells you.

Advice doesn’t travel between engines here either. SQLite’s documentation says a covering index “can make many queries run twice as fast”, and for SQLite that’s right: it has no MVCC visibility map to consult. Carry the expectation to PostgreSQL and a hot table will disappoint you.

When the planner stops using your index

The rule of thumb is that PostgreSQL abandons an index once a query matches more than a few percent of the table. That one isn’t internet folklore — it’s in the manual, in the partial-index chapter: “a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway”.

Let’s widen a predicate and watch.

Rows matched	Answered from the index alone	Needing the table too
1.2%	11, Index Only Scan	262, Bitmap Heap Scan
4.6%	29, Index Only Scan	351, Bitmap Heap Scan
10.1%	61, Index Only Scan	515, Bitmap Heap Scan
21.3%	122, Index Only Scan	828, Bitmap Heap Scan
32.4%	184, Index Only Scan	1,141, Bitmap Heap Scan
54.6%	440, Index Only Scan	2,273, Seq Scan, forced: still Seq Scan
76.8%	628, Index Only Scan	2,273, Seq Scan, forced: still Seq Scan
100%	2,273, Seq Scan	2,273, Seq Scan, forced: still Seq Scan

Block accesses. The table is 2,273 pages.

So the manual’s own rule of thumb is badly wrong in one case and roughly right in the other, and the difference explains the whole thing.

The query that can be answered from the index alone stayed on the index at 76.8% of the table. Reading three-quarters of a 252-page index beats reading a 2,273-page table, so of course it did.

The query that needs a column the index doesn’t hold switched to a sequential scan between 32.4% and 54.6%. Notice it used a Bitmap Heap Scan all the way up to that point — so this is not the old story about random I/O order. A bitmap scan collects the row locations first and visits the heap in physical order; the manual notes the original index ordering “is lost”. What actually kills it is simpler: the matching rows are spread everywhere, so by the time you want a third of them you are visiting nearly every page of the table and reading index pages on top.

So the real rule is not a percentage of rows. It’s whether the plan ends up visiting most of the pages anyway. Same lesson as the trigram index in Part 17, arriving from the other direction.

And a warning about the obvious experiment. We tried to price the alternative by turning enable_seqscan off at the two points where the planner had given up — and it went on choosing a sequential scan, at exactly the same 2,273 blocks. That is the documented behaviour, not a bug: “It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available.” So this part cannot tell you what the index plan would have cost there, and neither can anyone who runs that experiment without checking which plan came back.

Four queries an index can’t help, and what to do

The query	Blocks	What the planner did	Why
a function on the column	2,273 → 4 after the fix	Seq Scan	the index holds email, not lower(email), so the planner cannot match them
a leading wildcard	2,273 → 4 after the fix	Seq Scan	a B-tree is ordered left to right, so it cannot anchor a trailing match
arithmetic on the column	1,102 → 3 after the fix	Index Only Scan	the expression is not the indexed value; take the arithmetic off the column
an OR whose sides are both selective	7 → no fix applied	Bitmap Heap Scan, Bitmap Index Scan (2 searches)	nothing to fix: OR does not prevent index use, and the planner combines two indexes with a BitmapOr
an OR with one unselective side	2,005 → no fix applied	Bitmap Heap Scan, Bitmap Index Scan (2 searches)	the planner used both indexes and a BitmapOr, and it still cost nearly the whole table: the unselective side dominates the plan
the unselective side on its own	2,003 → no fix applied	Bitmap Heap Scan, Bitmap Index Scan	one percent of the rows, spread over nearly every page: the index is used, and buys almost nothing against a whole-table scan

A function on the column. The index holds email; the query asks about lower(email). The planner can’t match those, so it scans. An index on the expression itself — create index on orders (lower(email)) — takes the same query from 2,273 blocks to 4.

A leading wildcard. email LIKE '%r4242@example.com' can’t use a B-tree, because a B-tree is ordered from the left and the query doesn’t know the left. If you genuinely need suffix matching, index the reversed string and reverse the pattern too: 2,273 blocks down to 4. (Part 17’s trigram index is the other answer, with its own conditions.)

Arithmetic on the column, and this one is subtler than it looks. customer_id + 0 = 4242 did not make the planner abandon the index — EXPLAIN says Index Only Scan. But there is no Index Cond, so the expression was applied as a filter to every entry in turn: the scan read the whole index, 1,102 blocks, and still made 870 heap fetches on the way. Taking the arithmetic off the column brings it to 3. This is the clearest example in the post of the covering section’s warning: an Index Only Scan line is not by itself good news.

OR — the one that’s a myth. “OR prevents index use” is repeated everywhere, and the lab doesn’t reproduce it. Two selective conditions joined by OR cost 7 blocks, and the plan contains a BitmapOr node over both orders_customer and orders_email.

What is expensive is an OR with an unselective side — 2,005 blocks. But look at the last row of the table: that side on its own costs 2,003. The OR added two blocks. And note what the planner did in both cases — it used the indexes, with a BitmapOr, and still ended up touching nearly the whole table, because one percent of the rows scattered across every page is not something an index can rescue.

So OR was never the problem. Selectivity was, and the habit worth forming is to measure each side alone before blaming the syntax.

The folklore does have an origin, and it is worth being precise about it. MySQL’s manual gives last_name = 'Jones' OR first_name = 'John' as a query a composite index on (last_name, first_name) cannot serve — which is its leftmost-prefix rule, not a claim about OR as such, and MySQL documents Index Merge for the separate-indexes case. The rule of thumb travelled between engines more cleanly than the behaviour did.

Reading EXPLAIN: the two things to look at first

EXPLAIN output is dense, but for most debugging you need two things from it — one of which isn’t a number.

One PostgreSQL 18 change worth knowing before you read any older advice: BUFFERS is now on by default with EXPLAIN ANALYZE, so the page counts appear without asking for them.

What the plan actually did — the node types. Seq Scan means it read the table. Index Scan means it walked the index and fetched rows. Index Only Scan means it answered from the index. Bitmap Heap Scan means it collected row locations first and then read the heap in physical order — which is what the planner picks when there are too many rows for one-at-a-time fetches but too few for a full scan. The index’s own ordering is lost in the process, which is why a bitmap scan can’t satisfy an ORDER BY for free.

Estimated rows against actual rows. This is the number that tells you whether to trust anything else. The planner costs plans using its estimate; if the estimate is wrong, the plan is a guess.

Here is the classic way for it to be wrong. Two columns that look independent to the planner but aren’t:

Query	Planner’s estimate	Actual rows
independent columns	20	20
correlated columns	12,734	50,000
independent columns (after CREATE STATISTICS)	19	20
correlated columns (after CREATE STATISTICS)	49,780	50,000

For the independent pair the planner is exact. For the correlated pair — country = 'IN' and city = 'Chennai', where every Chennai order is an Indian order — it predicted 12,734 rows and found 50,000. It multiplied the two selectivities as if they were independent: a quarter of a quarter is a sixteenth, and a sixteenth of 200,000 is 12,500.

The fix is to tell it they’re related:

create statistics orders_country_city (dependencies, ndistinct)
  on country, city from orders;
analyze orders;

After which the estimate was 49,780 against an actual 50,000. A 4-times misestimate became a rounding error, and any plan built on it gets better.

What the index costs the writer

Every index has to be maintained by every write that touches it. This is the side of the bet that people skip, so here it is in the currency that matters — write-ahead log bytes for 10,000 inserts and 10,000 updates:

Extra indexes	Insert	Update	All indexes on disk
0	1.88 MB	2.59 MB	5.2 MB
1	2.54 MB	3.17 MB	9.2 MB
3	3.99 MB	4.64 MB	16.4 MB
6	6.18 MB	6.72 MB	35.2 MB

Write-ahead log for 10,000 inserts and 10,000 updates. “Extra” is on top of the primary key, which this table always has — which is why the first row already carries 5.2 MB of index. The table itself is 18.7 MB.

Six extra indexes cost 3.3 times the log on insert and 2.6 times on update, plus 35 MB of index that has to be backed up, replicated and vacuumed forever — against a 19 MB table.

Read the baseline row honestly, though: “0 extra indexes” is not “no indexes”. The table has a primary key, which is a unique B-tree over 200,000 integers, and it is already 5.2 MB of the cost. The ratios above are seven indexes against one, not six against none.

One more caveat on the update column: the statement updates status, which is itself indexed in the three- and six-index rows. So those numbers already include the penalty from Part 16 — an update to an indexed column can’t be a heap-only tuple, so it writes index entries as well as a new row version. That is a real cost, but it is inside these figures rather than on top of them.

Explain it like I’m ten

A recipe book with an index at the back.

• Looking up “lemon” in the index is quick: the index is alphabetical, so you go straight there. That’s an index scan.
• The index is sorted by first word. If you want every recipe with “lemon” as the second word, the alphabet doesn’t help — you have to read the whole index. Still faster than reading every recipe, because the index is a few pages and the book is hundreds. That’s the wrong column order.
• Sometimes the index tells you everything — if you only wanted to know how many lemon recipes there are, you never open the book. That’s an index-only scan.
• If half the book is lemon recipes, it depends what you asked. If you only want to know how many, the index still wins — ours was still winning at 76.8% of the table. If you have to open the book at every entry, half is too many, and reading it straight through is quicker. That’s the crossover.
• Every new recipe means updating every index at the back. That’s the write cost.

The precise version

• The index is a sorted copy of some columns, plus a pointer to where the row lives.
• “Second word” is a non-leading column, and reading the whole index is a full index scan. When the first word has only a few possible values, you can check each in turn instead: a skip scan.
• Answering without opening the book is an index-only scan, and it works only when the visibility map says the pages are clean.
• Where the analogy breaks: a recipe book’s index is built once. A database’s is maintained on every write, which is the cost this part keeps returning to.

Trade-offs

• Every index is a permanent write tax for a temporary read benefit. Measured: six indexes, 3.3× the log on insert. The reads have to be worth that every day.
• Column order matters less than the internet thinks, but it isn’t free: 3 pages against 261 on our data. Order by what you filter on most.
• A covering index is the cheapest read and the most conditional. It depends on the visibility map, which depends on vacuum keeping up — and it does not make the index smaller.
• A partial index is often the better answer than a wider one: if you only ever query one status, index only those rows and pay nothing for the rest.
• Unused indexes are pure cost. pg_stat_user_indexes shows which ones have never been scanned. Look before you add another.
• The planner’s estimate is the foundation of everything. If it’s wrong, tune the statistics, not the query.

Common mistakes

• Adding an index without checking whether one already serves the query. A composite index serves its leading column too; you may already have it.
• Believing “OR prevents index use”. Our lab: two selective sides, 7 pages, both indexes used.
• Believing the “a few percent” rule, which is in the manual but is about the heap-visiting case. An index-only scan was still winning at 76.8% of the table.
• Wrapping the indexed column in a function and then wondering why the index is idle. Index the expression, or move the function off the column.
• Treating an index-only scan as guaranteed. One update to the rows in question, and it visited the heap ten times.
• Reading EXPLAIN without ANALYZE and believing the row counts. Those are predictions. ANALYZE runs the query and reports what actually happened.
• Indexing a column you update constantly. You pay on the index and you lose heap-only updates.
• Adding an index to fix a plan whose estimate is wrong. Fix the estimate: ANALYZE, a bigger statistics target, or CREATE STATISTICS for correlated columns.

Interview questions

Try to answer each one before opening the model answer.

1. How do you decide the column order in a composite index?

Show a strong answer

• Lead with the column your queries filter on most often, especially with equality, because that’s the one a lookup can jump straight to.
• Equality before range: (status, placed_at) serves status = ? and placed_at > ? well; the reverse serves it badly.
• It is not a cliff. Our lab: the trailing-column query cost 261 pages against 3 for the leading one — but the table scan was 2,273, so even the “wrong” index won.
• And PostgreSQL 18 skip-scans a low-cardinality leading column, which made the wrong order cost 38 pages instead of a full scan.
• Check what you already have before adding another index: one composite index can serve several query shapes.

Likely follow-up: “When would you add a second index instead of reordering?” When both shapes are hot and the table is read-heavy enough to pay the extra write cost — measure it rather than assume.

2. What is a covering index, and when does it stop covering?

Show a strong answer

• It holds every column the query needs, so the query is answered from the index alone: an index-only scan.
• INCLUDE adds payload columns without adding them to the sort order, so the index stays smaller and those columns can’t be searched or ordered on.
• It stops covering when the visibility map is stale. The index doesn’t know what’s visible; PostgreSQL checks the map, and an unmarked page sends the scan to the table.
• Measured: after one update to the rows in question, our index-only scan did ten heap fetches. After VACUUM, zero.
• So the tuning knob is often vacuum, not the index.

Likely follow-up: “How would you spot this in production?” Heap Fetches in EXPLAIN (ANALYZE), and dead-tuple counts in pg_stat_user_tables.

3. Why might the planner ignore an index you just created?

Show a strong answer

• Because it costs the alternatives and picks the cheapest — so “ignored” often means “correctly rejected”.
• The query shape may not match the index: a function or arithmetic on the column, a leading wildcard, or the wrong operator class for the collation.
• Or the query matches too much of the table, in which case a scan really is cheaper. What matters is pages visited, not the row percentage.
• Or the statistics are stale and the planner’s estimate is wrong; ANALYZE first.
• Check by reading the plan, and if you try set enable_seqscan = off to price the alternative, check which plan actually came back. The manual is explicit: “It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available.” In our lab it went on choosing a sequential scan.

Likely follow-up: “It’s still not used after ANALYZE — now what?” Compare estimated against actual rows. If they disagree badly, that’s a statistics problem, possibly correlated columns needing CREATE STATISTICS.

4. Walk me through reading an EXPLAIN plan.

Show a strong answer

• Read inside out: the innermost nodes run first, and each node’s cost includes its children.
• Start with the scan nodes: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan — that tells you how the rows were found.
• Then compare estimated rows with actual rows. A big gap is the root cause of most bad plans, and it points at statistics rather than the query.
• Use ANALYZE for reality and BUFFERS for cost, because block accesses are comparable across machines in a way that milliseconds aren’t — and on PostgreSQL 18 BUFFERS comes for free with ANALYZE. Remember ANALYZE actually executes the statement — wrap writes in a transaction you roll back.
• Watch loops: a node showing 3 ms with 10,000 loops is 30 seconds.

Likely follow-up: “What do the cost numbers mean?” They’re in arbitrary units for comparing plans, not a prediction of milliseconds.

5. What does an index cost?

Show a strong answer

• Write amplification on every insert and delete, which touch every index, and on any update that changes an indexed column. Measured: six extra indexes tripled the write-ahead log on insert.
• Disk, backups and replication: our six extra indexes brought the table’s indexes to 35 MB against a 19 MB table, and all of it gets copied everywhere the data goes.
• Lost heap-only updates: changing an indexed column forces an index write and a new row version, as Part 16 measured.
• Vacuum work, because indexes need their dead entries cleaned up too.
• And planning time, mildly, since every index is another option to cost.

Likely follow-up: “How do you find indexes that aren’t earning it?” pg_stat_user_indexes.idx_scan — zero after a full business cycle is a strong hint, but check the replica too before dropping.

6. Your query got slow after the table grew. How do you investigate?

Show a strong answer

• Get the plan first, with EXPLAIN (ANALYZE, BUFFERS), and compare it to what you expected.
• Look for a plan flip: an index scan that became a sequential scan usually means the estimate crossed a threshold — sometimes correctly.
• Check estimated against actual. Growth often invalidates statistics, especially on a column whose distribution changed.
• Check for bloat and the visibility map if an index-only scan started fetching from the heap.
• Then, and only then, consider an index — and consider a partial one if the hot query only touches a slice of the table.

Likely follow-up: “How would you tell a plan flip from general slowness?” Pages touched. If the plan is the same and the pages went up proportionally with the data, it’s growth; if the pages jumped, it’s the plan.

7. When is a partial index the right tool?

Show a strong answer

• When your queries only ever look at a slice — unprocessed jobs, open tickets, one tenant’s rows.
• It’s smaller, so it’s cheaper to scan, cheaper to cache and cheaper to maintain.
• And rows outside the predicate cost nothing: inserts that don’t match the condition don’t touch it at all.
• The catch is that the planner must prove your query implies the index’s predicate, so the condition has to be written in a way it can match.
• Classic shape: create index on jobs (created_at) where status = 'pending' on a table where pending is a tiny fraction.

Likely follow-up: “What if the slice grows?” The index grows with it, and you lose the advantage — it’s worth checking the assumption periodically.

8. You inherit a table with fifteen indexes. What do you do?

Show a strong answer

• Measure before removing anything: pg_stat_user_indexes for scan counts, over a full business cycle including monthly jobs.
• Check the replicas, because read traffic may live there and the counters are per-node.
• Look for redundancy: an index on (a) is redundant with (a, b) for most purposes, and duplicates by different names are common.
• Price the write side so the argument is concrete: each index is log bytes on every write, plus disk everywhere the data is replicated.
• Drop in stages, with a way back — and note that dropping and recreating a large index is not instant.

Likely follow-up: “How do you drop one safely?” Mark it invalid or drop it concurrently in a low-traffic window, keep the DDL to recreate it, and watch the plans for the queries you believed it served.

Sources

• Lab: system-design/checks/part18_indexes/postgres.py — one 200,000-row table in PostgreSQL 18 in a container, measuring composite column order on three query shapes, covering indexes and the visibility map, the selectivity crossover for index-only and heap-visiting queries, four query shapes an index cannot serve, planner estimates with and without extended statistics, and the write cost of nought to six indexes
• PostgreSQL 18: index types, multicolumn indexes, index-only scans and covering indexes, partial indexes, indexes on expressions, combining multiple indexes, examining index usage, using EXPLAIN, planner method configuration, statistics used by the planner, extended statistics, the visibility map, heap-only tuples, release notes for 18
• MySQL 8.4: how MySQL uses indexes, EXPLAIN output format; SQLite: the query optimizer overview

What to remember

• An index is a permanent write cost for a conditional read benefit. Price both sides.
• Column order matters, but the wrong order is a smaller table rather than a wasted index — and PostgreSQL 18 may skip-scan it.
• An index-only scan is the cheapest read and depends on the visibility map, so it depends on vacuum.
• The planner abandons an index based on the pages it would end up visiting, not on a percentage of rows.
• OR doesn’t prevent index use. Selectivity does.
• Estimated against actual rows is the first thing to read in a plan; a big gap is a statistics problem.
• Correlated columns break the independence assumption, and CREATE STATISTICS fixes it.

An index doesn’t make a database fast. It makes one question fast, and every write a little slower. Know which question you bought.