Index Scan

Hook: Interviewers love this question because index scan is where simple SQL turns into real performance wins: one good access path can cut a query from seconds to milliseconds.

Question: What is an index scan in SQL?

Answer: An index scan is when the database uses an index to find matching rows instead of reading the whole table. The index is a smaller, sorted structure that points to the real rows, so the database can jump straight to likely matches. It is usually faster when only a small part of the table matches the filter.

Interview-Ready Answer: I would say an index scan is a query plan where the database walks an index first, then uses the row pointers in that index to fetch the matching table rows. It is most useful for selective predicates like a unique lookup or a narrow range, because the engine avoids scanning every row. One important detail is that a normal index scan may still need to visit the table storage unless the query can be answered from the index alone.

What an index scan really is

An index is a separate data structure, usually a B-tree, that keeps keys in sorted order. A scan here means the optimizer chose to read that structure first, rather than doing a full table read. In PostgreSQL terms, the index stores key values plus a tuple ID (a pointer to the row in the main table storage, often called the heap).

1. The optimizer estimates how many rows will match using table statistics collected by ANALYZE.
2. It compares the cost of an index scan with other plans, especially a sequential scan.
3. If the index looks cheaper, it starts at the B-tree root, then follows child pages until it reaches the leaf page with the target key range.
4. The leaf page returns tuple IDs for matching rows.
5. The database fetches the actual table rows and applies any remaining filters that the index did not already cover.
6. The final rows are returned to the query.

This is why an index scan is fast for small result sets: the engine does not read the entire table, only the part that the index says is relevant.

When it shines

Use an index scan when the predicate is selective, meaning it matches a small fraction of the table. Classic examples are primary-key lookups, foreign-key lookups, date ranges, and searches on a leading column of a composite index. It also pairs well with ORDER BY and LIMIT when the index order matches the sort, because the engine can stop early.

How it compares to other plans

Index-only scan means the database can answer the query from the index without visiting the table storage, but only if the needed columns are all in the index and the engine knows the rows are visible. In PostgreSQL, that visibility check depends on the visibility map, which tracks pages that are safe to read without extra heap checks.

Performance and complexity

For a B-tree index, finding the first matching leaf is roughly O(log n), because the tree is balanced. Fetching matching rows adds about O(k) for k matches. But the real cost is often dominated by I/O: many small random reads can be slower than one big sequential read. On modern SSDs, a random page read may still cost tens to hundreds of microseconds, while a sequential scan can stream data at a much higher rate. That is why a query that returns 100 rows from a 10 million row table is a great index scan candidate, but a query that returns 3 million rows may be faster as a sequential scan.

Index size also matters. A B-tree index can easily be a meaningful fraction of the table size, and every INSERT, UPDATE, or DELETE must keep it updated. So indexes speed reads, but they are not free.

Important edge cases

• If you wrap the column in a function, like LOWER(status) or DATE(created_at), the normal index on that column may not be usable. The fix is usually to rewrite the predicate as a range or create an expression index.
• A leading wildcard like LIKE '%abc' usually cannot use a normal B-tree index because there is no searchable prefix.
• For a composite index, the leftmost column matters. An index on (a, b) is much less helpful for a filter on only b.
• For tiny tables, a sequential scan may still win because the overhead of using the index is larger than just reading everything.

Memory rule: an index scan is a shortcut only when the shortcut is shorter than the trip through the whole table.

Real-World Story: Imagine a checkout service with an orders table behind the payment dashboard. Support agents search by order_id all day, so the team expects those lookups to be instant. One day a developer adds a report query like WHERE DATE(created_at) = CURRENT_DATE and assumes the existing index on created_at will help. In production, the query plan switches to a sequential scan, CPU spikes, and the dashboard p95 jumps from 120 ms to 4 seconds. Logs start showing statement timeouts, the connection pool fills up, and users see spinning loaders while agents cannot find recent orders.

The fix is simple but important: rewrite the filter as a range, such as created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL '1 day', or build an expression index if the function is truly needed. The lesson is that index scans are powerful, but only when the predicate matches the index shape.

Follow-up & Tricky Questions:

• How does the optimizer decide between an index scan and a sequential scan? It uses table statistics, predicate selectivity, and estimated I/O cost. If the filter matches many rows or the table is tiny, a sequential scan can be cheaper.
• What is the difference between an index scan and an index-only scan? An index scan still fetches table rows when needed, while an index-only scan can answer from the index alone if all requested columns are present and visibility rules allow it.
• Why can an index scan be slower on low-selectivity queries? Because it causes lots of random row fetches. If most of the table matches, reading the whole table once is often cheaper than bouncing around the storage.
• How does column order matter in a composite index? The leftmost column is the strongest anchor. An index on (a, b) helps most for filters on a, then a and b, and much less for b alone.
• When would a bitmap index scan appear? When the engine wants to combine many index hits and then fetch table rows in a more ordered way. It is common when the result set is not tiny but still not large enough for a full scan.
• Does ORDER BY always benefit from an index scan? No. It helps only when the index order matches the requested sort, or when the engine can stop early with LIMIT. Otherwise the database may still need to sort.
• Does adding an index always speed up reads? No. It helps selective reads, but it also adds storage and slows writes because every insert or update must maintain the index.
• Can LIKE '%abc' use a normal index? Usually not, because the leading wildcard removes the prefix the B-tree needs to navigate. A prefix search like LIKE 'abc%' is the one that typically benefits.

Common Mistakes:

• Assuming any index means faster queries. Correction: indexes help most when the filter is selective and matches the index shape.
• Wrapping indexed columns in functions without thinking. Correction: rewrite the predicate as a range or add an expression index.
• Ignoring composite index order. Correction: put the most useful leading column first and remember the leftmost prefix rule.
• Forgetting write overhead. Correction: every extra index makes INSERT, UPDATE, and DELETE a little slower.

Memory Hook: Think of an index scan like the index at the back of a book: you jump to the right page instead of reading every page in order.

Cheat Sheet:

• Index scan = use index first, then fetch matching rows.
• Best for small result sets and selective WHERE clauses.
• B-tree lookup is roughly O(log n), but random row fetches can dominate.
• Seq scan can win on small tables or when many rows match.
• Index-only scan is the fastest version when the query is covered.
• Functions and leading wildcards often block normal index use.

Practice Tasks:

• Create a table with 50,000 rows, add an index, and compare EXPLAIN for an equality filter versus no filter.
• Rewrite DATE(created_at) = ... into a range predicate and compare the plan again.
• Create a composite index on two columns and test how changing the column order changes the plan.