Partial indexes with WHERE clause in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of query using a partial index

Given a table orders with columns id, status, and amount, a partial index is created as:

CREATE INDEX idx_pending_orders ON orders(amount) WHERE status = 'pending';

What will be the result of this query?

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND amount > 100;

A The query uses the index but scans all rows regardless of status.

B The query performs a full table scan ignoring the partial index.

C The query uses the partial index to scan only rows with status 'pending' and amount > 100.

D The query raises an error because partial indexes cannot be used in WHERE clauses.

💡 Hint

Partial indexes only include rows matching the WHERE condition.

The partial index idx_pending_orders includes only rows where status = 'pending'. The query filters on the same condition, so PostgreSQL can use the index to efficiently find rows with amount > 100 among pending orders.

📝 Syntax

intermediate

Correct syntax for creating a partial index

Which of the following is the correct syntax to create a partial index on the users table for rows where active = true?

A CREATE PARTIAL INDEX idx_active_users ON users(id) WHERE active = true;

B CREATE INDEX idx_active_users ON users(id) WHERE active = true;

C CREATE INDEX idx_active_users ON users(id) IF active = true;

D CREATE INDEX idx_active_users ON users(id) FILTER active = true;

💡 Hint

Partial indexes use the WHERE clause after the index definition.

The correct syntax uses CREATE INDEX followed by the index name, table, columns, and a WHERE clause specifying the condition. There is no CREATE PARTIAL INDEX command.

❓ optimization

advanced

Choosing a partial index for query optimization

You have a transactions table with millions of rows. Most rows have status = 'completed', but you often query for status = 'failed' transactions. Which partial index would best optimize queries filtering WHERE status = 'failed'?

A CREATE INDEX idx_failed_transactions ON transactions(id) WHERE status = 'failed';

B CREATE INDEX idx_completed_transactions ON transactions(id) WHERE status = 'completed';

C CREATE INDEX idx_all_transactions ON transactions(id);

D CREATE INDEX idx_failed_transactions ON transactions(id) WHERE status != 'failed';

💡 Hint

Partial indexes should match the query filter condition to be effective.

Since queries filter for status = 'failed', creating a partial index on only those rows improves performance by indexing fewer rows and speeding up lookups.

🔧 Debug

advanced

Why does this partial index not improve query performance?

A partial index was created as:

CREATE INDEX idx_active_users ON users(id) WHERE active = true;

But the query below does not use the index:

SELECT * FROM users WHERE active = false AND id = 123;

Why?

A The query optimizer ignores all indexes on boolean columns.

B The index is corrupted and needs to be rebuilt.

C Partial indexes cannot be used with equality filters.

D The query filters for <code>active = false</code>, but the index only covers rows where <code>active = true</code>.

💡 Hint

Partial indexes only include rows matching their WHERE condition.

The partial index only includes rows where active = true. The query filters for active = false, so the index cannot be used.

🧠 Conceptual

expert

Effect of partial index on unique constraint enforcement

Consider a table employees with a partial unique index:

CREATE UNIQUE INDEX idx_unique_email_active ON employees(email) WHERE active = true;

What is the effect of this index on enforcing uniqueness of email values?

A It enforces uniqueness of <code>email</code> only for rows where <code>active = true</code>. Duplicate emails can exist for inactive employees.

B It enforces uniqueness of <code>email</code> for all rows regardless of <code>active</code> status.

C It does not enforce uniqueness at all because partial indexes cannot be unique.

D It enforces uniqueness only for inactive employees.

💡 Hint

Partial unique indexes apply uniqueness only to rows matching the WHERE condition.

The unique constraint applies only to rows where active = true. Rows where active = false can have duplicate email values without violating the index.

Practice

1. What is the main purpose of a partial index with a WHERE clause in PostgreSQL?

easy

A. To index only rows that meet a specific condition

B. To index all rows in the table regardless of condition

C. To create a backup of the table

D. To delete rows that do not meet the condition

Solution

1. Step 1: Understand partial index concept

   A partial index indexes only a subset of rows based on a condition.

2. Step 2: Role of WHERE clause

   The WHERE clause defines which rows to include in the index.

3. Final Answer:

   To index only rows that meet a specific condition -> Option A

4. Quick Check:

   Partial index = conditional indexing [OK]

Hint: Partial indexes use WHERE to limit indexed rows [OK]

Common Mistakes:

• Thinking partial indexes index all rows
• Confusing partial index with table backup
• Assuming partial index deletes rows

2. Which of the following is the correct syntax to create a partial index on a table orders for rows where status = 'pending'?

easy

A. CREATE INDEX idx_pending ON orders (status) WHERE status = 'pending';

B. CREATE INDEX idx_pending ON orders WHERE status = 'pending';

C. CREATE INDEX idx_pending ON orders WHERE status = 'pending' (status);

D. CREATE INDEX idx_pending ON orders (status) WHERE status == 'pending';

Solution

1. Step 1: Check index creation syntax

   CREATE INDEX requires index name, table, columns, and optional WHERE clause.

2. Step 2: Validate WHERE clause and operators

   Use single equals (=) for comparison, and specify columns to index.

3. Final Answer:

   CREATE INDEX idx_pending ON orders (status) WHERE status = 'pending'; -> Option A

4. Quick Check:

   Correct syntax includes columns and WHERE with = [OK]

Hint: Include columns before WHERE; use single = for condition [OK]

Common Mistakes:

• Omitting columns in index definition
• Using double equals (==) instead of single =
• Placing WHERE before columns

3. Given the partial index:
CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;
What will be the result of this query?
SELECT * FROM users WHERE active = true AND last_login > '2024-01-01';

medium

A. The query will return no rows because partial indexes exclude all rows

B. The query will ignore the partial index and perform a full table scan

C. The query will cause a syntax error due to the partial index

D. The query will use the partial index and return matching rows quickly

Solution

1. Step 1: Understand partial index condition

   The index covers rows where active = true, indexing last_login.

2. Step 2: Analyze query filter

   The query filters on active = true and last_login > '2024-01-01', matching the index condition.

3. Final Answer:

   The query will use the partial index and return matching rows quickly -> Option D

4. Quick Check:

   Query matches partial index condition = uses index [OK]

Hint: Query filters must match partial index WHERE to use it [OK]

Common Mistakes:

• Assuming partial index causes syntax errors
• Thinking partial index excludes all rows
• Believing query ignores partial index if condition partially matches

4. You created a partial index:
CREATE INDEX idx_recent_orders ON orders (order_date) WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
But queries filtering order_date > CURRENT_DATE - INTERVAL '30 days' are not using the index. What is the likely problem?

medium

A. Partial indexes cannot use date intervals

B. The index is missing the column order_date

C. The partial index condition uses a non-immutable function, so it cannot be used

D. The WHERE clause in the index is invalid syntax

Solution

1. Step 1: Check partial index WHERE clause

   The WHERE clause uses CURRENT_DATE, which is non-immutable (changes daily).

2. Step 2: Understand index usage limitation

   PostgreSQL partial indexes require immutable conditions to be used by queries.

3. Final Answer:

   The partial index condition uses a non-immutable function, so it cannot be used -> Option C

4. Quick Check:

   Non-immutable functions block partial index usage [OK]

Hint: Partial index WHERE must use immutable expressions [OK]

Common Mistakes:

• Assuming syntax error causes issue
• Thinking partial indexes can't use date columns
• Ignoring function immutability rules

5. You want to speed up queries on a products table filtering only active products with price < 100. Which partial index is best to create?

hard

A. CREATE INDEX idx_active_price ON products (price);

B. CREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100;

C. CREATE INDEX idx_active_price ON products (price) WHERE active = true OR price < 100;

D. CREATE INDEX idx_active_price ON products WHERE active = true AND price < 100;

Solution

1. Step 1: Define index condition matching query filters

   The query filters active = true AND price < 100, so index WHERE must match both.

2. Step 2: Check syntax and columns

   Index must specify columns (price) and use AND in WHERE clause for correct filtering.

3. Final Answer:

   CREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100; -> Option B

4. Quick Check:

   Partial index WHERE matches query filters exactly [OK]

Hint: Use AND in WHERE to match all query conditions [OK]

Common Mistakes:

• Using OR instead of AND in WHERE clause
• Omitting columns in index definition
• Trying to create index without columns