DT
Drizzle Team•14mo ago
zerokelvin

Why is my query so much slower when filter by all four latitude/longitude bounds than just three?

When I uncomment out any of the latitude / longitude filters, the response latency spikes from ~50ms to 2000ms. (a similar query on prisma runs in just ~60ms, even with all filters.). Does anyone have an idea why? Or how I could fix this? Unfortunately, migrating to PostGIS is not an option at the moment 
  const facilities = await db.query.facility.findMany({
    orderBy: desc(facility.internalRating),
    limit: 1,
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });
  const facilities = await db.query.facility.findMany({
    orderBy: desc(facility.internalRating),
    limit: 1,
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });
7 Replies
Angelelz
Angelelz•14mo ago
That is a very hard question to ask without any context, like database size and indexes You should explain analyze that query to see where the bottleneck is
Deras
Deras•14mo ago
Drizzle is not ready for production when it comes for complex queries. Prisma is battle tested. I would suggest writing raw sql for this case.
zerokelvin
zerokelvin•14mo ago
@angelelz Is there a way I can get drizzle to print out the SQL query it created to see where the bottleneck might be?
Angelelz
Angelelz•14mo ago
one way is by passing true to the logger config field when instatiating drizzle All queries will get printed out to the console
zerokelvin
zerokelvin•13mo ago
Here's the query it generates:

message.txt

zerokelvin
zerokelvin•13mo ago
My guess is that the problem is with the excessive usage of 
coalesce(
                json_agg(
                    json_build_array
coalesce(
                json_agg(
                    json_build_array
kratious
kratious•13mo ago
In prisma the include/in queries are split up into separate queries. See here: https://www.prisma.io/docs/guides/performance-and-optimization/query-optimization-performance#solving-n1-with-include As you've seen in the logs, drizzle keeps these queries in one huge query, this leaves the responsibility on the DB planner on how the queries are ran, sometimes the planner is not smart enough to optimize such cases. Looking and thinking about the query, my intuition on how it would run fast would follow something like the following steps: 1. Filter out all facility rows based on the clauses in the section 
where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
2. Work on left joining the other tables from filtered facility rows In your case the planner is probably optimizing incorrectly and does it in this way: 1. Work on left joining all the other tables from the unfiltered facility rows 2. Filter out rows from previous step based on the where clauses What you could try is finding the facility you need first and then doing the joins in a separate query 
const filteredFacility = await db.query.facility.findFirst({ 
    orderBy: desc(facility.internalRating),
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

const joined = await db.query.facility.findFirst({
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: eq(facility.id, filteredFacility[0].id)
  });
const filteredFacility = await db.query.facility.findFirst({ 
    orderBy: desc(facility.internalRating),
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

const joined = await db.query.facility.findFirst({
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: eq(facility.id, filteredFacility[0].id)
  });
Want results from more Discord servers?
Add your server