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

Practice

1. Why is having a good indexing strategy important in PostgreSQL?

easy

A. It helps the database find data faster, improving query speed.

B. It increases the size of the database without benefits.

C. It makes the database ignore queries.

D. It automatically fixes data errors.

Solution

1. Step 1: Understand what indexes do

   Indexes act like shortcuts to quickly locate data without scanning the whole table.

2. Step 2: Connect indexing to query speed

   Good indexes reduce the time to find data, making queries faster and more efficient.

3. Final Answer:

   It helps the database find data faster, improving query speed. -> Option A

4. Quick Check:

   Index = Faster data search [OK]

Hint: Indexes speed up searches by acting like shortcuts [OK]

Common Mistakes:

• Thinking indexes slow down queries
• Believing indexes fix data errors
• Assuming indexes increase query ignoring

2. Which of the following is the correct syntax to create a basic index on column email in PostgreSQL?

easy

A. CREATE INDEX ON users email;

B. CREATE INDEX idx_email ON users (email);

C. MAKE INDEX idx_email ON users email;

D. INDEX CREATE idx_email users (email);

Solution

1. Step 1: Recall PostgreSQL index creation syntax

   The correct syntax starts with CREATE INDEX, followed by index name, ON table name, and column list in parentheses.

2. Step 2: Match syntax to options

   CREATE INDEX idx_email ON users (email); matches the correct syntax exactly; others have wrong keywords or missing parentheses.

3. Final Answer:

   CREATE INDEX idx_email ON users (email); -> Option B

4. Quick Check:

   CREATE INDEX ... ON table (column) [OK]

Hint: Use CREATE INDEX index_name ON table (column) [OK]

Common Mistakes:

• Omitting parentheses around column name
• Using wrong keywords like MAKE or INDEX CREATE
• Missing ON keyword before table name

3. Given a table orders with 1 million rows and an index on customer_id, what is the likely result of this query?

SELECT * FROM orders WHERE customer_id = 12345;

medium

A. The query will return no rows because indexes filter data.

B. The query will scan all rows, ignoring the index.

C. The query will fail due to missing index.

D. The query will use the index to quickly find matching rows.

Solution

1. Step 1: Understand index usage in queries

   When a column is indexed, PostgreSQL uses the index to find matching rows quickly instead of scanning the whole table.

2. Step 2: Apply to the given query

   The query filters by customer_id, which is indexed, so the index helps find rows efficiently.

3. Final Answer:

   The query will use the index to quickly find matching rows. -> Option D

4. Quick Check:

   Indexed column = faster search [OK]

Hint: Queries on indexed columns use indexes for speed [OK]

Common Mistakes:

• Thinking index is ignored automatically
• Assuming query fails without explicit index hint
• Believing indexes filter out rows

4. You created multiple indexes on a table, but your INSERT queries became slower. What is the most likely cause?

medium

A. Indexes slow down data changes because they must update on each insert.

B. Indexes cause syntax errors during INSERT.

C. Indexes delete rows automatically on insert.

D. Indexes prevent data from being inserted.

Solution

1. Step 1: Understand index impact on data modification

   Indexes must be updated every time data changes, so more indexes mean more work during INSERT, UPDATE, DELETE.

2. Step 2: Connect to slower INSERT queries

   Because indexes update on each insert, having many indexes slows down insert speed.

3. Final Answer:

   Indexes slow down data changes because they must update on each insert. -> Option A

4. Quick Check:

   More indexes = slower inserts [OK]

Hint: More indexes slow inserts due to update overhead [OK]

Common Mistakes:

• Thinking indexes cause syntax errors
• Believing indexes block inserts
• Assuming indexes delete data automatically

5. You have a table products with columns id, category, and price. You often run this query:

SELECT * FROM products WHERE category = 'books' AND price < 20;

Which indexing strategy will most improve query speed without slowing inserts too much?

hard

A. Create no indexes to keep inserts fast.

B. Create separate indexes on category and price.

C. Create a composite index on (category, price).

D. Create an index only on price.

Solution

1. Step 1: Analyze query filter conditions

   The query filters on both category and price together, so a composite index on both columns helps the database find matching rows efficiently.

2. Step 2: Compare indexing options

   Separate indexes may be less efficient because PostgreSQL might not combine them well; no index slows queries; indexing only price misses category filtering.

3. Final Answer:

   Create a composite index on (category, price). -> Option C

4. Quick Check:

   Composite index matches multi-column filters [OK]

Hint: Use composite index for multi-column WHERE filters [OK]

Common Mistakes:

• Creating separate indexes expecting same speed
• Indexing only one column in multi-filter queries
• Avoiding indexes to keep inserts fast but hurting queries