Node.js application slow response time with Prisma and PostgreSQL on large datasets
I've tried everything I can think of but I'm experiencing important response time issues in my Node.js application when querying a large dataset using Prisma with PostgreSQL... My `User` model has over 10,000 records, and the query takes around 4 seconds to return a list of users. I've tried using pagination and filtering, but the performance is still not optimal. Here's the Prisma query I'm using: ```javascript const users = await prisma.user.findMany({ take: 100, skip: page * 100, where: { isActive: true }, }); ``` I have verified that the database has the appropriate indexes set up, particularly on the `isActive` column. However, I still see a slow response, especially when I increase the `skip` parameter. I also checked the PostgreSQL execution plan using `EXPLAIN ANALYZE` and noticed that it's doing a sequential scan instead of using the index. Could it be that my query is not optimized for larger datasets? I've also experimented with different approaches, such as using raw SQL queries with `prisma.$queryRaw` to see if I could improve the performance, but the scenario continues. Here’s an example of the raw SQL query I tried: ```javascript const result = await prisma.$queryRaw`SELECT * FROM "User" WHERE "isActive" = TRUE LIMIT 100 OFFSET ${page * 100}`; ``` Despite these efforts, the response time has not improved significantly. Is there a better way to structure my queries or possibly refactor the data retrieval logic to enhance performance? I'm using Node.js v16.13 and Prisma v2.30 with PostgreSQL 13. Cheers for any assistance! I'm developing on Debian with Javascript.