All quizzesMedium
Composite & Partial Indexes — Series 3
Preview — 3 of 10 questions
orders has 2 million rows and a is_paid boolean column, roughly split 50/50. An index exists on is_paid. What does EXPLAIN typically show for WHERE is_paid = true?
AA Seq Scan, because matching ~1 million of 2 million rows isn't selective enough for the planner to prefer walking the index over reading the table sequentially — a boolean column is close to the worst case for index selectivity
BAlways an Index Scan, since any indexed column is used whenever it appears in WHERE
CAn error, since boolean columns cannot be indexed
DA Bitmap Index Scan every time, regardless of row counts
orders has separate single-column indexes on customer_id and status, but no composite index on (customer_id, status). How can WHERE customer_id = 42 AND status = 'pending' still use both indexes efficiently?
AIt can't — without a composite index, at most one of the two indexes is ever used, and the other condition is applied by scanning the table
BPostgreSQL automatically merges the two indexes into a temporary composite index for the duration of the query
CThe planner can scan each index separately to get two sets of matching row locations (via Bitmap Index Scan), then combine them with a BitmapAnd — intersecting the two bitmaps before fetching only the rows present in both — without needing a single composite index at all
DOnly the status index is used, since text columns are always preferred over integer columns
An index exists on email. Why does WHERE email LIKE '%gmail.com' fail to use it, even though WHERE email LIKE 'ana%' can?
ALIKE never uses indexes in PostgreSQL, regardless of wildcard position
B'%gmail.com' is invalid LIKE syntax; only trailing wildcards are allowed
CThis only matters for the text_pattern_ops operator class; the default operator class handles leading wildcards fine
DA B-tree index is sorted, which lets it efficiently jump to a range of values sharing a known prefix — 'ana%' can restrict the search to that range. A leading % means there's no known prefix to narrow the search by, so the sorted structure offers no shortcut, and every row still has to be checked
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.