SQL.What is full scan / index scan ?

1️⃣ Full Scan (a.k.a. Sequential Scan)

Definition

A full scan reads the entire table from start to end, checking every row.

In PostgreSQL it’s called:

Seq Scan

Example

SELECT *
FROM orders
WHERE status = 'CANCELLED';

If:

• No index on status, or
• Optimizer thinks many rows match

Plan:

Seq Scan on orders
  Filter: status = 'CANCELLED'

Characteristics

✅ Pros

• Very simple
• Fast for small tables
• Efficient for large result sets
• Sequential I/O (disk-friendly)

❌ Cons

• Reads everything
• Wastes work if only few rows match

---

When optimizer chooses full scan

• Table is small
• Condition is not selective (returns many rows)
• No usable index
• Index would require many random reads
• Stale statistics make index look expensive

2️⃣ Index Scan

Definition

An index scan uses an index to locate matching rows and then fetches them from the table.

In PostgreSQL:

Index Scan

Example

CREATE INDEX idx_orders_status ON orders(status);

SELECT *
FROM orders
WHERE status = 'CANCELLED';

Plan:

Index Scan using idx_orders_status on orders
  Index Cond: status = 'CANCELLED'

How it works (simplified)

1. Traverse B-tree index
2. Find matching keys
3. Fetch corresponding rows from table (by TID)

Characteristics

✅ Pros

• Excellent for selective queries
• Reads only matching rows

❌ Cons

• Random I/O (can be expensive)
• Becomes slower when many rows match

3️⃣ Index-Only Scan (important senior detail)

Definition

Index-only scan reads data directly from the index without touching the table.

Plan:

Index Only Scan

Example

CREATE INDEX idx_orders_status_created
ON orders(status, created_at);

SELECT status, created_at
FROM orders
WHERE status = 'CANCELLED';

If:

• All columns are in index
• Visibility map allows it

Plan:

Index Only Scan

🔥 Fastest option.

4️⃣ Why optimizer sometimes prefers full scan over index scan

This is a very common interview trap.

Example

SELECT *
FROM orders
WHERE status IN ('NEW', 'PROCESSING', 'CANCELLED');

If this matches 70% of rows:

• Index scan:
  • Many random fetches
• Full scan:
  • Sequential read once

👉 Full scan wins.

Rule of thumb:

Index scan is good for few rows, full scan for many rows.

5️⃣ Full Scan vs Index Scan (comparison)

Aspect	Full Scan	Index Scan
Reads	All rows	Matching rows
I/O	Sequential	Random
Selectivity	Bad	Good
Small tables	Fine	Overkill
Large result sets	Good	Bad

6️⃣ What decides which one is chosen?

Optimizer uses:

• Table size
• Index availability
• Predicate selectivity
• Statistics (NDV, histograms)
• Cost of random vs sequential I/O
• LIMIT
• Correlation (index order vs physical order)

7️⃣ How joins relate to scans (important)

Nested Loop + Index Scan

Nested Loop
  -> Seq Scan users
  -> Index Scan orders(user_id)

Classic OLTP pattern.

Classic OLTP pattern.

Hash Join
  -> Seq Scan orders
  -> Seq Scan users

Classic analytics pattern.

8️⃣ Interview-ready answer (clean & short)

“A full scan reads the entire table and is efficient when many rows are needed or the table is small.
An index scan uses an index to locate matching rows and is efficient for selective queries.
The optimizer chooses between them based on statistics, selectivity, and I/O cost — sometimes a full scan is faster than using an index.”

9️⃣ Senior bonus insight ⭐

If someone says:

“Index scan is always faster”

🚩 That’s a red flag.

Correct mindset:

Indexes reduce search, not work.