Code Monkey home page Code Monkey logo

Comments (7)

BracketJohn avatar BracketJohn commented on September 24, 2024 1

Could this potentially be related to #22596?

from prisma.

Weakky avatar Weakky commented on September 24, 2024 1

@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.

jkomyno avatar jkomyno commented on September 24, 2024

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.

chris-basis avatar chris-basis commented on September 24, 2024

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.

Weakky avatar Weakky commented on September 24, 2024

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.

apolanc avatar apolanc commented on September 24, 2024

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.

BracketJohn avatar BracketJohn commented on September 24, 2024

@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)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo 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.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.