QuizzesB-Tree Index Internals — Concept Check
intermediate
sql

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.

8 minutes 8 questions Pass at 70%
Time Limit
8 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 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. 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. 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. 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. 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. 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. 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'.