Composite index and include columns in PostgreSQL

In PostgresSQL, both composite indexes and indexes with included columns are used to enhance query performance, but they serve different purposes

Composite Index

A composite index is an index that include two or more columns as part of index key. The order of columns in a composite index is matters, for example, an index on (a, b) can be used for queries on a, a and b, but not efficiently for b alone.

Example :

-- Create composite index on columns name and grades on table student
CREATE INDEX idx_name_grades ON student (name, grades);

-- Query using index
SELECT * FROM student  WHERE name = 'Alice' AND grades = 90

-- Query not using index
SELECT * FROM student WHERE grades = 90        

Index with include columns

An index with included columns adds additional columns to the index that are not part of the index key. These included columns are stored in the index but are not used for lookups or ordering.

Example:

-- Create index with include column 
CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date) INCLUDE (total_amount);

-- Query using index
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1 AND order_date = '2024-06-16';        

Comparision between Composite Index and Index with include columns

Here are some comparision between these two.

Index Key

• Composite key: All columns are part of index key
• Index with include columns: Only specified key column

Order of columns in query

• Composite key: Order matters
• Index with include columns: Order of included columns doesn't matter

Covering Queries

• Composite key: Can cover queries on key columns
• Index with include columns: Can cover queries needing key and included columns