Lesson 28 / 35
Making ORM Queries Fast
Find slow queries from logs, read a query plan, add the right index, fetch less data and switch to cursor pagination for deep pages.
By Rahul Agarwal · 15 years in web development
Measure, then fix the SQL
ORM performance work is SQL performance work. Start by measuring: enable query logging or your database's slow-query log, and find the queries that run most often or take longest. Then ask the database how it executes one with EXPLAIN (PostgreSQL's EXPLAIN ANALYZE also runs it and reports real timings). A sequential scan over a large table for a selective filter usually means a missing index on the filtered or joined column. Next, fetch less: select only needed columns, avoid loading relations you do not render, and replace counting in JavaScript with a COUNT query. Finally, fix deep pagination: OFFSET 100000 makes the database walk past and discard a hundred thousand rows. Cursor (keyset) pagination remembers the last seen key and asks for WHERE id > :lastId ORDER BY id LIMIT n, which can use an index and stays fast on any page. The trade-off is that you cannot jump straight to page 57.
A performance and safety loop
Log the queries, find the slow or unsafe ones, fix them with an index, a narrower select or parameters, then verify the plan and timings again.
An index changes the plan
SQLite's EXPLAIN QUERY PLAN shows the same idea PostgreSQL's EXPLAIN does: a full scan before the index, an index search after it.
import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER, title TEXT)")
db.executemany("INSERT INTO posts (author_id, title) VALUES (?, ?)",
[(i % 1000, f'post {i}') for i in range(50000)])
q = "EXPLAIN QUERY PLAN SELECT id, title FROM posts WHERE author_id = 42"
print('before:', db.execute(q).fetchall()[0][3])
db.execute("CREATE INDEX idx_posts_author ON posts(author_id)")
print('after: ', db.execute(q).fetchall()[0][3])
Output:
before: SCAN posts after: SEARCH posts USING INDEX idx_posts_author (author_id=?)
Cursor pagination in Prisma
cursor starts at the last row of the previous page and skip: 1 excludes that row itself.
const firstPage = await prisma.post.findMany({ take: 20, orderBy: { id: 'asc' } });
// assumes the first page has rows; check firstPage.length in real code
const lastId = firstPage[firstPage.length - 1].id;
const nextPage = await prisma.post.findMany({
take: 20,
skip: 1,
cursor: { id: lastId },
orderBy: { id: 'asc' },
});Quick check: An infinite-scroll feed with millions of rows gets slower the further a user scrolls. It uses skip/take with large offsets. What is the best fix?
- Switch to cursor pagination on an indexed, ordered key
- Increase take so fewer pages are requested
- Remove ORDER BY to save sorting time
- Wrap each page query in a transaction
Answer
Switch to cursor pagination on an indexed, ordered key — Large offsets force the database to skip rows one by one. Keyset or cursor pagination seeks directly via the index, so every page costs about the same.