How a Composite Index Fixed My Slow Prisma Query in Postgres

A few months ago I had a dashboard page that felt fine in development and painfully slow in production. The culprit was a single, innocent-looking Prisma query: fetch the latest orders for a user, newest first, 20 per page. Locally the table had a few hundred rows. In production it had millions, and Postgres was reading far more of them than it needed to.

The fix was one line in my Prisma schema: a composite index. In this post I want to walk through how I found the problem, why a composite index works (and why the column order matters so much), and how I check that Postgres actually uses it.

The slow query

Here is a simplified version of the model and the query:

model Order {
  id        String   @id @default(cuid())
  userId    String
  status    String
  total     Int
  createdAt DateTime @default(now())

  user User @relation(fields: [userId], references: [id])
}

const orders = await prisma.order.findMany({
  where: { userId, status: "PAID" },
  orderBy: { createdAt: "desc" },
  take: 20,
});

Nothing wrong with that code. The problem is what the database has to do to answer it. Without a suitable index, Postgres has two bad options: scan the whole table (a sequential scan) and filter, or use some other index and then sort every matching row in memory before it can return the top 20.

Prisma does not magically create indexes for you. It creates the primary key and unique constraints you declare, but foreign key columns like userId are not indexed automatically on Postgres.

That last point surprises a lot of people. Postgres creates an index for primary keys and unique constraints, but not for the referencing side of a foreign key. So a relation field you filter on constantly can be completely unindexed.

Step 1: Look at the query plan

Before adding anything, I always look at what Postgres is actually doing. I turned on Prisma query logging to grab the generated SQL, then ran it in psql with EXPLAIN ANALYZE:

const prisma = new PrismaClient({
  log: ["query"],
});

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, "userId", status, total, "createdAt"
FROM "Order"
WHERE "userId" = 'clx123' AND status = 'PAID'
ORDER BY "createdAt" DESC
LIMIT 20;

The plan showed what I expected: a Seq Scan on "Order" with a Filter line, a large number of Rows Removed by Filter, and a Sort node above it. In plain English: Postgres read the entire table, threw almost all of it away, sorted what was left, and then returned 20 rows.

A few things I look for in every plan:

• Seq Scan on a large table in a hot query path is usually a red flag.
• Rows Removed by Filter tells you how much work was wasted.
• A Sort node right under a Limit means the database had to sort everything before taking the top N.
• actual time on each node shows where the time really goes. Note that EXPLAIN ANALYZE actually executes the query, so be careful with writes.

Step 2: Add a composite index in Prisma

The query filters on userId and status with equality, then sorts by createdAt. That shape maps directly onto a B-tree index with three columns:

model Order {
  id        String   @id @default(cuid())
  userId    String
  status    String
  total     Int
  createdAt DateTime @default(now())

  user User @relation(fields: [userId], references: [id])

  @@index([userId, status, createdAt(sort: Desc)])
}

Then generate and apply the migration:

npx prisma migrate dev --name order_user_status_created_idx

Prisma produces a plain CREATE INDEX statement in the migration SQL. Running EXPLAIN ANALYZE again, the plan changed to an Index Scan using "Order_userId_status_createdAt_idx" under the Limit, and the Sort node was gone entirely. Postgres could jump straight to that user's paid orders, already stored in createdAt order, and stop after 20 rows.

Why column order matters

This is the part I wish someone had explained to me earlier. A B-tree composite index is sorted by the first column, then by the second within each value of the first, and so on. Think of a phone book sorted by last name, then first name.

That has a few practical consequences:

1. Equality columns first, range or sort columns last. With (userId, status, createdAt), all rows for one user and one status sit next to each other, already ordered by date. Perfect for our query.
2. The leftmost prefix rule. The same index efficiently serves WHERE userId = ? and WHERE userId = ? AND status = ?. It is much less useful for WHERE status = ? alone, because status values are scattered across every user. (Postgres can sometimes still use it, but usually not well.)
3. Putting the range column first breaks things. An index on (createdAt, userId) would force Postgres to walk through dates and check the user on each entry, which is much worse for this access pattern.

A good rule of thumb I use: write the index columns in the order equality filters → range filter or sort. If you have both a range filter and a different sort column, you usually can't satisfy both with one index, and you need to decide which matters more.

Does the sort direction matter?

For a single sort column, not much. Postgres can scan a B-tree index backwards, so an ascending index can serve ORDER BY createdAt DESC too. I still add sort: Desc because it documents intent. Direction becomes important when you sort by multiple columns in mixed directions, such as ORDER BY priority DESC, createdAt ASC; then the index needs to match that mix (or its exact reverse).

Going further: covering and partial indexes

Once the main index was in place, two other Postgres features were worth knowing about.

Partial indexes

If 95% of your queries only ever ask for status = 'PAID', you can index only those rows. The index is smaller, cheaper to maintain on writes, and more likely to stay in memory:

CREATE INDEX order_paid_user_created_idx
  ON "Order" ("userId", "createdAt" DESC)
  WHERE status = 'PAID';

Prisma's schema language doesn't describe partial indexes, so I add these by editing the generated migration SQL by hand (for example, with prisma migrate dev --create-only, then editing the file before applying it). Just be aware that Prisma doesn't know about that WHERE clause, so keep a comment in the schema so the next person isn't confused.

Covering indexes with INCLUDE

If the query only needs a couple of extra columns, Postgres can return them straight from the index with an Index Only Scan, skipping the table lookup. The INCLUDE clause stores extra columns in the index without making them part of the sort key:

CREATE INDEX order_user_created_cover_idx
  ON "Order" ("userId", "createdAt" DESC)
  INCLUDE (total, status);

Index-only scans also depend on the table's visibility map being reasonably up to date, which autovacuum normally handles. I treat covering indexes as a targeted optimization for very hot read paths, not a default.

Indexes are not free

It's tempting to sprinkle @@index everywhere after a win like this. I try not to. Every index:

• has to be maintained on every INSERT and on many UPDATEs, which slows writes;
• takes disk space and competes for memory in the buffer cache;
• gives the query planner more options to consider.

Postgres tracks how often each index is used, so you can find dead weight:

SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 after weeks in production is a strong candidate for removal (stats reset on certain events, and replicas track their own usage, so check before dropping anything). Also look for redundant indexes: if you have both (userId) and (userId, status, createdAt), the single-column one is usually unnecessary because the composite index already covers its leftmost prefix.

Adding indexes to a busy production table

A plain CREATE INDEX blocks writes to the table while it builds. On a big, busy table that can mean minutes of failed or queued writes. Postgres offers CREATE INDEX CONCURRENTLY, which builds the index without blocking writes, at the cost of taking longer:

CREATE INDEX CONCURRENTLY "Order_userId_status_createdAt_idx"
  ON "Order" ("userId", "status", "createdAt" DESC);

Two caveats: CONCURRENTLY cannot run inside a transaction block, so check how your migration tool executes the file, and if the build fails it can leave behind an INVALID index that you need to drop and recreate. For large tables I usually create the migration with --create-only, swap in the concurrent version, and keep that migration to a single statement.

My workflow now

1. Find slow queries (Prisma query logs, pg_stat_statements, or your APM).
2. Run EXPLAIN (ANALYZE, BUFFERS) on the real SQL against realistic data.
3. Design an index that matches the query shape: equality columns first, then range or sort.
4. Add it with @@index (or hand-edited SQL for partial and covering indexes).
5. Re-run the plan and confirm the index is used and the sort disappeared.
6. Periodically review pg_stat_user_indexes and remove what isn't pulling its weight.

The biggest lesson for me was to test against production-like data volumes. With a few hundred rows, a sequential scan is often the fastest plan, so a missing index is invisible until real traffic arrives.

---

Key takeaways

• Postgres does not automatically index foreign key columns, and Prisma won't add those indexes for you on Postgres.
• Always read EXPLAIN ANALYZE before and after: look for Seq Scans, Rows Removed by Filter, and Sort nodes under a Limit.
• In composite indexes, put equality columns first and the range or ORDER BY column last.
• Partial and covering (INCLUDE) indexes are powerful, but need hand-written migration SQL.
• Every index costs write performance and memory, so audit usage with pg_stat_user_indexes.
• Use CREATE INDEX CONCURRENTLY on large, busy production tables.