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.