Covering Index

Introduction

Covering Index is used to enable Index-Only Scan with Non-key Columns, initialized using the INCLUDE clause
PostgreSQL supports three types of indexes for this feature: B-Tree (Most common), GiST and SP-GiST
All other index types (GIN, BRIN, Hash) do NOT support the INCLUDE clause.

How it works

When you create an index with INCLUDE as follows: CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_amount, status)
- Main column (user_id): Located at the upper nodes and leaf nodes of the B-Tree. Used for searching, filtering (WHERE) and sorting (ORDER BY).
- Payload columns (total_amount, status): ONLY stored at Leaf nodes. They do not participate in tree sorting or searching operations.
The main purpose is to help Postgres perform an Index-Only Scan, meaning Postgres retrieves data for total_amount and status directly from the index without reading the Heap Page, making the SELECT query execution extremely fast.

Advantages

Avoiding placing those columns directly into the main key list tells the Query Planner that you only use them to SELECT data, not for searching or sorting
Using a Covering Index (with INCLUDE (total_amount)), instead of a Composite Index (a multi-column index like (user_id, total_amount)), is beneficial for the following key reasons
- Key Size Limit: Columns in the search key part of an index have length limits. Columns inside INCLUDE are exempt from this limitation.
- Smaller Index Size: Only leaf nodes contain INCLUDE columns, while Internal Nodes of the B-Tree tree do not, keeping the index tree lightweight and searches faster.

Detail

Create the table and seed data as follows

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    shipping_address TEXT NOT NULL
);

INSERT INTO orders (customer_id, order_date, status, total_amount, shipping_address)
SELECT 
    floor(random() * 5000 + 1)::int AS customer_id,
    NOW() - (random() * 365 || ' days')::interval AS order_date,
    (ARRAY['PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED'])[floor(random() * 5 + 1)] AS status,
    round((random() * 990 + 10)::numeric, 2) AS total_amount,
    floor(random() * 9999 + 1)::text || ' ' || 
    (ARRAY['Main St', 'Broadway', 'Oak Ave', 'Pine St', 'Maple Dr', 'Washington Ave'])[floor(random() * 6 + 1)] || ', City ' || 
    chr(floor(random() * 26 + 65)::int) AS shipping_address
FROM generate_series(1, 100000);

Suppose you need to retrieve the status and total_amount for a customer with the following query

SELECT status, total_amount FROM orders WHERE customer_id = 1050;

CREATE INDEX idx_orders_customer_covering ON orders (customer_id) INCLUDE (status, total_amount);

If using conventional methods, Postgres can only use a Seq Scan to check row by row