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

After adding the index, it utilizes Index Only Scan because the required columns (status, total_amount) are present in the index, resulting in

Happy coding!

See more articles here.