Composite & Partial Indexes

Preview — 3 of 10 questions

You have a composite index on (last_name, first_name). Which query can use this index efficiently?

AWHERE first_name = 'Alice'

BWHERE last_name = 'Smith'

CBoth B and C can use the index

DWHERE first_name = 'Alice' AND last_name = 'Smith'

What is the advantage of a partial index?

javascript

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

AIt indexes only the created_at column, not status

BIt prevents queries without WHERE status = 'pending' from running

CIt creates a smaller index covering only rows where status = 'pending', making it faster and smaller

DIt automatically updates when the status changes to another value

What does the INCLUDE clause do in a PostgreSQL index?

javascript

CREATE INDEX idx_orders_user ON orders (user_id) INCLUDE (status, total);

AAdds status and total as non-key payload columns stored in index leaf pages, enabling index-only scans

BIndexes user_id, status, and total as searchable key columns

CCreates separate indexes on user_id, status, and total

DDeclares a foreign key constraint on those columns

Answer all 10 questions (7 more), see explanations for every answer, and track your score.