Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท Performance and Patterns

Indexes and Query Plans

Speed up Prisma queries with @@index, @unique and composite indexes, learn how the leftmost-prefix rule works, and read EXPLAIN output to confirm an index is used.

An index is a sorted structure the database keeps next to a table so it can find rows without reading the whole table. It is like the index of a book: finding "pagination" takes a glance instead of scanning every page. Most slow Prisma queries are slow because a where or orderBy column has no index.

Prisma schema and queries are read-only here. The TryIts are labelled stand-ins that ask SQLite for query plans, which show the same principle.

Declaring indexes in the schema

PRISMA
model Post {
  id        Int      @id @default(autoincrement())
  slug      String   @unique                    // unique index: fast lookup and uniqueness
  authorId  Int
  status    String
  createdAt DateTime @default(now())
  author    User     @relation(fields: [authorId], references: [id])

  @@index([authorId])                           // filter by author
  @@index([status, createdAt])                  // composite: status then date
}
  • @id and @unique automatically create an index.
  • Foreign keys are not automatically indexed in PostgreSQL (MySQL does create one). Index every relation field you filter or join on, such as authorId, or include: { posts: true } scans the table.
  • @@index([a, b]) creates one composite index. @@unique([a, b]) also enforces uniqueness of the pair.
  • Options exist for sort order (@@index([createdAt(sort: Desc)])), index type, names and database-specific features (PostgreSQL type: Gin for JSON/full-text). Run prisma migrate dev to create them.

The leftmost-prefix rule

A composite index (status, createdAt) is sorted by status first, then by createdAt inside each status. So it can serve:

  • where status = 'published'
  • where status = 'published' orderBy createdAt (filter and sort, no extra sorting)

but not a query that filters only on createdAt, because the data is not sorted by that column alone. Put equality columns first, range and sort columns after. Do not index everything: each index costs disk space and slows every insert, update and delete, because the index must be maintained too.

Seeing the difference with EXPLAIN

EXPLAIN asks the database for the plan without running the query. Run it from Prisma with a raw query (prisma.$queryRawUnsafe('EXPLAIN ANALYZE SELECT ...') for trusted, fixed SQL) or in your SQL client. In PostgreSQL look for Seq Scan (reads the whole table) versus Index Scan or Index Only Scan. EXPLAIN ANALYZE actually runs the query and shows real timings.

Node.js Stand-in: query plan before and after an index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Composite order matters

Node.js Stand-in: leftmost prefix of a composite index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The last query cannot seek into the composite index efficiently, because status comes first.

What to index

  1. Columns in frequent where filters (slug, email, status).
  2. Foreign keys you join or filter by.
  3. Columns in orderBy combined with the filter column, to avoid a sort step.
  4. Columns used by cursor pagination.

What goes wrong

  • Low-selectivity indexes. An index on a boolean with 50/50 values helps little.
  • Functions on the column. WHERE lower(email) = ... cannot use a plain index on email; store a normalized value or create an expression index with raw SQL in a migration.
  • Leading wildcards. contains becomes LIKE '%term%', which cannot use a B-tree. Use full-text search or trigram indexes for that.
  • Too many indexes on write-heavy tables.
  • Unused indexes. PostgreSQL's pg_stat_user_indexes shows indexes never read; drop them.
flowchart TD
  A["Slow query found in logs"] --> B["EXPLAIN it"]
  B --> C{"Seq Scan on a big table?"}
  C -- yes --> D["Add index matching where + orderBy"]
  C -- no --> E["Check row counts, select less, paginate"]
  D --> F["EXPLAIN again and compare"]

Always confirm with EXPLAIN: an index the planner ignores is pure cost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Prisma attribute creates a composite index on two fields?

    @@index takes a list of fields; use @@unique for a unique pair.
  2. 2. In PostgreSQL, are foreign key columns indexed automatically?

    PostgreSQL does not index the referencing column of a foreign key; you must.
  3. 3. A composite index on (status, createdAt) can efficiently serve which filter alone?

    The index is ordered by status first, so the leftmost column works on its own.
  4. 4. What does a Seq Scan in an EXPLAIN plan indicate?

    A sequential scan reads everything; on big tables it often means a missing or unusable index.

Summary

  • Indexes let the database find rows without scanning; declare them with @unique and @@index.
  • Index relation fields, frequent filters and sort columns, with equality columns first in composites.
  • Every index slows writes and uses space, so add them for measured needs.
  • Verify with EXPLAIN (or EXPLAIN ANALYZE) rather than guessing.
  • Avoid functions on indexed columns and leading-wildcard searches.

Next: Client Extensions