Postgres 16, about 120k rows in the child table. The call is findMany({ where: { orgId }, include: { items: true, tags: true, owner: true }, take: 50 }). pg_stat_statements shows four separate statements, and the slow one is a select on items with an IN list of 50 ids doing a sequential scan. The equivalent single query with joins that I wrote by hand runs in 80ms. Is this just what the ORM does?
Careful with the conclusion. 80ms versus 4.2s is not an ORM-versus-SQL result, it is an index-versus-no-index result — your handwritten query probably hit a different plan by accident. Rewriting the app in raw SQL on the same schema would have been fast today and slow again in three months.
That said, there is nothing wrong with a hybrid. The two or three heavy list endpoints get handwritten SQL through $queryRaw with a zod parse on the result, everything else stays ORM. Trying to express a reporting query through a relation API is how you end up with something nobody can read or explain.
One more trap in that snippet: take: 50 limits the parents, not the children. If one row has 4,000 tags you pull all of them into memory to build the response. Load big relations separately with their own limit.
Move to drizzle, it generates real joins instead of doing this.
Drizzle's relational queries also build a per-relation subquery shape by default, so you would have hit the same sequential scan with the same missing index. Migrating an ORM to fix an index is a lot of yak for one CREATE INDEX.
@layer_shift_lu · 2mo ago · 3 replies
Separate queries per relation is the default strategy, yes — it fetches parents, then children with an
IN, and stitches them in JS. That is usually fine. What is not fine is the sequential scan.Check for an index on
items.post_id. Prisma creates the foreign key constraint but not an index on the referencing column, and Postgres does not create one for you either. An unindexed FK plus anINlist of 50 is exactly the 4-second shape you are describing.Secondary: turn on the relation-join strategy if you want a single query with lateral joins, and stop using bare
includein favour ofselectso you are not shipping every column of three tables to render a list.Reply
Report
@gradschool_gary · 2mo ago
There was no index on
items.post_id. Added it, 4.2s to 110ms, no other changes. Feeling both relieved and stupid, which I gather is the standard outcome here.Reply
Report
@peer_review_pat · 2mo ago
It is the single most common slow query I get asked about. People add
@relationand assume the index came with it. Worth a one-off audit query listing FK columns with no index — most codebases have a few.Reply
Report