How to Use INCLUDE in Index in PostgreSQL

In PostgreSQL, use the INCLUDE clause in a CREATE INDEX statement to add extra columns to the index that are not part of the search key but are stored in the index for faster access. This helps speed up queries by covering more columns without affecting the index's uniqueness or ordering.

Syntax

The basic syntax to create an index with included columns in PostgreSQL is:

CREATE INDEX index_name ON table_name (key_column1, key_column2, ...) INCLUDE (non_key_column1, non_key_column2, ...);

Explanation:

• index_name: Name you give to the index.
• table_name: The table on which you create the index.
• key_column1, key_column2, ...: Columns used to search or sort in the index.
• INCLUDE (non_key_column1, ...): Extra columns stored in the index but not used for searching or sorting.

sql

CREATE INDEX idx_example ON my_table (column1) INCLUDE (column2, column3);

Example

This example creates an index on customer_id and includes order_date and total_amount as non-key columns. This helps queries that filter by customer_id and select the included columns to run faster without accessing the main table.

sql

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id int NOT NULL,
  order_date date NOT NULL,
  total_amount numeric NOT NULL
);

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

-- Query that benefits from the index
EXPLAIN ANALYZE
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 123;

Output

Index Scan using idx_orders_customer on orders  (cost=0.29..8.31 rows=1 width=20) (actual time=0.010..0.011 rows=0 loops=1)
  Index Cond: (customer_id = 123)
Planning Time: 0.123 ms
Execution Time: 0.030 ms

Common Pitfalls

• Including columns in the index does not make them searchable or sortable; only key columns are used for that.
• Including too many columns can increase index size and slow down writes.
• Trying to use INCLUDE in older PostgreSQL versions (before 11) will cause errors because the feature was introduced in version 11.
• Do not confuse INCLUDE columns with key columns; only key columns affect index uniqueness and ordering.

sql

/* Wrong: Trying to search by an included column */
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date = '2023-01-01';

/* Right: Search by key column */
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

Quick Reference

Clause	Description
CREATE INDEX	Starts the index creation statement.
index_name	Name you assign to the index.
ON table_name	Specifies the table to index.
(key_columns)	Columns used for searching and sorting.
INCLUDE (non_key_columns)	Extra columns stored in the index for faster access but not used for searching.

Key Takeaways

Use INCLUDE to add non-key columns to a PostgreSQL index for faster query access without affecting uniqueness.

Only key columns in the index are used for searching and sorting; included columns are stored for covering queries.

INCLUDE was introduced in PostgreSQL 11; older versions do not support it.

Avoid including too many columns to keep index size and write performance optimal.

Queries filtering by key columns benefit most from indexes with included columns.