Setup
A Postgres table events has 10 million rows:
CREATE TABLE events (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
type text NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL
);
CREATE INDEX events_user_id ON events (user_id);The activity feed runs this query:
SELECT * FROM events
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;A typical user has about 2,000 events. The heaviest user has 400,000. The database runs on a mid-sized cloud instance, the table fits in memory, and nothing else is running.
Outcome
100 ms to 1 second. On a setup like this one, the query typically takes a few hundred milliseconds for the heaviest user, compared with a few milliseconds for a typical user.
The index on user_id finds the user’s rows, but it doesn’t help with ORDER BY created_at. Postgres has to fetch all 400,000 rows for that user, read each one from the table, and then sort them to find the newest 20. LIMIT 20 doesn’t help, because it can only stop early if the rows already come out in order.
With a composite index on (user_id, created_at DESC), the same query reads 20 index entries and returns in well under a millisecond, whatever the user’s size.
Senior commentary
“‘It’s indexed, so it’s fast’ is the trap. The question is always: indexed for which part of the query? Here the index covers the filter but not the sort, so the cost grows with how many rows match, not with the LIMIT.
“The other lesson is to think about your heaviest user, not your typical one. A query that takes 3 ms for most people and 400 ms for your biggest customer is a query your biggest customer complains about. Run EXPLAIN ANALYZE with the heaviest real user ID, not a random one.”
Discussion questions
- What was your answer based on: the row count, the index, the
LIMIT, or something else? - How would the answer change if the table didn’t fit in memory?
- Can you think of a query in your own system that’s fast for typical users and slow for your heaviest one?