Why indexing strategy matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index on Query Speed

Consider a table employees with 1 million rows. It has a column department_id. Which query will run faster if there is an index on department_id?

Assume department_id has many repeated values.

PostgreSQL

SELECT * FROM employees WHERE department_id = 5;

A The query will run faster because the index helps find rows with department_id = 5 quickly.

B The query will run slower because the index adds overhead to the search.

C The query speed will be the same because indexes do not affect SELECT queries.

D The query will fail because indexes cannot be used on columns with repeated values.

💡 Hint

Think about how indexes help find data without scanning the whole table.

An index on department_id allows the database to quickly locate rows matching the condition without scanning all rows. This speeds up the query significantly.

🧠 Conceptual

intermediate

Choosing the Right Index Type

Which index type is best suited for a column that stores unique user IDs and is often used in equality searches?

A Hash index because it supports range queries efficiently.

B BRIN index because it is best for columns with many unique values.

C B-tree index because it efficiently supports equality and range queries.

D GIN index because it is optimized for full-text search.

💡 Hint

Think about which index type supports equality searches well.

B-tree indexes are the default and best for equality and range queries. Hash indexes only support equality but are less commonly used. GIN is for full-text or array data. BRIN is for large tables with correlated data.

📝 Syntax

advanced

Creating a Composite Index

Which SQL command correctly creates a composite index on columns last_name and first_name in the customers table?

A CREATE INDEX idx_name ON customers (last_name AND first_name);

B CREATE INDEX idx_name ON customers (first_name, last_name);

C CREATE INDEX idx_name ON customers (last_name); CREATE INDEX idx_name ON customers (first_name);

D CREATE INDEX idx_name ON customers (last_name, first_name);

💡 Hint

Composite indexes list columns separated by commas inside parentheses.

Option D correctly creates a composite index on last_name and first_name. Option D reverses the order, which changes index usage. Option D creates two separate indexes, not a composite one. Option D uses invalid syntax.

❓ optimization

advanced

Impact of Index on Insert Performance

What is a common downside of having many indexes on a table when performing frequent INSERT operations?

A INSERT operations become faster because indexes speed up data insertion.

B INSERT operations become slower because each index must be updated.

C INSERT operations are unaffected by indexes.

D INSERT operations fail if there are more than three indexes.

💡 Hint

Think about what happens to indexes when new data is added.

Each index must be updated when a new row is inserted, which adds overhead and slows down INSERT operations.

🔧 Debug

expert

Why a Query Does Not Use an Index

You created an index on the email column of the users table. However, the query below does not use the index:

SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

Why does this happen?

A Because the query applies a function <code>LOWER()</code> on the column, the index on <code>email</code> is not used.

B Because the index is corrupted and needs to be rebuilt.

C Because the query is missing an explicit <code>USE INDEX</code> hint.

D Because the <code>email</code> column is not indexed by default in PostgreSQL.

💡 Hint

Think about how functions on columns affect index usage.

Applying a function like LOWER() on a column prevents the use of a normal index. To fix this, create an index on the expression LOWER(email).