DEV Community

Cover image for My "latest 20 issues" query read 200,000 rows, even with an index on project_id
Muhammad Adnan
Muhammad Adnan

Posted on

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

#postgres #performance #sql #backend

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;
Enter fullscreen mode Exit fullscreen mode

And almost every schema has the index that seems to cover it:

CREATE INDEX ON issues (project_id);
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)