Boost Salary Query Performance with a B-tree Index

PostgreSQL - Create a B-tree Index on a Numeric Column

Write a PostgreSQL query to create a B-tree index on the "salary" column in the Employees table.

Solution:

-- Create a B-tree index on the "salary" column.
CREATE INDEX idx_employees_salary ON Employees USING btree (salary);

Explanation:

• Purpose of the Query:
• To optimize queries that filter or sort by the "salary" column.
• This showcases the use of a B-tree index for numeric data.
• Key Components:
• USING btree explicitly sets the index type.
• (salary) specifies the column to be indexed.
• Real-World Application:
• Beneficial for range queries such as finding employees with salaries above a certain threshold.

Notes:

• B-tree indexes efficiently support both equality and range queries.
• Ensure that the column data type is compatible with B-tree indexing.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a B-tree index on the "price" column in the "Products" table.
• Write a PostgreSQL query to create a B-tree index on the "age" column in the "Users" table.
• Write a PostgreSQL query to create a B-tree index on the "rating" column in the "Reviews" table.
• Write a PostgreSQL query to create a B-tree index on the "quantity" column in the "Inventory" table.

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

Previous PostgreSQL Exercise: Create a B-tree Index on a Text Column.
Next PostgreSQL Exercise: Create a Composite B-tree Index on two Columns.