PostgreSQL - Multicolumn Indexes

Multicolumn index in PostgreSQL is an index created on two or more columns of a table. It improves the performance of queries that filter, join or sort using multiple columns together.

• Speed up filtering and sorting operations.
• Optimize WHERE, JOIN and ORDER BY clauses.
• Reduce query execution time for frequently used column combinations.

Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Where:

• index_name: The name of the index.
• table_name: The table on which the index is created.
• column1, column2, ...: The columns included in the index.

Working

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Department VARCHAR(30),
    Salary DECIMAL(10,2),
    City VARCHAR(30)
);

INSERT INTO Employees VALUES
(101, 'John Smith', 'HR', 48000, 'New York'),
(102, 'Emily Davis', 'HR', 65000, 'Chicago'),
(103, 'Michael Brown', 'Finance', 72000, 'Boston'),
(104, 'Sophia Wilson', 'IT', 58000, 'Seattle'),
(105, 'Daniel Lee', 'HR', 72000, 'Chicago'),
(106, 'Olivia Johnson', 'Finance', 68000, 'Boston');

Example 1: Create a Multicolumn Index

The following statement creates a multicolumn index on the Department and Salary columns.

Query:

CREATE INDEX idx_department_salary
ON Employees (Department, Salary);

The statement creates an index on both Department and Salary to improve queries that use these columns together.

Example 2: Use the Multicolumn Index in a Query

The following query retrieves employees from the HR department whose salary is greater than 50000.

Query:

SELECT EmployeeID,
       EmployeeName,
       Department,
       Salary
FROM Employees
WHERE Department = 'HR'
  AND Salary > 50000;

Output:

The multicolumn index can improve the performance of queries that filter using both Department and Salary.

Example 3: Create a Multicolumn Index on Three Columns

The following statement creates a multicolumn index on the Department, Salary and City columns.

Query:

CREATE INDEX idx_department_salary_city
ON Employees (Department, Salary, City);

The statement creates an index on three columns, which can improve queries that use these columns together in filtering or sorting operations.