B-Tree Index Internals — Concept Check
Questions on B-Tree Index Internals. B-Tree indexes store sorted key values in a balanced tree so the database can binary-search to the target leaf node and follow a pointer to the heap row — avoiding full-table scans.
Questions in this quiz
8 questions on B-Tree Index Internals — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query on the 'users' table with a WHERE clause on LOWER(email) is running slowly. Which index would optimize this query?
- ACreate a partial index on email.
- BCreate an expression index on LOWER(email).
- CCreate a composite index on (email, status).
- DCreate a standard index on email.
2.You notice a sequential scan on a 'status' column with only two possible values. Why might the database choose this scan?
- ASequential scans are faster for low-selectivity columns.
- BIndexes are always slower for columns with few distinct values.
- CThe index on 'status' is corrupted.
- DThe database cannot create indexes on boolean columns.
3.A PostgreSQL index on 'orders(user_id)' is not being used. What could be a reason?
- AThe index is outdated and needs reindexing.
- BIndexes are automatically used regardless of query conditions.
- CThe query uses a non-indexed column in the WHERE clause.
- DThe index is only for sorting, not filtering.
4.In PostgreSQL, what command should you use to prevent locking during index creation?
- ACREATE INDEX
- BCREATE INDEX SAFELY
- CCREATE INDEX CONCURRENTLY
- DCREATE INDEX WITHOUT LOCK
5.A query with WHERE status = 'active' and email = 'x@y.com' is slow. Which index helps?
- AComposite index on (status, email)
- BStandard index on email
- CPartial index on (email) WHERE status = 'active'
- DExpression index on LOWER(email)
6.Why might an index scan be preferred over a sequential scan?
- AIndex scans are cheaper than sequential scans.
- BSequential scans are deprecated in modern databases.
- CIndexes always ensure faster queries.
- DFewer pages are touched due to high selectivity.
7.What is the role of leaf nodes in a B-Tree index?
- AStore internal branch nodes for navigation.
- BMaintain the root node structure.
- CBalance the tree by redistributing keys.
- DHold indexed key values and pointers to heap rows.
8.A query using EXPLAIN shows 'Seq Scan on orders'. What does this indicate?
- AThe query is optimized for high selectivity.
- BThe query is scanning all heap pages without an index.
- CThe index on 'orders' is corrupted.
- DThe query is using an index on 'orders'.