Databases & SQL Practice Question 8: Index Leading Wildcard
Databases & SQL · Test 1 · question 8 of 10
A PostgreSQL table customers has 20 million rows and a B-tree index on email. Statistics are up to date. EXPLAIN shows that SELECT * FROM customers WHERE email LIKE 'alice%'; uses the index. However, SELECT * FROM customers WHERE email LIKE '%@example.com'; does a sequential scan of the whole table.
What is the most likely reason the second query cannot use the index?
Show answer and explanation
Answer:
A B-tree stores values in sorted order. A prefix pattern such as 'alice%' becomes a range scan (everything from alice up to just before alicf), so the index is useful. With a leading wildcard such as '%@example.com', matching values can be anywhere in the sort order, so there is no range to seek to. The planner has to check every row, and a sequential scan is the cheapest way to do that. The PostgreSQL docs say this directly: B-tree supports col LIKE 'foo%' but not col LIKE '%bar'. To support suffix searches, you can use an expression index on reverse(email) and search for the reversed prefix, or a trigram index from the pg_trgm extension.
Option B is contradicted by the stem: EXPLAIN shows the prefix query using the index, and anchored LIKE patterns are a documented use of B-tree indexes. Caching affects how fast a plan runs, not which plan is chosen.
Option C does not fit. The stem says statistics are current, and the same index is already used for the other pattern. Maintenance does not change what a B-tree can physically search.
Option D gets it backwards. B-tree supports equality and range and prefix comparisons. Hash indexes support *only* equality, so a hash index could not serve any LIKE pattern.
Reference: PostgreSQL: Index Types (B-tree and LIKE patterns)