Enhance Query Performance with a Composite Index

Creating a Composite Index in PostgreSQL

Write a PostgreSQL query to create an index on multiple columns for optimized multi-column queries.

Solution:

-- Specify the action to create an index.
CREATE INDEX idx_employees_first_last 
-- Define the target table and columns for the composite index.
ON Employees(first_name, last_name);

Explanation:

• Purpose of the Query:
• To optimize queries filtering by both first_name and last_name simultaneously.
• Key Components:
• CREATE INDEX idx_employees_first_last : Names the composite index.
• ON Employees(first_name, last_name) : Specifies the multiple columns for the index.

Notes:

• Composite indexes are beneficial when queries use more than one column in the WHERE clause.
• Order matters; the index is most effective when filtering by the leading column first.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a composite index on the "first_name" and "last_name" columns in the "Contacts" table.
• Write a PostgreSQL query to create a composite index on the "country" and "city" columns in the "Addresses" table.
• Write a PostgreSQL query to create a composite index on the "department_id" and "employee_id" columns in the "Payroll" table.
• Write a PostgreSQL query to create a composite index on the "year" and "quarter" columns in the "Sales" table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating a Unique Index in PostgreSQL.
Next PostgreSQL Exercise: Creating an Index Concurrently in PostgreSQL.