Query for empty relations is very slow for many-to-many relation #25731

vimutti77 · 2024-11-27T10:23:38Z

Bug description

I have a schema with many-to-many relation like this

model User {
  id     String  @id
  issues Issue[]
}

model Issue {
  id        String @id
  assignees User[]
}

I insert the data to database by using this SQL

INSERT INTO "User" ("id")
SELECT * FROM generate_series(1, 100) ;

INSERT INTO "Issue" ("id")
SELECT * FROM generate_series(1, 100000) ;

INSERT INTO "_IssueToUser" ("A", "B")
SELECT * FROM generate_series(1, 100000) AS A, generate_series(1, 100) AS B;

I want to count the Issue that have empty assignees by using prisma.issue.count({ where: { assignees: { none: {} } } })
But it takes about 1-2 mins to get the result.

How to reproduce

Create schema with many-to-many relation
Insert mock data
Count the items that relation is empty

Expected behavior

It should be fast

Prisma information

model User {
  id     String  @id
  issues Issue[]
}

model Issue {
  id        String @id
  assignees User[]
}

// Add your code using Prisma Client
prisma.issue.count({ where: { assignees: { none: {} } } })

Environment & setup

OS: Windows, Linux
Database: PostgreSQL
Node.js version: 20

Prisma Version

prisma                  : 5.22.0
@prisma/client          : 5.22.0
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v20.13.1
Query Engine (Node-API) : libquery-engine 605197351a3c8bdd595af2d2a9bc3025bca48ea2 (at node_modules\.pnpm\@[email protected]\node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli 605197351a3c8bdd595af2d2a9bc3025bca48ea2 (at node_modules\.pnpm\@[email protected]\node_modules\@prisma\engines\schema-engine-windows.exe)  
Schema Wasm             : @prisma/prisma-schema-wasm 5.22.0-44.605197351a3c8bdd595af2d2a9bc3025bca48ea2
Default Engines Hash    : 605197351a3c8bdd595af2d2a9bc3025bca48ea2
Studio                  : 0.503.0

The text was updated successfully, but these errors were encountered:

vimutti77 · 2024-11-28T06:55:02Z

The SQL from prisma is using NOT IN which is very slow in this case (total time ~1.30 mins).

SELECT COUNT(*)
FROM (
  SELECT "public"."Issue"."id"
  FROM "public"."Issue"
  WHERE ("public"."Issue"."id") NOT IN (
    SELECT "t1"."A"
	FROM "public"."_IssueToUser" AS "t1"
	INNER JOIN "public"."User" AS "j1"
	ON ("j1"."id") = ("t1"."B")
	WHERE (1=1 AND "t1"."A" IS NOT NULL)
  )
) AS "sub"

I modified it to use NOT EXISTS instead, which speeds up the query significantly (total time ~0.5 secs).

SELECT COUNT(*)
FROM (
  SELECT "public"."Issue"."id"
  FROM "public"."Issue"
  WHERE NOT EXISTS (
    SELECT "t1"."A"
	FROM "public"."_IssueToUser" AS "t1"
	INNER JOIN "public"."User" AS "j1"
	ON ("j1"."id") = ("t1"."B")
	WHERE ("public"."Issue"."id" = "t1"."A")
  )
) AS "sub"

vimutti77 · 2024-12-13T09:13:23Z

This issue may be related to #24524.
The problem is prisma using IN / NOT IN for filtering relationship which is very inefficient for large table.

jacek-prisma · 2025-01-08T13:53:49Z

Hi @vimutti77, @senorverde-tc and @Sjlver,
I've published a dev version which uses correlated subqueries for all relational filters, it'd be good to get some feedback on whether it performs better in typical user workloads, it's been published as:
prisma@6.3.0-integration-engines-6-3-0-1-feat-relation-exists-aff8bf73313e0df76046e671a2cb2d31491601a3.5

If you can offer some feedback on it that'd be great.

Sjlver · 2025-01-08T14:05:19Z

Hi @jacek-prisma, thanks so much for working on this!

I can give this a try mid next week, around Jan 15. To make sure I understand: I need to specify prisma@6.3.0-integration-engines-6-3-0-1-feat-relation-exists-aff8bf73313e0df76046e671a2cb2d31491601a3.3 in my package.json file as the prisma version? Do I need the same for @prisma/client?

jacek-prisma · 2025-01-08T14:09:24Z

Hi @Sjlver. yes, these are the two NPM packages:

prisma@6.3.0-integration-engines-6-3-0-1-feat-relation-exists-aff8bf73313e0df76046e671a2cb2d31491601a3.5
@prisma/client@6.3.0-integration-engines-6-3-0-1-feat-relation-exists-aff8bf73313e0df76046e671a2cb2d31491601a3.5

vimutti77 · 2025-01-09T06:38:06Z

@jacek-prisma I have upgraded to the version you mentioned, but it still uses IN. What else do I need to do?

Prisma Version

prisma                  : 6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a074425ccc05b5ec12b88ec141ba5dea21472b7.2
@prisma/client          : 6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a074425ccc05b5ec12b88ec141ba5dea21472b7.2
Computed binaryTarget   : windows
prisma                  : 6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a074425ccc05b5ec12b88ec141ba5dea21472b7.2
@prisma/client          : 6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a074425ccc05b5ec12b88ec141ba5dea21472b7.2
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v20.13.1
TypeScript              : 5.4.5
Query Engine (Node-API) : libquery-engine 9a074425ccc05b5ec12b88ec141ba5dea21472b7 (at node_modules\.pnpm\@prisma+engines@6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a0744_kt6xdhq7jmyzsepmxwuvshgjom\node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli 9a074425ccc05b5ec12b88ec141ba5dea21472b7 (at node_modules\.pnpm\@prisma+engines@6.3.0-integration-engines-6-3-0-2-integration-sql-nested-transactions5-9a0744_kt6xdhq7jmyzsepmxwuvshgjom\node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 6.3.0-2.integration-sql-nested-transactions5-9a074425ccc05b5ec12b88ec141ba5dea21472b7
Default Engines Hash    : 9a074425ccc05b5ec12b88ec141ba5dea21472b7
Studio                  : 0.503.0

jacek-prisma · 2025-01-09T09:42:42Z

Sorry @vimutti77 , I've listed the wrong version, I just updated the comment with the correct one!

vimutti77 · 2025-01-11T06:37:33Z

Thanks @jacek-prisma. I did some initial testing, and the query time was reduced to 0.5 seconds! I haven't encountered any issues yet.

vimutti77 added the kind/bug A reported bug. label Nov 27, 2024

jacek-prisma mentioned this issue Jan 7, 2025

feat: use exists for relational filters prisma/prisma-engines#5104

Merged

jacek-prisma closed this as completed in prisma/prisma-engines#5104 Jan 16, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Query for empty relations is very slow for many-to-many relation #25731

Query for empty relations is very slow for many-to-many relation #25731

vimutti77 commented Nov 27, 2024

vimutti77 commented Nov 28, 2024 •

edited

Loading

vimutti77 commented Dec 13, 2024

jacek-prisma commented Jan 8, 2025 •

edited

Loading

Sjlver commented Jan 8, 2025

jacek-prisma commented Jan 8, 2025 •

edited

Loading

vimutti77 commented Jan 9, 2025

jacek-prisma commented Jan 9, 2025

vimutti77 commented Jan 11, 2025

Query for empty relations is very slow for many-to-many relation #25731

Query for empty relations is very slow for many-to-many relation #25731

Comments

vimutti77 commented Nov 27, 2024

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

Prisma Version

vimutti77 commented Nov 28, 2024 • edited Loading

vimutti77 commented Dec 13, 2024

jacek-prisma commented Jan 8, 2025 • edited Loading

Sjlver commented Jan 8, 2025

jacek-prisma commented Jan 8, 2025 • edited Loading

vimutti77 commented Jan 9, 2025

jacek-prisma commented Jan 9, 2025

vimutti77 commented Jan 11, 2025

vimutti77 commented Nov 28, 2024 •

edited

Loading

jacek-prisma commented Jan 8, 2025 •

edited

Loading

jacek-prisma commented Jan 8, 2025 •

edited

Loading