🔎Index scan vs seek
Navigate the tree vs walk the leaves
A seek uses the B-tree to jump to a start key and walks a narrow band, great for selective equality or range on leading keys. A scan walks a large portion of the leaf level, sometimes right for low selectivity or tiny tables, expensive when misapplied.
🧾Plan operators you will see
Names vary slightly by engine; ideas transfer.
| Operator | When you see it |
|---|---|
| Index Seek | Selective equality or range on a useful leading key; residual filters may still apply. |
| Index Scan | Non-leading predicates, low selectivity, or optimizer choice to read most of the index. |
| RID / Key Lookup | Index does not cover all projected columns, bridge back to the heap or clustered row. |
Selective seek pattern
SELECT *
FROM patients
WHERE mrn = 'MRN-0001201';| plan_operator | est_rows |
|---|---|
| Index Seek on PK | 1 |
Scans that look “indexed” but feel slow
- Wrapping columns in functions blocks sargable seeks.
- Implicit conversions between parameters and column types.
- Treating every “Index Scan” line as cheap without row estimates.
Seek-friendly predicates and types
- Compare estimated vs actual rows at each plan node after statistics refresh.
- Align parameter types with column definitions.
- Put
=and range predicates on leading index keys; alert on plan hash drift for golden queries.
Ops note
Sudden seek-to-scan flips after data skew or stats drift are a common incident trigger, capture plan text and parameters when debugging, not only the slow query fingerprint.
TL;DR
Index seek (lookup) uses the B-tree to jump to a key range, ideal for highly selective predicates.Index scan walks many leaf pages, still an index-only path if covering, but can be expensive when “almost everything” matches. SQL Server uses these terms explicitly; PostgreSQL reports similar ideas as index scans vs bitmap plans. Misleading cardinality estimates flip one into the other.