Hands-on PostgreSQL Indexing Techniques and Exercises

This resource offers a total of 60 PostgreSQL Index types problems for practice. It includes 12 main exercises, each accompanied by solutions, detailed explanations, and four related problems.

Following exercises cover a variety of index types and demonstrate how to create, use, and manage B-tree, Hash, GIN, and GiST indexes in PostgreSQL for performance optimization.

1. Create a B-tree Index on a Text Column

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

Click me to see the solution

2. 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.

Click me to see the solution

3. 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.

Click me to see the solution

4. Create a Hash Index for Equality Search

Write a PostgreSQL query to create a hash index on the "username" column in the Users table.

Click me to see the solution

5. Hash Index on employee_id in Employees

Write a PostgreSQL query to create a hash index on the "employee_id" column in the Employees table.

Click me to see the solution

6. Create a GIN Index on an Array Column

Write a PostgreSQL query to create a GIN index on the "tags" array column in the Articles table.

Click me to see the solution

7. GIN Index on data in Products

Write a PostgreSQL query to create a GIN index on the "data" column in the Products table, where data is stored as JSONB.

Click me to see the solution

8. GiST Index on location in Stores

Write a PostgreSQL query to create a GiST index on the "location" column in the Stores table, where location is of a geometric data type.

Click me to see the solution

9. Create a GiST Index on a Point Column

Write a PostgreSQL query to create a GiST index on the "coordinates" column in the Landmarks table, where coordinates are stored as points.

Click me to see the solution

10. Unique B-tree Index on email in Users

Write a PostgreSQL query to create a unique B-tree index on the "email" column in the Users table.

Click me to see the solution

11. Create a Partial GIN Index for Full-Text Search

Write a PostgreSQL query to create a partial GIN index on the "document" column in the Documents table for rows where "published" is true.

Click me to see the solution

12. Drop a Specific Index

Write a PostgreSQL query to drop the previously created GIN index on the "tags" column in the Articles table.

Click me to see the solution

More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.