Covering indexes with INCLUDE in PostgreSQL

Introduction

Covering indexes with INCLUDE help speed up queries by storing extra columns in the index, so the database can answer queries without looking at the main table.

When you want to make SELECT queries faster by avoiding extra table lookups.

When you have queries that filter by some columns but also select other columns.

When you want to reduce the time it takes to get results from large tables.

When you want to improve performance without adding too much extra storage.

When you want to optimize read-heavy workloads with specific query patterns.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name (column1, column2) INCLUDE (column3, column4);

The columns inside INCLUDE are not used for searching but are stored in the index for faster access.

This feature is specific to PostgreSQL and helps avoid extra table lookups.

Examples

Creates an index on last_name and stores first_name and email in the index for faster SELECT queries.

PostgreSQL

CREATE INDEX idx_users_name ON users (last_name) INCLUDE (first_name, email);

Indexes order_date and includes customer_id and total_amount to speed up queries selecting those columns.

PostgreSQL

CREATE INDEX idx_orders_date ON orders (order_date) INCLUDE (customer_id, total_amount);

Sample Program

This example creates a table employees, inserts some data, then creates a covering index on last_name including first_name and department. The SELECT query can use the index to get all needed columns without reading the main table.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  last_name TEXT NOT NULL,
  first_name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary INT NOT NULL
);

INSERT INTO employees (last_name, first_name, department, salary) VALUES
('Smith', 'John', 'Sales', 50000),
('Doe', 'Jane', 'Marketing', 60000),
('Brown', 'Charlie', 'Sales', 55000);

CREATE INDEX idx_employees_lastname ON employees (last_name) INCLUDE (first_name, department);

-- Query that benefits from the covering index
SELECT last_name, first_name, department FROM employees WHERE last_name = 'Smith';

Important Notes

Including columns in the index does not affect the order of the index, only the columns used for searching do.

Covering indexes can make indexes larger, so use INCLUDE only for columns needed in SELECT but not in WHERE or JOIN.

PostgreSQL uses covering indexes automatically when possible to speed up queries.

Summary

Covering indexes store extra columns in the index to speed up SELECT queries.

Use INCLUDE to add columns that are selected but not searched on.

They reduce the need to read the main table, improving performance.