Covering index

Cover all columns with one index.

A covering index holds every column a query needs. In PostgreSQL the INCLUDE clause adds extra columns to the leaf nodes.

CREATE INDEX idx_orders_cust
ON orders(customer_id)
INCLUDE (amount);

A covering index enables index-only scan.

The index idx_orders_cust is created.

Covering index — details

If the query reads only from the index, the table is never touched. This is an index-only scan and it sharply cuts IO cost.

Create a customer_id index on orders and add the amount column with INCLUDE.

Show example solution

CREATE INDEX idx_orders_cust ON orders(customer_id) INCLUDE (amount);

INCLUDE clause

CREATE INDEX idx_orders_cust ON orders(customer_id) ____ (amount)

Show answer

CREATE INDEX idx_orders_cust ON orders(customer_id) INCLUDE (amount)