Does the column order in a multi-column B-tree index matter?
Yes. A composite index is sorted by the first column, then the second within it, and so on. It helps queries that filter on a leading prefix of its columns, and it is much less useful if only a later column is filtered.
Explain it simply
A phone directory is sorted by surname, then by first name. Finding "Sharma, Rohit" is instant. Finding everyone named "Rohit" is not, because Rohits are scattered under every surname.
An index on (city, age) behaves the same way: great for "city = Pune", great for "city = Pune AND age > 20", poor for "age > 20" alone.
Example
CREATE INDEX students_city_age_idx ON students (city, age);
-- Good: uses the leading column
SELECT * FROM students WHERE city = 'Pune';
SELECT * FROM students WHERE city = 'Pune' AND age > 20;
-- Poor: skips the leading column, usually a seq scan
SELECT * FROM students WHERE age > 20;A common rule of thumb: put columns tested with equality first, and the range or sort column last. Before PostgreSQL 18 there is no skip scan, so the leading column really matters.
If you need both access patterns, a second index on age may be justified, but every index slows writes.
What to say in the interview
"A composite B-tree index is ordered by its first column, then the next. It serves queries on a leading prefix. I put equality columns first and the range column last, and I check with EXPLAIN rather than assuming."
Common mistakes
- Creating (a, b) and (a) as separate indexes; the first already covers the second.