Part 133: Composite Indexes & The Leftmost Prefix Rule

An index containing multiple columns: CREATE INDEX idx_user_loc ON users (country, city, last_name);

1. The Leftmost Prefix Rule

Think of a telephone directory sorted by (Last Name, First Name): - Searching by Last Name is fast. - Searching by (Last Name AND First Name) is fast. - Searching only by First Name is impossible without scanning the entire phone book!

-- Uses Index:
WHERE country = 'US'
WHERE country = 'US' AND city = 'Chicago'
WHERE country = 'US' AND city = 'Chicago' AND last_name = 'Smith'

-- ❌ CANNOT use index:
WHERE city = 'Chicago'
WHERE last_name = 'Smith'

Key Takeaways

• Place the most selective and most frequently queried equality columns on the left of composite indexes.

In Part 134, we explore Specialized Indexes: Hash, GIN, GiST & Full-Text!