Optimize Full Name Searches with a Composite Index

Create a Composite B-tree Index on two Columns

Write a PostgreSQL query to create a composite B-tree index on the "first_name" and "last_name" columns in the Employees table.

Solution:

-- Create a composite B-tree index on "first_name" and "last_name".
CREATE INDEX idx_employees_first_last ON Employees USING btree (first_name, last_name);

Explanation:

• Purpose of the Query:
• To enhance query performance when filtering or sorting by both "first_name" and "last_name" together.
• Demonstrates the creation of a multi-column (composite) index.
• Key Components:
• first_name, last_name defines the order of columns in the index.
• The explicit use of USING btree ensures the B-tree method is applied.
• Real-World Application:
• Particularly useful for applications that need to quickly locate employees by their full names.

Notes:

• The order of columns in a composite index is crucial for query optimization.
• Composite indexes can be used when queries filter on the leading column(s).

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a composite B-tree index on the "first_name" and "last_name" columns in the "Contacts" table.
• Write a PostgreSQL query to create a composite B-tree index on the "city" and "state" columns in the "Addresses" table.
• Write a PostgreSQL query to create a composite B-tree index on the "order_date" and "status" columns in the "Orders" table.
• Write a PostgreSQL query to create a composite B-tree index on the "category" and "price" columns in the "Products" table.

Go to:

• Hands-on PostgreSQL Indexing Techniques and Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : PostgreSQL - Create a B-tree Index on a Numeric Column.
NEXT : Create a Hash Index for Equality Search.

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