Profiling Queries with Prisma Query Insights

Step 30 of 31 — Next.js Tutorial Series | Source code

Live Demo →

---

Commands in This Step

Command	Purpose
npx prisma migrate dev --name add-post-indexes	Apply database migration to add indexes

---

What You Will Learn

↑ Index

Your blog app is fully deployed and working. But as it grows — more posts, more users, more queries — it will slow down. This step introduces the tools Prisma gives you to find and fix database performance problems before your users notice them.

In this step, you will:

1. Understand what Query Insights is and how it works
2. Use the Query Insights dashboard (built into Prisma Postgres — no setup needed)
3. Learn the most common query problems it detects: N+1 queries, missing indexes, over-fetching, and offset pagination
4. Apply the AI-generated fix recommendations

Goal: Know how to profile your Prisma queries and act on the performance data.

---

Table of Contents

1. What is Query Insights?
2. Reading the Query Insights dashboard
3. Common performance problems
4. N+1 queries and how to fix them
5. Missing indexes
6. Over-fetching with select
7. Offset vs cursor pagination
8. The AI recommendation workflow
9. Summary & Key Takeaways

---

What is Query Insights?

↑ Index

Query Insights is Prisma's built-in query profiling tool, included with Prisma Postgres at no extra cost. It requires no packages to install, no API keys, and no code changes — it works automatically with your existing Prisma Postgres database.

What about Prisma Optimize? You may see references to "Prisma Optimize" and the @prisma/extension-optimize package online or in older tutorials. From the official Prisma docs: "Query Insights replaces Prisma Optimize and is now included with Prisma Postgres at no extra cost." The @prisma/extension-optimize package only supports Prisma 5.x and 6.x — it does not work with Prisma 7.x (which our app uses). If your app runs on Prisma Postgres, Query Insights gives you the same capabilities with zero setup.

Since our app already uses Prisma Postgres, Query Insights is available right now. Open the Prisma Console and it is there.

Query Insights connects slow database queries back to the exact Prisma ORM call that generated them. It handles cases where one Prisma call expands into multiple SQL statements — and it tracks raw SQL queries too.

How to open it

1. Go to console.prisma.io
2. Select your project
3. Click Query Insights in the left sidebar

No instrumentation code, no agents, no extra packages required.

What it shows

Dashboard (top-level charts):

• Average query latency over time
• Queries per second
• Time-based charts with hover timestamps and playback controls

Query list:

• Per-query average latency, execution count, rows read
• Last-seen timestamp
• The SQL statement shape (with parameters redacted)
• Filterable by table, sortable by cost

Query detail view (click any query):

• Full execution stats
• The complete SQL statement
• An AI-generated analysis of the problem
• A copyable prompt you can paste into your editor to apply the fix

---

Reading the Query Insights dashboard

↑ Index

Step 1 — Generate some traffic

Query Insights needs real queries to show data. Run your app locally or use the Live Demo, then:

• Load the posts page several times
• Paginate through posts
• Open individual post detail pages
• Log in and create a post

Step 2 — Find expensive queries

In the Query Insights query list, sort by cost (latency × execution count). The most expensive queries float to the top. Look for:

• Any query with high average latency (> 100ms on a warm connection is a red flag)
• Any query that runs many times per page load (symptom of N+1)
• Queries that read a large number of rows compared to what they return

Step 3 — Open the detail view

Click a query to open the detail view. You will see:

Execution count:  147
Average latency:  38ms
Total latency:    5.6s
Rows read:        12,450
Rows returned:    147

A high rows read / rows returned ratio (like 12,450 : 147) usually means a full table scan — a missing index or an inefficient WHERE clause.

Step 4 — Read the AI analysis

Below the stats, Prisma generates a plain-English explanation:

"This query performs a sequential scan on the Post table. Adding an index on authorId would allow the database to skip directly to the relevant rows."

And a copyable prompt:

"Add a @@index([authorId]) to the Post model in schema.prisma."

---

Common performance problems

↑ Index

Query Insights detects the following categories of problems. Here is what to look for and how to fix each one.

Problem	Symptom	Fix
N+1 queries	1 query for a list + N more for related data	Use include or nested reads
Missing index	High rows-read / rows-returned ratio	Add @@index to schema.prisma
Over-fetching	Selecting all fields when you need 2	Use select to limit fields
Offset pagination	Slow LIMIT/OFFSET on large tables	Switch to cursor-based pagination
No query limit	Full table scan	Add .take(n) to all list queries

---

N+1 queries and how to fix them

↑ Index

The N+1 problem is the most common Prisma performance bug. It happens when you fetch a list and then make a separate database call for each item.

The problem

// ❌ N+1: 1 query to get posts + 1 query per post to get the author
const posts = await prisma.post.findMany()

for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  })
  console.log(post.title, author.name)
}

If there are 50 posts, this makes 51 database queries.

The fix — use include

// ✅ 1 query with a JOIN
const posts = await prisma.post.findMany({
  include: {
    author: true,
  },
})

Query Insights flags this automatically. In the query list you will see 51 queries with nearly identical shapes — a clear N+1 signal.

The fix — use select for partial data

// ✅ 1 query, only the fields you need
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: { name: true },
    },
  },
})

---

Missing indexes

↑ Index

When you query by a field that is not indexed, the database scans every row in the table. This is fine for small tables but becomes very slow as data grows.

Spotting it

In the query detail, you will see a high rows-read / rows-returned ratio and a message like:

"Sequential scan detected on Post.authorId. Consider adding an index."

Adding an index in Prisma

Open prisma/schema.prisma and add an @@index to the relevant model:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  authorId  Int
  createdAt DateTime @default(now())

  author    User     @relation(fields: [authorId], references: [id])

  @@index([authorId])        // ← add this
  @@index([createdAt])       // ← useful for ORDER BY createdAt DESC
}

Then run the migration:

npx prisma migrate dev --name add-post-indexes

When to add indexes

• Any field used in a WHERE clause on a large table
• Any field used in an ORDER BY clause
• Foreign key fields (like authorId) — Prisma does NOT add these automatically on PostgreSQL

---

Over-fetching with select

↑ Index

By default, prisma.post.findMany() returns every column in the Post table. If a post has a large content field and you only need id and title for a list page, you are transferring unnecessary data on every request.

The problem

// ❌ fetches all columns including large content field
const posts = await prisma.post.findMany({
  orderBy: { createdAt: 'desc' },
  take: 10,
})

The fix

// ✅ only the fields the list page actually uses
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    createdAt: true,
    author: {
      select: { name: true, image: true },
    },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
})

Query Insights flags queries where the data transferred per row is significantly larger than what the application reads.

---

Offset vs cursor pagination

↑ Index

Our tutorial uses URL-based pagination with skip and take. This is offset pagination and works well for small datasets. On large tables it becomes slow because the database must scan and discard the first N rows before returning the page you want.

Offset pagination (what we built)

// ❌ On page 100 with 10 items per page, the DB scans 1000 rows to skip 990
const posts = await prisma.post.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
})

Cursor pagination (the scalable alternative)

// ✅ The DB jumps directly to the cursor position
const posts = await prisma.post.findMany({
  take: pageSize,
  skip: cursor ? 1 : 0, // skip the cursor itself
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: 'desc' },
})

Cursor pagination is faster on large tables but requires a different UI (next/prev with a cursor token rather than page numbers). Query Insights flags slow OFFSET queries and recommends converting them.

---

The AI recommendation workflow

↑ Index

Query Insights includes an AI layer that analyses your queries and generates fix suggestions. Here is the workflow:

1. Open the query detail view
   └── Read the plain-English analysis

2. Copy the fix prompt
   └── The prompt is formatted for your editor / AI assistant

3. Paste it into your IDE or Prisma AI assistant
   └── It generates the schema change or query rewrite

4. Apply the fix
   └── Update schema.prisma, run migration, or update the query

5. Re-run your app
   └── Check Query Insights again — the expensive query should disappear
      or show a much lower latency

Using the Prisma AI assistant

Query Insights includes a chat interface where you can ask questions about specific recommendations:

• "Why is this query slow?"
• "Show me the schema change needed"
• "Rewrite this Prisma query to avoid the N+1"

It has full context of your schema and the recorded queries, so responses are specific to your app rather than generic.

---

Summary & Key Takeaways

↑ Index

Concept	Details
Query Insights	Built into Prisma Postgres — no packages, no API keys, no code changes. Dashboard at console.prisma.io
N+1 problem	Fetch list + loop with individual queries → fix with include or nested select
Missing indexes	High rows-read ratio → add @@index to schema.prisma and run migration
Over-fetching	Selecting all fields → use select to request only what you need
Offset pagination	Slow on large tables → consider cursor-based pagination with Prisma's cursor option
AI workflow	Open detail view → copy prompt → apply fix → verify in dashboard
No packages needed	Query Insights replaces the deprecated @prisma/extension-optimize — no install required

Further reading

• Query Insights docs →
• Prisma performance guide →