We use Prisma to access a Postgres database. The query below runs very slowly. A simple modification to the SQL (also shown below) makes it orders of magnitude faster. Is there a way to tell Prisma to use the modified version. I’d like to avoid the slowdown, preferably without resorting to raw SQL.

The Prisma query:

this.prismaService.users.findMany({
  where: {
    events: {
      // they have not had activity since timeWindowEnd
      none: {
        timestamp: {
          gte: timeWindowEnd,
        },
      },
    },
  },
});

This generates the following SQL

SELECT *
FROM "public"."users" AS "t1"
WHERE (
        ("t1"."id") NOT IN (
            SELECT "t5"."user_id"
            FROM "public"."events" AS "t5"
            WHERE (
                    "t5"."timestamp" >= '2024-07-01T11:46:00'
                    AND "t5"."user_id" IS NOT NULL
                )
            -- GROUP BY "t5"."user_id"  -- This would fix the slowdown
        )
    )
ORDER BY "t1"."id" ASC

When executing the query, Postgres materializes the subquery. Since users can have thousands of events, doing so is very costly.

If I modify the subquery, adding the GROUP BY that is commented out above, then the intermediate result is much smaller, and the query completes almost instantly.

Is there a way to get the fast behavior without using raw SQL?