Comments (7)
Could this potentially be related to #22596?
from prisma.
@BracketJohn I don't believe this issue to be related to #22596. @chris-basis is complaining about a query plan being slow. It's no longer the case for #22596 now that they've added an index. The query from #22596 takes 500ms instead of 300ms without relationJoins
. The remaining time is overhead from the Query Engine, which we must improve but isn't related to OP's problem. Here we're talking about a query plan that takes 3mn on EXPLAIN ANALYZE
.
I've spent significant time again trying to reproduce @chris-basis issue. I thought I had something at some point where the index scan would return 4.5M rows instead of 44k which was causing the heap scan to take forever, but after re-seeding it to test something, it no longer reproduces.
Here's the slow query plan I had at some point: https://explain.dalibo.com/plan/e647aacea9h8hd31
And what I'm getting now without having changed anything (at least I think so): https://explain.dalibo.com/plan/07ffc1dhe3b5f3ed
@chris-basis I need further help to move this forward. I have setup this reproduction repo https://github.com/Weakky/repro-23139 so that you can have a look at what I'm doing. It may be that the mock data I'm seeding is not representative of your own. If that's the case, could you please edit the seed.ts
file in a way that reproduces what you're seeing on prod?
Thank you
from prisma.
Hi @chris-basis, thanks for opening this issue.
Would you mind upgrading to [email protected]
and letting us know whether you still face the same kind of performance concerns?
Thank you.
from prisma.
Hey @jkomyno, looks like the same query plan on the newer version. Pasting the raw vs. generated query below.
Prisma version: 5.9.1
prisma:query
SELECT
account.id,
COALESCE(
JSONB_AGG(JSONB_BUILD_OBJECT('child_account_id', figure_formula_edge.child_account_id)),
'[]'
) as parent_edges
FROM
account
LEFT JOIN
figure_formula_edge
ON account.id = figure_formula_edge.parent_account_id AND figure_formula_edge.company_id = $1::uuid AND figure_formula_edge.scenario_id = $2::uuid
WHERE
account.company_id = $3::uuid
AND account.scenario_id = $4::uuid
GROUP BY
account.id
> Raw Query time: 1537.885498046875
Accounts: 7800
Edges: 187200
---
SELECT
"t1"."id",
"Account_parentEdges"."__prisma_data__" AS "parentEdges"
FROM
"public"."account" AS "t1"
LEFT JOIN LATERAL (
SELECT
COALESCE(
JSONB_AGG("__prisma_data__"),
'[]'
) AS "__prisma_data__"
FROM
(
SELECT
"t4"."__prisma_data__"
FROM
(
SELECT
JSONB_BUILD_OBJECT(
'child_account_id', "t3"."child_account_id"
) AS "__prisma_data__",
"t3"."company_id",
"t3"."scenario_id"
FROM
(
SELECT
"t2".*
FROM
"public"."figure_formula_edge" AS "t2"
WHERE
"t1"."id" = "t2"."parent_account_id"
/* root select */
) AS "t3"
/* inner select */
) AS "t4"
WHERE
(
"t4"."company_id" = $1
AND "t4"."scenario_id" = $2
)
/* middle select */
) AS "t5"
/* outer select */
) AS "Account_parentEdges" ON true
WHERE
(
"t1"."is_deleted" = $3
AND "t1"."company_id" = $4
AND "t1"."scenario_id" = $5
)
> ORM Query time: 212998.3642578125
Accounts: 7800
Edges: 187200
from prisma.
Hey @chris-basis,
Is there any way you could provide some kind of reproduction or more info about the statistics of your tables?
I've tried taking the datamodel you shared and seeded it with millions of rows. However, since the core issue seems to come from the filters on scenarioId
and companyId
, I'm failing to get a realistic reproduction where the filter filters enough rows to get to the perf issues you're seeing.
Thanks for the help 🙏
from prisma.
Hey there @chris-basis 👋🏼
Just pinging again: have you been able to look at @Weakky's comment above?
In order to proceed further, we need your help here. Thank you and hope to hear from you soon!
from prisma.
@apolanc have you checked out #22596? It contains reproductions + @chris-basis gave a thumbs up to my comment above mentioning it.
from prisma.
Related Issues (20)
- Creating and including a related object returns trimmed numbers with `relationJoins`
- `wasm-edge-light-loader.js` importing `node:crypto` instead of using `globalThis.crypto` HOT 11
- Insert or update an item without returning it HOT 3
- Performance problems with batched `findUnique()` HOT 6
- Can't use Prisma Client in Next.js middleware with `@prisma/adapter-pg` and `pg`, even locally HOT 13
- Docs include incorrect information regarding Vercel deployment fix HOT 3
- Add support for `pre`/`post` validation hooks in Prisma schema HOT 3
- Introspecting error with "Microsoft SQL Server 2019" database HOT 1
- Batched `findUnique()` is not chunked and exceeds parameter binding limits HOT 2
- Gin/Gist index operator classes brought in via PostgreSQL extensions
- Accelerate: "Invalid client engine type, please use `library` or `binary`" on Edge HOT 23
- Undefined "meta" when handling "PrismaClientKnownRequestError"
- Queries are getting executed sequentially even when using Interactive transactions API using Promise.all(). HOT 2
- Webstorm does not support prisma 5.15.0 version of preview functionality
- Is the time taken by a complex query in sql run using queryRaw and normal raw sql query the same or does Prisma have to run anything under the hood?
- `update`: Compile-time error is missing if suppliying non-objects to `data` argument
- Add full MongoDB queries to query spans
- findUnique behavior
- Accelerate breaks "PrismaClient is unable to run in this browser environment, or has been bundled for the browser" HOT 3
- Prisma (v5.12 & v5.14 & v5.15) client has errors accessing `cockroachdb` when using with cloudflare workers and the new `driverAdapter` preview feature HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from prisma.