My "latest 20 issues" query read 200,000 rows, even with an index on project_id

Almost every app has this query. Show the newest items in a project, one page at a time: SELECT id, title, status, created_at FROM issues WHERE project_id = $1 ORDER BY created_at DESC, id DESC LIMIT 20; And almost every schema has the index that seems to cover it: CREATE INDEX ON issues (project_id); It's indexed, it's fast in development, and code review passes it. I measured what it does once one project gets big. The setup PostgreSQL 16, 2,000,000 issues across 2,000 projects. Project 1 is a big customer with 200,000 issues; project 500 is typical, with 901. Each number below is the median of 5 runs of EXPLAIN (ANALYZE, BUFFERS), with the data in memory. Index Project Execution time Buffers touched (project_id) big (200,000 issues) 20.9 ms 1,941 (project_id) typical (901 issues) 2.42 ms 911 (project_id, created_at DESC, id DESC) big 0.091 ms 23 (project_id, created_at DESC, id DESC) typical 0.106 ms 23 On the big project the composite index is about 230 times faster. The more useful number is the last column: 1,941 buffers against 23. Why the "indexed" query reads every row Here is the plan with the project_id index, for the big project: Sort Method: top-N heapsort -> Parallel Bitmap Heap Scan on issues (rows=66667 loops=3) -> Bitmap Index Scan on i_proj (rows=200000) The index finds the project's rows, but it knows nothing about created_at. So Postgres has to fetch all 200,000 rows of that project, sort them by date, and keep the top 20. Three parallel workers split the job. That's a lot of machinery to return 20 rows. The cost grows with the size of the project, not the size of the page. A typical project pays it a little (2.4 ms); your biggest customer pays it the most, on every page load. And it only stays at 20 ms while those rows are cached. When they have to come from disk, those 1,941 buffers become real reads. The fix: filter columns first, then the sort columns CREATE INDEX CONCURRENTLY idx_issues_project_created ON issues (project_id, created_at DESC, id DESC); Now the index is already ordered the way the query asks. The plan becomes: -> Index Scan using i_proj_created on issues Index Cond: (project_id = 1) No sort step. Postgres walks the index from the newest entry and stops after 20. Big project or small, it reads 23 buffers. Three details make it work: Equality columns first, then the ORDER BY columns. With (created_at, project_id), Postgres can't jump to one project's rows; it walks the whole table's timeline and filters as it goes, which gets worse the smaller the project. Match the sort exactly. Including the id tiebreaker. That's also what makes keyset pagination (WHERE (created_at, id) < ($2, $3)) stable, and the same index serves it. Build it with CONCURRENTLY. A plain CREATE INDEX blocks writes to issues for the whole build. Once the composite index exists, the old (project_id) index is redundant: the composite one serves WHERE project_id = $1 on its own. Dropping it saves write overhead. How to spot it in your own plans Run the slow query with EXPLAIN (ANALYZE, BUFFERS) on your biggest tenant, not your test data. Look for: A Sort (often top-N heapsort) right under the Limit. rows= on the scan far larger than your LIMIT. Buffers in the thousands for a page of 20. Any of those on a "latest N" query means the index doesn't match the ORDER BY. Checklist Every "latest N per parent" query has an index on (parent_id, sort columns...) in the query's sort direction. The tiebreaker column is in both the ORDER BY and the index. Benchmarks run against your largest parent, not an average one. New indexes are built with CONCURRENTLY. Making Claude Code do this by default Reading a plan like this and picking the right index is one of the rules in the slow-query profiling skill of my Claude Code skills pack for TypeScript + GraphQL + Postgres backends. The pack's starter backend ships with exactly this composite index on its issues table. Two skills are free (GraphQL N+1 fixes and safe Postgres migrations): github.com/adnan909/free-sample The full pack, with the slow-query profiling skill, 6 more skills and a starter backend tested in CI: Backend Skills Pack on Gumroad