Index-only scans mental model in PostgreSQL

Introduction

Index-only scans help the database find data faster by using just the index without looking at the full table.

When you want to quickly find rows based on indexed columns.

When the query only needs columns stored in the index.

When you want to reduce disk reads and speed up queries.

When your table is large but the index covers all needed data.

When you want to improve performance without changing the query.

Syntax

PostgreSQL

SELECT column1, column2 FROM table WHERE indexed_column = value;

The database decides automatically if it can use an index-only scan.

Index-only scans work only if the index contains all columns needed by the query.

Examples

Create an index on last_name and first_name columns.

PostgreSQL

CREATE INDEX idx_name ON employees (last_name, first_name);

This query can use an index-only scan if the index covers last_name and first_name.

PostgreSQL

SELECT last_name, first_name FROM employees WHERE last_name = 'Smith';

Simpler query that can also use an index-only scan if last_name is indexed.

PostgreSQL

SELECT last_name FROM employees WHERE last_name = 'Smith';

Sample Program

This example creates a table and an index on last_name and first_name. Then it runs a query that can use an index-only scan because the query only asks for columns in the index.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  last_name TEXT,
  first_name TEXT,
  department TEXT
);

INSERT INTO employees (last_name, first_name, department) VALUES
('Smith', 'John', 'Sales'),
('Doe', 'Jane', 'HR'),
('Smith', 'Anna', 'IT');

CREATE INDEX idx_last_first ON employees (last_name, first_name);

EXPLAIN ANALYZE SELECT last_name, first_name FROM employees WHERE last_name = 'Smith';

Important Notes

Index-only scans avoid reading the full table if all requested columns are in the index.

If the table has recent changes, the scan might need to check the table (heap fetches) to confirm data.

Not all queries can use index-only scans; it depends on the columns and index design.

Summary

Index-only scans speed up queries by using only the index.

They work when the index has all columns needed by the query.

This reduces disk reads and improves performance.