Hey! I'm seeing some queries taking ages to load when doing select with distinct.
Prisma Client's distinct option does not use SQL SELECT DISTINCT. Instead, distinct uses: A SELECT query In-memory post-processing to select distinct
3 Replies
You opted for speed from this bot. Let's conjure up an answer right now!
You're right about the performance issue with Prisma's
distinct operation. By default, Prisma doesn't use SQL's native DISTINCT functionality but instead fetches all records and performs the distinct operation in memory, which can be very slow for large datasets.
This behavior is documented and has been a known limitation. For example, in one test case with 1.5M rows, a raw SQL distinct query took less than 10ms while Prisma's in-memory approach took 3 seconds Github Issue #26283.
The good news is that Prisma has recently added a preview feature called nativeDistinct in version 5.7.0 that pushes the distinct operation to the database layer for PostgreSQL databases. This can significantly improve performance Prisma Client API reference.
To enable this feature, add it to your Prisma schema:
There are some current limitations to be aware of:
1. It currently only works with PostgreSQL
2. It will revert to in-memory processing when used with orderBy
3. There are also issues when used with take and skip pagination
The Prisma team is working on expanding this feature to other databases like MySQL, SQLite, and SQL Server Github Issue #23846.
If you need immediate performance improvements and can't use the preview feature, you might consider using $queryRaw to write a native SQL query with DISTINCT.Can i get past this
Hey!
Which database are you using?
Is this happening on latest prisma version?