Join query is fast on 1st setup, but much slower on the other

We have two Postgres setups. The first one (v11) is on AWS RDS, the second one (v12) is on OVH CloudDB. Both contain the exact same indexes and tables. Both have 1GB of RAM and 1 vCPU.

When executing this query:

EXPLAIN (  analyze on,  buffers on,  verbose on)   SELECT     "User"."id",     "User"."firstName",     "User"."lastName",     "User"."phone",     "User"."biography",     "User"."email",     COUNT("skills"."id") AS "skillsCount",     COUNT("languagesList"."id") AS "languagesCount",     COUNT("certificates"."id") AS "certificatesCount",     COUNT("mobility"."id") AS "mobilityCount",     COUNT("studies"."id") AS "studiesCount",     COUNT("experience"."id") AS "experienceCount",     COUNT("address"."id") AS "addressCount",     COUNT (DISTINCT "chatMessages".id) AS "chatMessagesCount",     json_agg(     CASE        WHEN           appointments.id IS NULL         THEN           NULL         ELSE           json_build_object('id', appointments.id, 'validations', json_build_array(json_build_object('managerId', "appointments -> validations"."managerId", 'validated', "appointments -> validations".validated)), 'recruiterUser', json_build_object('id', "appointments -> recruiterUser".id, 'firstName', "appointments -> recruiterUser"."firstName", 'lastName', "appointments -> recruiterUser"."lastName", 'company', json_build_object('id', "appointments -> recruiterUser -> company".id, 'title', "appointments -> recruiterUser -> company".title)), 'day', appointments.day, 'time', appointments.time, 'status', appointments.status)      END  ) AS "meetings", json_agg(     CASE        WHEN           applications.id IS NULL         THEN           NULL         ELSE           json_build_object('id', applications.id, 'job', json_build_object('id', "applications -> job".id, 'title', "applications -> job".title), 'validations', json_build_array(json_build_object('managerId', "applications -> validations"."managerId", 'validated', "applications -> validations".validated)), 'cv', json_build_object('id', "applications -> cv".id, 'cvFile', "applications -> cv"."cvFile"))      END  ) AS "Applications", json_agg(     CASE        WHEN           visits.id IS NULL         THEN           NULL         ELSE           json_build_object('id', visits.id, 'time', visits.time, 'jobBoard', json_build_object('anonymized', "visits -> jobBoard".anonymized, 'subdomain', "visits -> jobBoard".subdomain))      END  ) AS "Visits", "validations"."id" AS "validations.id", "validations"."validated" AS "validations.validated", "validations"."jobBoardId" AS "validations.jobBoardId"   FROM     "Users" AS "User"      INNER JOIN        "UserVisits" AS "visits"         ON "User"."id" = "visits"."userId"      INNER JOIN        "JobBoards" AS "visits -> jobBoard"         ON "visits"."jobBoardId" = "visits -> jobBoard"."id"         AND "visits -> jobBoard"."creator" = 769      LEFT OUTER JOIN        (           "JobApplications" AS "applications"            INNER JOIN              "Jobs" AS "applications -> job"               ON "applications"."jobId" = "applications -> job"."id"            LEFT OUTER JOIN              "UsersCV" AS "applications -> cv"               ON "applications"."cvId" = "applications -> cv"."id"            LEFT OUTER JOIN              "JobApplicationsValidations" AS "applications -> validations"               ON "applications"."id" = "applications -> validations"."applicationId"         )        ON "User"."id" = "applications"."userId"      LEFT OUTER JOIN        (           "Appointments" AS "appointments"            INNER JOIN              "Users" AS "appointments -> recruiterUser"               ON "appointments"."recruiter" = "appointments -> recruiterUser"."id"            INNER JOIN              "Companies" AS "appointments -> recruiterUser -> company"               ON "appointments -> recruiterUser"."companyId" = "appointments -> recruiterUser -> company"."id"            LEFT OUTER JOIN              "AppointmentsValidations" AS "appointments -> validations"               ON "appointments"."id" = "appointments -> validations"."appointmentId"         )        ON "User"."id" = "appointments"."candidate"         AND "appointments"."type" = 'meeting'      LEFT OUTER JOIN        "ChatMessages" AS "chatMessages"         ON "User"."id" = "chatMessages"."userId"         AND NOT ("chatMessages"."userId" = 769)      LEFT OUTER JOIN        "CandidateSkills" AS "skills"         ON "User"."id" = "skills"."userId"      LEFT OUTER JOIN        "CandidateLanguage" AS "languagesList"         ON "User"."id" = "languagesList"."userId"      LEFT OUTER JOIN        "CandidateCertificates" AS "certificates"         ON "User"."id" = "certificates"."userId"      LEFT OUTER JOIN        "CandidateMobility" AS "mobility"         ON "User"."id" = "mobility"."userId"      LEFT OUTER JOIN        "CandidateDiplomas" AS "studies"         ON "User"."id" = "studies"."userId"      LEFT OUTER JOIN        "CandidateExperience" AS "experience"         ON "User"."id" = "experience"."userId"      LEFT OUTER JOIN        "Address" AS "address"         ON "User"."id" = "address"."userId"      LEFT OUTER JOIN        "UserValidations" AS "validations"         ON "User"."id" = "validations"."userId"         AND "validations"."managerId" = 769   WHERE     "User"."roleId" = ANY ('{5}')   GROUP BY     "User"."id", "visits"."time", "validations"."id"   ORDER BY     "User"."lastName" ASC LIMIT 50 OFFSET '0';  

The query takes up about 730ms on our AWS setup (which is fine for what we're trying to accomplish). However, when ran on OVH, it takes up about 3500ms.. way too slow!

Here are the EXPLAIN results for both queries:

AWS OVH

What could possibly cause this? Is this a ressources problem or one with my code?

https://stackoverflow.com/questions/66791618/join-query-is-fast-on-1st-setup-but-much-slower-on-the-other March 25, 2021 at 09:05AM