0
Challenge · 33Correct & Fast

Indexing in practice

Perfused in ~90% of apps

DevLog's feed query used to take 900ms. Concept 32 already fixed the easy version of this problem — one missing index on one foreign key. Production schemas rarely stop at one: DevLog alone has three foreign keys, and the feed's real query filters on TWO columns at once. Index the wrong one of those two, or index both in the wrong order, and the index silently does nothing — which is worse than no index at all, because you now believe the problem is fixed.

Core idea. Indexing in practice means two things concept 32 didn't cover. First: a real schema has several foreign keys, not one, and every single one you filter or join on needs its own index — Postgres never audits this for you. Second, and the harder rule: a composite index on (a, b) is ONE list sorted by a, then by b only as a tiebreaker — so it serves a query on a alone, and a query on (a AND b), but a query on b alone gets no useful help from it at all. That second rule is called leftmost-prefix, and it is the one this concept is built around.
Playground, this isn't a screenshot
index on likes (post_id, user_id) — six entries, in index order
101
alice
101
bob
102
alice
103
alice
103
bob
104
bob
contiguous block
Contiguous — a real range scan. The index is sorted by post_id first, so every entry for post 103 sits next to every other entry for post 103. Postgres walks straight to the start of that block and stops at the end. This is what a composite index is FOR.
↑ play a little first, then dig in