Indexing Assessment | Database Indexing Deep Dive

1

You have a users table with 50 million rows. The query 'SELECT * FROM users WHERE is_active = true' returns 45 million rows. Should you add an index on is_active?

Yes, it will speed up the query significantly No, the index would be used but provide little benefit Yes, but only a partial index No, the database will likely choose a sequential scan anyway

2

Your e-commerce site has this query: 'SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500 ORDER BY rating DESC LIMIT 20'. What's the optimal index?

INDEX(price, category_id, rating) INDEX(category_id, price, rating) INDEX(category_id, rating DESC, price) INDEX(rating DESC, category_id, price)

3

EXPLAIN shows 'Bitmap Heap Scan' after 'Bitmap Index Scan'. What does this indicate?

The index is corrupted The query matches many rows, so PostgreSQL builds a bitmap to batch table lookups A covering index would be useless here The query is using multiple indexes combined with AND

4

A table has indexes: idx1(A), idx2(A, B), idx3(A, B, C). Which indexes are redundant?

None are redundant idx1 is redundant (covered by idx2 and idx3) idx1 and idx2 are redundant (covered by idx3) idx3 is redundant (too many columns)

5

Your query uses 'WHERE created_at::date = '2024-03-15''. The index on created_at isn't being used. Why?

Date comparisons don't use indexes The ::date cast creates a function call that prevents index use The index needs to be recreated created_at should be a DATE type, not TIMESTAMP

6

You need to support both 'WHERE email = ?' and 'WHERE LOWER(email) = ?'. How many indexes do you need?

One index on email handles both Two indexes: one on email, one on LOWER(email) One index on LOWER(email) handles both No index needed, use COLLATE instead

7

A posts table has 100M rows. You need to show 'SELECT id, title FROM posts WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10'. What index design is optimal?

INDEX(user_id, created_at DESC) INDEX(user_id, created_at DESC) INCLUDE (id, title) INDEX(user_id) and INDEX(created_at) INDEX(created_at DESC, user_id)

8

Your analytics query aggregates millions of rows: 'SELECT COUNT(*) FROM events WHERE event_type = 'click''. It's slow despite an index on event_type. What might help?

Add more columns to the index Create a partial index: CREATE INDEX ON events(event_type) WHERE event_type = 'click' Use a materialized view with pre-computed counts Both B and C could help depending on update frequency

9

After adding an index, queries are still slow. EXPLAIN shows the index isn't being used. Which is NOT a valid reason?

Statistics are outdated (need to run ANALYZE) The query returns too many rows (>10-20% of table) The index was created with wrong column order The database connection is too slow

10

A high-write table (10K inserts/sec) has 5 indexes. Inserts are slow. What should you investigate first?

Increase database memory Review if all 5 indexes are necessary for your query patterns Switch to a NoSQL database Add more CPU cores

11

For full-text search on a documents table with a 'content' column, which approach is correct?

CREATE INDEX ON documents(content) CREATE INDEX ON documents USING gin(to_tsvector('english', content)) CREATE INDEX ON documents USING hash(content) CREATE INDEX ON documents(content) WHERE content LIKE '%search%'

12

What's the main difference between GIN and GiST indexes for full-text search?

GIN is faster for reads but slower for updates; GiST is the opposite GIN only works in PostgreSQL; GiST works in all databases GIN is for text; GiST is only for geometric data There is no difference; they're interchangeable

13

Final question: A query joins 3 tables with WHERE, ORDER BY, and LIMIT. EXPLAIN shows nested loops with 1M iterations. What's the likely issue?

Nested loops are always bad Missing index on the join column of the inner table Too many columns in SELECT The LIMIT clause is too small

Launch Offer