P
Prisma•2mo ago
Max

Duplicate joins & offsets in count expression

Hi! I'm doing some complex count operations and the query builder is making some decisions that I do not understand. Essentially, I'm doing this query: 
prisma.table.count({
  where: {
    OR: flag1
            ? undefined
            : [
                  {
                      fKey1: id
                  },
                  {
                      foreignTable1: { // Foreign key 1 points to this table
                          parentId: id
                      }
                  }
              ],
        tenantId: user.tenantId ?? undefined,
        fKey2: flag1 ? id : undefined,
        AND:
            aPermissionsCondition
                ? [
                      {
                          OR: [
                              {foreignTable3: {visibility: Visibility.PUBLIC}},
                              {foreignTable3: {visibility: Visibility.CREATOR_ONLY, userId: user.id}},
                              {fKey3: null}
                          ]
                      }
                  ]
                : [],
        anotherProperty: {
            in: anArray
        }
  }
})
prisma.table.count({
  where: {
    OR: flag1
            ? undefined
            : [
                  {
                      fKey1: id
                  },
                  {
                      foreignTable1: { // Foreign key 1 points to this table
                          parentId: id
                      }
                  }
              ],
        tenantId: user.tenantId ?? undefined,
        fKey2: flag1 ? id : undefined,
        AND:
            aPermissionsCondition
                ? [
                      {
                          OR: [
                              {foreignTable3: {visibility: Visibility.PUBLIC}},
                              {foreignTable3: {visibility: Visibility.CREATOR_ONLY, userId: user.id}},
                              {fKey3: null}
                          ]
                      }
                  ]
                : [],
        anotherProperty: {
            in: anArray
        }
  }
})
Very complex query, so I'd expect the count op to be complex, but I feel its a little un-optimized. This generates the following SQL (flag1 = false, so it's filtering based on foreignTable1):
5 Replies
Prisma AI Help
Prisma AI Help•2mo ago
You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!
Max
MaxOP•2mo ago
SELECT
  COUNT(*) AS "_count._all"
FROM
  (
    SELECT
      "schema"."table"."id" -- Why not just do SELECT COUNT(*) here?
    FROM
      "schema"."table"
      LEFT JOIN "schema"."foreignTable1" AS "j0" ON ("j0"."id") = ("schema"."table"."fKey1")
      LEFT JOIN "schema"."foreignTable3" AS "j1" ON ("j1"."id") = ("schema"."table"."fKey3")
      LEFT JOIN "schema"."foreignTable3" AS "j2" ON ("j2"."id") = ("schema"."table"."fKey3") -- WHY ARE THERE TWO JOINS TO THE EXACT SAME TABLE???
    WHERE
      (
        (
          "schema"."table"."fKey1" = $1 -- Indexed
          OR (
            "j0"."parentId" = $2 -- This is indexed
            AND "j0"."deleted" = $3 -- This is not indexed, but is a boolean
            AND ("j0"."id" IS NOT NULL) -- Does this affect performance in a meaningful way? Why is this here?
          )
        )
        AND "schema"."table"."tenantId" = $4 -- This is indexed
        AND (
          (
            "j1"."visibility" = CAST($5::TEXT AS "schema"."Visibility") -- Indexed
            AND "j1"."deleted" = $6
            AND ("j1"."id" IS NOT NULL)
          )
          OR (
            "j2"."visibility" = CAST($7::TEXT AS "schema"."Visibility")
            AND "j2"."userId" = $8 -- Indexed
            AND "j2"."deleted" = $9
            AND ("j2"."id" IS NOT NULL)
          )
          OR "schema"."table"."fKey3" IS NULL
        )
        AND "schema"."table"."anotherProperty" IN (
          CAST($10::TEXT AS "schema"."SomeEnum"), -- More values
        )
      )
    OFFSET
      $28 -- What the heck is this offset doing? Why is this here?
  ) AS "sub"
SELECT
  COUNT(*) AS "_count._all"
FROM
  (
    SELECT
      "schema"."table"."id" -- Why not just do SELECT COUNT(*) here?
    FROM
      "schema"."table"
      LEFT JOIN "schema"."foreignTable1" AS "j0" ON ("j0"."id") = ("schema"."table"."fKey1")
      LEFT JOIN "schema"."foreignTable3" AS "j1" ON ("j1"."id") = ("schema"."table"."fKey3")
      LEFT JOIN "schema"."foreignTable3" AS "j2" ON ("j2"."id") = ("schema"."table"."fKey3") -- WHY ARE THERE TWO JOINS TO THE EXACT SAME TABLE???
    WHERE
      (
        (
          "schema"."table"."fKey1" = $1 -- Indexed
          OR (
            "j0"."parentId" = $2 -- This is indexed
            AND "j0"."deleted" = $3 -- This is not indexed, but is a boolean
            AND ("j0"."id" IS NOT NULL) -- Does this affect performance in a meaningful way? Why is this here?
          )
        )
        AND "schema"."table"."tenantId" = $4 -- This is indexed
        AND (
          (
            "j1"."visibility" = CAST($5::TEXT AS "schema"."Visibility") -- Indexed
            AND "j1"."deleted" = $6
            AND ("j1"."id" IS NOT NULL)
          )
          OR (
            "j2"."visibility" = CAST($7::TEXT AS "schema"."Visibility")
            AND "j2"."userId" = $8 -- Indexed
            AND "j2"."deleted" = $9
            AND ("j2"."id" IS NOT NULL)
          )
          OR "schema"."table"."fKey3" IS NULL
        )
        AND "schema"."table"."anotherProperty" IN (
          CAST($10::TEXT AS "schema"."SomeEnum"), -- More values
        )
      )
    OFFSET
      $28 -- What the heck is this offset doing? Why is this here?
  ) AS "sub"
Do I have to manually optimize these queries? Or am i doing something wrong in my prisma calls? I'm sorry I have obfuscated this. Private code base and all, but I hope it can be followed! I am using relationship joins. Does this impact how count queries are performed?
Nurul
Nurul•5w ago
It seems that some of your questions are related to discussion in this GitHub Issue: https://github.com/prisma/prisma/issues/23891 Can you take a look?
GitHub
nested count, aggregate _count query inefficiency · Issue #23891 ...
Bug description When prisma builds count queries or aggregates for "_count" values why does it use IN to get values after joining when counting directly from the joined table would be muc...
Nurul
Nurul•5w ago
You can also use TypedSQL to create highly optimised queries: https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql
Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Docu...
Learn how to use TypedSQL to write fully type-safe SQL queries that are compatible with any SQL console and Prisma Client.
Max
MaxOP•5w ago
Thank you! I'm going to look into seeing if not using relation joins fixes the perf issues im seeing thank you!

Did you find this page helpful?