Index Scan vs Index Only Scan

Index Scan

An Index Scan is a method used by a database to retrieve data from a table by first looking up values in an index and then using those index entries to locate and retrieve the corresponding rows from the table itself.

Process:

The database traverses the index to find entries that match the query’s conditions.
For each matching index entry, the database obtains a pointer (e.g., a row ID or RID) to the actual row in the table.
The database uses this pointer to access the table heap and retrieve the required columns for that row.

When it’s used:

When the query needs to select columns that are not part of the index.
The index helps narrow down the rows to be fetched, but the table access is still necessary to get all the requested data.

Characteristics:

Involves two main I/O operations: one to read the index and another to read the table data.
Can be slower than an Index Only Scan if many rows need to be fetched from the table, as this involves random I/O to the table heap.

Index Only Scan

An Index Only Scan is a more efficient data retrieval method where the database can satisfy the entire query (i.e., retrieve all requested columns) directly from an index without accessing the table itself.

Process:

The database traverses the index to find entries that match the query’s conditions.
All the columns required by the query are present within the index itself.
The database retrieves the data directly from the index, skipping the table heap for pages marked all-visible.
PostgreSQL caveat: the index has no visibility (MVCC) info, so for each row the planner checks the visibility map; if the row’s heap page is not all-visible (recently modified, not yet VACUUMed) it still fetches the heap tuple (shown as Heap Fetches in EXPLAIN ANALYZE). Running VACUUM keeps the visibility map up to date.

When it’s used:

When all columns selected in the query are part of the index (e.g., in a covering index).
For queries like COUNT(*) where the index can provide the count without needing to access the table.

Characteristics:

Involves only one main I/O operation: reading the index.
Significantly faster than an Index Scan because it avoids most of the I/O cost of accessing the table heap (all of it only when the pages are all-visible).
Reduces I/O and can improve query performance, especially for queries that select a small number of columns that are all covered by an index.

Key Differences Summarized

Feature	Index Scan	Index Only Scan
Table Access	Yes, accesses table heap after index lookup.	Only for heap pages not all-visible in the visibility map (Postgres); otherwise none.
Columns	Can retrieve any column from the table.	Can only retrieve columns present in the index.
I/O Operations	Reads index + Reads table data.	Reads index (+ visibility map, + heap for non-all-visible pages).
Performance	Generally slower due to table access.	Generally faster as it avoids table access.
Use Case	Query selects columns not in the index.	All selected columns are in the index (covering index).

When to Optimize for Index Only Scans

Identify frequently executed queries that select a limited set of columns.
Consider creating covering indexes that include all the columns needed by these queries in the SELECT, WHERE, ORDER BY, and GROUP BY clauses.
Be mindful of the trade-offs: adding too many columns to an index can increase its size, potentially slowing down write operations (INSERT, UPDATE, DELETE) and consuming more storage.
Regularly analyze query performance and index usage to ensure that the indexes are still beneficial as the data and query patterns evolve.

Additional Notes on Non-Key Columns

Non-key columns (also known as included columns or non-key attributes) are columns that are part of a non-clustered index but are not part of the index key itself. Their primary purpose is to support Index Only Scans.

Benefits in the context of Index Scans vs. Index Only Scans:

Facilitating Index Only Scans:
- By including non-key columns in an index, you can create “covering indexes.” If a query requests columns that are all present either as key columns or as included non-key columns in the index, the database can perform an Index Only Scan.
- This avoids the need to access the table heap (as in a regular Index Scan), significantly improving query performance by reducing I/O.
Selectivity vs. Coverage:
- Index Key Columns: These are used for searching and sorting data within the index. The selectivity of these columns (how many unique values they have) is crucial for the efficiency of index seeks.
- Non-Key (Included) Columns: These are not used for searching or sorting within the index structure itself. They are simply stored at the leaf level of the index alongside the key values. Their presence allows the query to be “covered” by the index.
Impact on Index Size and Maintenance:
- Adding non-key columns increases the overall size of the index. Larger indexes consume more disk space and can potentially slow down Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE) because the index also needs to be updated.
- It’s a trade-off: you improve read performance for specific queries at the cost of storage and potentially write performance.
When to Use Included Columns:
- When you have queries that frequently select specific columns that are not part of the primary search criteria (i.e., not ideal as key columns).
- To avoid making the index key too wide. Wide index keys can reduce the effectiveness of the B-tree structure. Including columns as non-key attributes is often a better alternative than adding them to the index key if they are not used for filtering or sorting.
- For columns with data types that are not suitable for index keys (e.g., large text or binary objects), though it’s generally better to avoid including very large columns even as non-key columns if possible.

Example Scenario:

Consider a table Employees with columns EmployeeID (PK), DepartmentID, Salary, HireDate, and Email.

If you frequently run a query like:

SELECT EmployeeID, Email
FROM Employees
WHERE DepartmentID = 5;

An index on (DepartmentID) would lead to an Index Scan:
1. Index seek on DepartmentID.
2. For each match, a key lookup (RID lookup) into the Employees table to fetch the Email.
An index on (DepartmentID) with Email as an included non-key column (e.g., CREATE INDEX IX_Dept_Email ON Employees (DepartmentID) INCLUDE (Email);) would lead to an Index Only Scan:
1. Index seek on DepartmentID.
2. The Email is available directly from the leaf level of the index. No table access is needed.

By carefully choosing which non-key columns to include, you can significantly boost the performance of targeted queries by enabling Index Only Scans.

Database-Specific Examples

Example Table Schema

Let’s use the following example table schema for demonstrations:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL Examples

Creating Indexes in PostgreSQL

-- Create a regular index (will result in Index Scan for most queries)
CREATE INDEX idx_category ON products(category_id);
 
-- Create a covering index to enable Index Only Scans
CREATE INDEX idx_category_covering ON products(category_id) INCLUDE (product_name, price);

Analyzing PostgreSQL Execution Plans

You can use the EXPLAIN ANALYZE command to see which type of scan PostgreSQL chooses:

-- This query will likely use an Index Scan because description is not in the index
EXPLAIN ANALYZE
SELECT product_name, description, price
FROM products
WHERE category_id = 5;
 
-- This query can use an Index Only Scan because all selected columns are in the covering index
EXPLAIN ANALYZE
SELECT product_name, price
FROM products
WHERE category_id = 5;

Expected output showing an Index Only Scan:

Index Only Scan using idx_category_covering on products
  Index Cond: (category_id = 5)
  Heap Fetches: 0  -- No heap fetches: all pages were all-visible (a high number means VACUUM is needed)

Controlling PostgreSQL Scan Behavior

PostgreSQL allows you to control the query planner behavior:

-- Disable Index Only Scans to force Index Scans
SET enable_indexonlyscan = off;
EXPLAIN ANALYZE
SELECT product_name, price
FROM products
WHERE category_id = 5;
 
-- Re-enable Index Only Scans
SET enable_indexonlyscan = on;

MySQL Examples

Creating Indexes in MySQL

-- Create a regular index (will result in Index Scan for most queries)
CREATE INDEX idx_category ON products(category_id);
 
-- Create a covering index to enable Index Only Scan capabilities
CREATE INDEX idx_category_name_price ON products(category_id, product_name, price);

Analyzing MySQL Execution Plans

In MySQL, you can use EXPLAIN to view the execution plan:

-- This query will likely use an Index Scan (ref in MySQL terminology)
-- because it needs to access the table for the description column
EXPLAIN
SELECT product_name, description, price
FROM products
WHERE category_id = 5;
 
-- This query can be satisfied using only the index (covering index)
EXPLAIN
SELECT product_name, price
FROM products
WHERE category_id = 5;

Expected output for Index Only Scan in MySQL:

+----+-------------+----------+------------+------+---------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_category  | idx_category_name_price | 4     | const |   10 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-----------------------+---------+-------+------+----------+-------------+

The Using index in the Extra column indicates that MySQL can satisfy the query using only the index (an Index Only Scan).

Controlling MySQL Scan Behavior

MySQL allows some control over optimizer behavior:

-- Use index hints to force the use of a specific index
SELECT product_name, price
FROM products FORCE INDEX (idx_category_name_price)
WHERE category_id = 5;
 
-- Ignore an index to prevent its use
SELECT product_name, price
FROM products IGNORE INDEX (idx_category_name_price)
WHERE category_id = 5;

Performance Comparison

When working with large tables:

Index Scan: May require thousands of random I/O operations to fetch rows from the table, especially when retrieving many rows.
Index Only Scan: Might be 2-10 times faster depending on the scenario, as it avoids these random I/O operations by reading only from the index structure.

Understanding these scan methods and designing your indexes accordingly can dramatically improve the performance of your database queries in production environments.

Kaushik's Notes

Explorer