Bird
0
0

You want to speed up queries filtering by LOWER(username). What is the best indexing approach?

hard📝 optimization Q9 of 15
PostgreSQL - Indexing Strategies
You want to speed up queries filtering by LOWER(username). What is the best indexing approach?
ACreate a functional index on LOWER(username)
BCreate a normal index on username
CCreate an index on username with COLLATE
DCreate no index and rely on sequential scans
Step-by-Step Solution
Solution:
  1. Step 1: Understand functional indexes

    Functional indexes index the result of a function applied to a column, enabling fast queries using that function.
  2. Step 2: Apply to LOWER(username) queries

    Creating an index on LOWER(username) allows queries filtering with LOWER(username) to use the index efficiently.
  3. Final Answer:

    Create a functional index on LOWER(username) -> Option A
  4. Quick Check:

    Functional indexes speed queries using functions on columns [OK]
Quick Trick: Use functional indexes for queries with functions on columns [OK]
Common Mistakes:
  • Using normal index when function is applied
  • Assuming COLLATE indexes functions
  • Avoiding indexes and accepting slow scans

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes