intermediateTop 30 Scenario-Based Questions
Design the indexing strategy for a user search feature: search by name (exact and partial), email, status, and date range.
1) Exact match: CREATE UNIQUE INDEX ON users(email) — already unique; B-Tree for =. 2) Name partial match (full-text): CREATE INDEX ON users USING GIN(to_tsvector('english', first_name || ' ' || last_name)). Query: WHERE to_tsvector('english', ...) @@ to_tsquery('john'). 3) OR for simple LIKE prefix: CREATE INDEX ON users(last_name) for WHERE last_name LIKE 'Smith%'. 4) Status filter: low cardinal
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response