You want to speed up queries filtering on the last 4 digits of a phone number stored as text in column phone. Which expression index is best?

hard📝 Application Q8 of 15

PostgreSQL - Indexing Strategies

You want to speed up queries filtering on the last 4 digits of a phone number stored as text in column phone. Which expression index is best?

ACREATE INDEX idx_phone ON contacts (phone);

BCREATE INDEX idx_phone_first4 ON contacts (LEFT(phone, 4));

CCREATE INDEX idx_phone_length ON contacts (LENGTH(phone));

DCREATE INDEX idx_phone_last4 ON contacts (RIGHT(phone, 4));

Step-by-Step Solution

Solution:

Step 1: Identify the filtering requirement
The query filters on the last 4 digits, so the index should be on RIGHT(phone, 4).
Step 2: Choose the matching expression index
CREATE INDEX idx_phone_last4 ON contacts (RIGHT(phone, 4)); creates an index on RIGHT(phone, 4), which matches the filter and speeds up queries.
Final Answer:
CREATE INDEX idx_phone_last4 ON contacts (RIGHT(phone, 4)); -> Option D
Quick Check:
Index expression must match query filter expression [OK]

Quick Trick: Match index expression to query filter expression exactly [OK]

Common Mistakes:

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free