Prisma•2mo ago

Prisma gets very slow...

TLDR: My query gets massively slower when I add another join that also has another join in it. I have a query, which is pretty big here it is:

selectFullVenue = Prisma.validator<Prisma.VenueInclude>()({
    address: true,
    location: true,
    images: {.},
    events: {.},
    stars: {.},
    busyness: {.},
    busynessPredictions: {.},
    _count: {.},
    // openingHours: {
    //   orderBy: {
    //     timeStamp: 'desc'
    //   },
    //   take: 1,
    //   include: {
    //     hours: true
    //   }
    // },
    tags: {.}
    }
  })

selectFullVenue = Prisma.validator<Prisma.VenueInclude>()({
    address: true,
    location: true,
    images: {.},
    events: {.},
    stars: {.},
    busyness: {.},
    busynessPredictions: {.},
    _count: {.},
    // openingHours: {
    //   orderBy: {
    //     timeStamp: 'desc'
    //   },
    //   take: 1,
    //   include: {
    //     hours: true
    //   }
    // },
    tags: {.}
    }
  })

This query takes about 300ms and when I wrap it in a find many like below it takes about 1-2seconds.

const stars = await prisma.stars.findMany({
    include: {
      venue: {
        include: selectFullVenue
      }
    },
    where: {
      userId
    },
    relationLoadStrategy: 'join'
  })

const stars = await prisma.stars.findMany({
    include: {
      venue: {
        include: selectFullVenue
      }
    },
    where: {
      userId
    },
    relationLoadStrategy: 'join'
  })

This is slow but not the main problem I have. As you can see some of the query is commented out, when I uncomment this my query time on the 'find many' jumps to over one minuet! There are about 1m entries in openingHours and about 5m in openingHoursData. I feel like this massive jump in query time must be some missing index or potentially a bad query being generated from prisma? Any help would be appreciated! I have attached the explain plan and the graph for the non prod server which unfortunately does not have much data in it. The relevent part of my schema is also included.

explain_plan_1723720...

data-1723720880403.c...

section.prisma

2 Replies

Tyler Benfield•2mo ago

Hi @oliver bagin 👋 Just taking a quick look over your schema and the execution plan, it looks like you need to add another index for @@index([venueId, timeStamp]) . Index columns are utilized left-to-right. Sorting can utilize the index as long as all fields left of the sorted field are included in the where. So in this case, @@index([timeStamp, venueId]) doesn't match because there is no filter on timeStamp . This causes the database to prefer opening_hour_venue_id_idx for filtering, though that doesn't have the timeStamp and needs to perform sorting in memory. The order of the fields makes a surprisingly big difference!

oliver bagin•2mo ago

This was part of the problem! Thanks for the help tyler. The other issue was the 'where' was not selective enough meaning the select before the limit was returning too many records!

Gaming

Programming

Prisma gets very slow...