Composite & Partial Indexes — Series 2

Preview — 3 of 10 questions

A plain B-tree index on name isnt being used for this prefix search, even though the query looks like a textbook case for it. Why, and whats the fix?

javascript

CREATE INDEX idx_products_name ON products (name);
SELECT * FROM products WHERE name LIKE 'Lap%';

AUnder most non-"C" locales, a plain B-tree index sorts strings according to locale collation rules, which don't guarantee that a prefix match corresponds to a contiguous index range — creating the index with the text_pattern_ops operator class (CREATE INDEX ... (name text_pattern_ops)) forces byte-wise ordering, enabling prefix LIKE queries to use the index

BLIKE queries never use indexes under any circumstances, regardless of locale

CThe fix is to switch from LIKE to ILIKE, which always uses indexes

Dtext_pattern_ops only works for numeric columns, not text

You commonly query with WHERE status = 'active' AND created_at > '2024-01-01'. Which composite index column order is generally better?

javascript

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

SELECT * FROM orders WHERE status = 'active' AND created_at > '2024-01-01';
-- seeks directly to the 'active' section, then range-scans created_at within it

A(created_at, status) — put the range column first because it's more selective

B(status, created_at) — put the equality column first; the index can seek directly to the 'active' section, then scan the created_at range within it — putting a range column first would prevent efficiently narrowing by the second column beyond the range itself

CColumn order in a composite index never affects query performance

DBoth orders perform identically in all cases

An index is created as (category ASC, price DESC). Which ORDER BY can use it directly, without a separate sort step?

javascript

CREATE INDEX idx_products_cat_price ON products (category ASC, price DESC);

-- Matches forward scan — no sort needed:
SELECT * FROM products ORDER BY category ASC, price DESC;

-- Matches backward scan — also no sort needed:
SELECT * FROM products ORDER BY category DESC, price ASC;

-- Does NOT match either direction — requires a separate sort:
SELECT * FROM products ORDER BY category ASC, price ASC;

AOnly the exact original order category ASC, price DESC can ever use the index — no other permutation

BORDER BY category ASC, price ASC — matches exactly

CORDER BY category DESC, price ASC — also matches, because reversing every column's sort direction still corresponds to the same physical index order, just scanned backwards

DORDER BY price DESC, category ASC — matches, because column order in ORDER BY doesn't need to match the index

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