PostgreSQL Index Scans vs Index Only Scans

Understanding the difference between Index Scans and Index Only Scans is crucial for PostgreSQL query optimization. Let's explore both types and learn when to use each one.

Index Scan

An Index Scan retrieves rows from an index and uses it to locate the corresponding rows in the table. This operation requires both index access and table access (heap fetch).

Use Case

Index Scans occur when the query requires data that isn't fully available in the index itself, necessitating a lookup in the underlying table.

Example

-- Create a table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC,
    department TEXT
);

-- Insert sample data
INSERT INTO employees (name, salary, department)
SELECT 'Employee ' || i, 50000 + i * 100, 'Dept' || (i % 5)
FROM generate_series(1, 1000) AS i;

-- Create an index
CREATE INDEX idx_employees_salary ON employees(salary);

-- Query that triggers an Index Scan
EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary = 51000;

In this example, PostgreSQL uses the index on salary to find matching rows, then fetches the complete rows from the table because we're selecting all columns (SELECT *).

Index Only Scan

An Index Only Scan is a more efficient operation that retrieves all required data directly from the index without consulting the underlying table.

Use Case

Index Only Scans happen when:

• The index contains all columns required by the query
• The visibility map indicates no recent updates/deletes for the relevant rows

Example

-- Query that triggers an Index Only Scan
EXPLAIN ANALYZE
SELECT salary FROM employees WHERE salary = 51000;

This query can be satisfied entirely from the index because we're only selecting the salary column, which is part of the index.

Key Differences

Practical Comparison

Index Scan Example Output

EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary > 50000;

-- Output:
Index Scan using idx_employees_salary on employees
  (cost=0.29..8.57 rows=10 width=72)
  Heap Blocks: exact=10

Index Only Scan Example Output

EXPLAIN ANALYZE
SELECT salary FROM employees WHERE salary > 50000;

-- Output:
Index Only Scan using idx_employees_salary on employees
  (cost=0.14..3.58 rows=10 width=8)
  Heap Fetches: 0

Optimizing for Index Only Scans

To maximize the use of Index Only Scans:

1. Create covering indexes that include all columns needed by your queries:

CREATE INDEX ON table (column1, column2);

2. Maintain your tables with regular VACUUM to keep the visibility map current

3. Structure queries to request only indexed columns when possible

Conclusion

Understanding the distinction between Index Scans and Index Only Scans helps in:

• Writing more efficient queries
• Creating appropriate indexes
• Optimizing database performance

Remember that Index Only Scans are generally more efficient, but they require careful planning of your indexes and query structure.