Database.Middle.How does an index-only scan work?

An index-only scan is a performance optimization used by relational databases (like PostgreSQL, MySQL, Oracle) where a query is satisfied entirely by reading an index, without accessing the table (heap) at all.

---

✅ What Is an Index-Only Scan?

In a normal index scan, the database:

1. Uses the index to find the row locations (row pointers).
2. Then fetches the full row data from the table (called the heap or base table).

In an index-only scan, the database:

• Retrieves all needed columns directly from the index.
• Skips the table (heap) entirely — saving I/O and improving speed.

---

🧠 Requirements for Index-Only Scan

1. All selected columns must exist in the index.
2. The table must have a visibility map (PostgreSQL) or equivalent mechanism to know that rows are visible (not deleted or uncommitted).
3. No need for SELECT * (unless all columns are in the index).

---

🔍 Example

Suppose we have this table and index:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

CREATE INDEX idx_users_email ON users (email);

Query:

SELECT email FROM users WHERE email LIKE 'john%';

✅ The index idx_users_email includes the column email, which is both:

• The filter condition
• The output column

→ So this query can be served by an index-only scan.

📈 Why It’s Faster

Operation	Index Scan	Index-Only Scan
Read index	✅	✅
Access table rows	✅ (extra disk I/O)	❌
Performance	Medium	Faster (less I/O)

🔧 How to Enable or Encourage Index-Only Scan

• Create covering indexes: indexes that include all needed columns

CREATE INDEX idx_orders_covering ON orders (customer_id, order_date, total_amount);

• Avoid SELECT * — only query columns that exist in the index.
• Keep the visibility map up-to-date (PostgreSQL):
  • Regular VACUUM helps maintain visibility maps.
• Use EXPLAIN to confirm: sqlCopyEditEXPLAIN SELECT customer_id, order_date FROM orders WHERE order_date > '2024-01-01';

---

❌ Limitations

• Won’t work if:
  • Not all columns are in the index
  • Visibility map says some rows may be outdated (PostgreSQL only)
• Index-only scans may not always be used, even when possible — the query planner chooses the cheapest plan.

---

🔍 Summary

Feature	Index Scan	Index-Only Scan
Reads from table (heap)?	✅	❌
Needs all columns in index	❌ (some ok)	✅
Performance	Moderate	High
Use case	General-purpose	Read-heavy, optimized