Как оптимизировать SQL запрос с множеством JOIN(ов)?

Question

[Artur Aralin]

Приветствую! У меня есть SQL запрос, который после выполнения возвращает более 110000 (что очень много).
Подскажите, как можно его оптимизировать? Или тут поможет только хеширование на уровне приложения?

Тут можно посмотреть результаты EXPLAIN https://pastebin.com/84jqy1wM

SELECT
  "user"."id", "user"."email", "user"."avatarUrl",
  "business"."businessName", "business"."address", "business"."siteUrl", "business"."businessType", "business"."blurb", "business"."contactPhone", "business"."businessPhone", "business"."businessEmail", "business"."instagramUsername", "business"."instagramLocation", "business"."contactName", "business"."contactPosition", "business"."panoPhotoUrl", "business"."accountState", "business"."booking", "business"."takeAway", "business"."delivery",
  "businessHours"."from" AS "businessHours.from", "businessHours"."to" AS "businessHours.to",
  "kitchenHours"."id" AS "kitchenHours.id", "kitchenHours"."from" AS "kitchenHours.from", "kitchenHours"."to" AS "kitchenHours.to",
  json_build_object('country', "address"."country", 'state', "address"."state", 'locality', "address"."locality", 'street', "address"."street", 'buildingNumber', "address"."buildingNumber", 'postcode', "address"."postcode", 'countryShort', "address"."countryShort", 'stateShort', "address"."stateShort", 'localityShort', "address"."localityShort", 'streetShort', "address"."streetShort", 'buildingNumberShort', "address"."buildingNumberShort", 'postcodeShort', "address"."postcodeShort", 'geoPosition', "address"."geoPosition") AS "address",
  "reviews",
-- Menu tags
  "mTags"."id" AS "menuTags.id",
  "mTags"."name" AS "menuTags.name",
-- Business features
  "businessFeatures"."id" AS "businessFeatures.id",
  "businessFeatures"."name" AS "businessFeatures.name",
-- Business cuisine
  "cuisine"."id" AS "cuisines.id",
  "cuisine"."name" AS "cuisines.name",
  -- Business subcategories
  "subcategories"."id" AS "subcategories.id",
  "subcategories"."name" AS "subcategories.name",
  "subcategories"."allowedCategories" AS "subcategories.allowedCategories",
  -- Business photo
  "businessPhoto"."id" AS "businessPhotos.id",
  "businessPhoto"."photoUrl" AS "businessPhotos.url",
  "businessPhoto"."index" AS "businessPhotos.index",
-- Business PDF menu
  "businessPdfMenu"."id" AS "businessPdfMenus.id",
  "businessPdfMenu"."pdfMenuUrl" AS "businessPdfMenus.url",
-- Email subscriptions
  "emailSubscription"."listId" AS "emailSubscriptions.listId"
FROM "User" AS "user"
INNER JOIN "Business" AS "business" ON "business"."id" = "user"."internalId"
INNER JOIN "BusinessHours" AS "businessHours" ON "businessHours"."businessId" = "user"."internalId"
INNER JOIN "KitchenHours" AS "kitchenHours" ON "kitchenHours"."businessId" = "user"."internalId"
INNER JOIN "EmailSubscription" AS "emailSubscription" ON "emailSubscription"."userId" = "user"."id"
INNER JOIN "BusinessPhoto" AS "businessPhoto" ON "businessPhoto"."businessId" = "user"."internalId"
INNER JOIN "BusinessPdfMenu" AS "businessPdfMenu" ON "businessPdfMenu"."businessId" = "user"."internalId"
INNER JOIN LATERAL (
  SELECT json_agg(json_build_object('id', "userReviews"."id", 'userId', "userReviews"."foodEnthusiastId",
    'comment', "userReviews"."comment", 'businessComment', "userReviews"."businessComment", 'likes', "Likes")) AS "reviews"
  FROM "UserReviews" "userReviews"
    LEFT JOIN LATERAL (
      SELECT json_agg("userReviewsLikes"."userId") AS "Likes"
      FROM "UserReviewsLikes" "userReviewsLikes"
      WHERE  "userReviewsLikes"."reviewId" = "userReviews"."id"
      ) "userReviewsLikes" ON true
  WHERE  "userReviews"."businessId" = "user"."internalId"
  ) "userReviews" ON true
INNER JOIN (
  "PickedBusinessTags" AS "pTags"
  JOIN "MenuTags" AS "mTags"
  ON "pTags"."tagId" = "mTags"."id"
) ON "user"."internalId" = "pTags"."businessId"
INNER JOIN (
  "PickedBusinessFeatures" AS "pFeatures"
  JOIN "BusinessFeatures" AS "businessFeatures"
  ON "pFeatures"."featureId" = "businessFeatures"."id"
) ON "user"."internalId" = "pFeatures"."businessId"
INNER JOIN (
  "PickedBusinessCuisine" AS "pCuisine"
  JOIN "Cuisine" AS "cuisine"
  ON "pCuisine"."cuisineId" = "cuisine"."id"
) ON "user"."internalId" = "pCuisine"."businessId"
INNER JOIN (
  "BusinessPickedSubcategories" AS "pSubcategories"
  JOIN "Subcategories" AS "subcategories"
  ON "pSubcategories"."subcategoryId" = "subcategories"."id"
) ON "user"."internalId" = "pSubcategories"."businessId"
INNER JOIN "Address" AS "address" ON "address"."id" = "business"."address"
WHERE "user"."id" = 24 and "user"."type" = 'business'

Answer 1

[ivankomolin]

Если нужно получать данные быстро, а актуальностью данных можно пренебречь на немного, то можно порциями(например по 5000) по крону писать в отдельно созданную таблицу, содержащую все необходимые поля.

А уже когда потребуется читать, то опять же порциями читать с помощью простого select из этой специально созданной таблицы.

Оптимизацию все же стоит провести, как минимум расставить ключи правильные, т.к. сервер то он все равно будет нагружать в момент чтения по крону.

А вот оптимизация самого запроса, в данном случае - титанический труд, т.к. сначала нужно разобраться со структурой чтобы предложить какую-то другую структуру запроса, где-то вообще будет проще сделать 2 запроса, а результаты потом программированием смержить и т.п.
Из того, что бросается в глаза это вложенные запросы. Но иногда они нужны. Думаю никто не даст вам хорошего совета не разобравшись со структурой и количеством данных в каждой таблице.

Answer 2

[Artur Aralin]

Отвечаю сам на свой вопрос. Запрос можно оптимизировать так (код по ссылке https://gist.github.com/ArturAralin/11397ae4c590c7... )