Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Perf: Order detail query very slow on Postgres v16 #3037

Closed
michaelbromley opened this issue Aug 28, 2024 · 1 comment
Closed

Perf: Order detail query very slow on Postgres v16 #3037

michaelbromley opened this issue Aug 28, 2024 · 1 comment
Labels
type: bug 🐛 Something isn't working @vendure/core

Comments

@michaelbromley
Copy link
Member

Describe the bug
It was reported by several users that the order detail page in the Admin UI was loading very slowly. Response times of 10-15 seconds were being reported, for basic setups with no custom fields defined on Order/OrderLine entities.

I had never encountered this and then it turned out they were all using Postgres v16, whereas I've been using v12 in dev and v14 in prod.

To Reproduce

Run the following query against the Admin API

Order detail query 
query OrderDetailQuery($id: ID!) {
  order(id: $id) {
    ...OrderDetail
    __typename
  }
}

fragment Discount on Discount {
  adjustmentSource
  amount
  amountWithTax
  description
  type
  __typename
}

fragment OrderLine on OrderLine {
  id
  createdAt
  updatedAt
  featuredAsset {
    preview
    __typename
  }
  productVariant {
    id
    name
    sku
    trackInventory
    stockOnHand
    __typename
  }
  discounts {
    ...Discount
    __typename
  }
  fulfillmentLines {
    fulfillmentId
    quantity
    __typename
  }
  unitPrice
  unitPriceWithTax
  proratedUnitPrice
  proratedUnitPriceWithTax
  quantity
  orderPlacedQuantity
  linePrice
  lineTax
  linePriceWithTax
  discountedLinePrice
  discountedLinePriceWithTax
  __typename
}

fragment OrderAddress on OrderAddress {
  fullName
  company
  streetLine1
  streetLine2
  city
  province
  postalCode
  country
  countryCode
  phoneNumber
  __typename
}

fragment PaymentWithRefunds on Payment {
  id
  createdAt
  transactionId
  amount
  method
  state
  nextStates
  errorMessage
  metadata
  refunds {
    id
    createdAt
    state
    items
    adjustment
    total
    paymentId
    reason
    transactionId
    method
    metadata
    lines {
      orderLineId
      quantity
      __typename
    }
    __typename
  }
  __typename
}

fragment Fulfillment on Fulfillment {
  id
  state
  nextStates
  createdAt
  updatedAt
  method
  lines {
    orderLineId
    quantity
    __typename
  }
  trackingCode
  __typename
}

fragment OrderDetail on Order {
  id
  createdAt
  updatedAt
  type
  aggregateOrder {
    id
    code
    __typename
  }
  sellerOrders {
    id
    code
    channels {
      id
      code
      __typename
    }
    __typename
  }
  code
  state
  nextStates
  active
  couponCodes
  customer {
    id
    firstName
    lastName
    __typename
  }
  lines {
    ...OrderLine
    __typename
  }
  surcharges {
    id
    sku
    description
    price
    priceWithTax
    taxRate
    __typename
  }
  discounts {
    ...Discount
    __typename
  }
  promotions {
    id
    couponCode
    __typename
  }
  subTotal
  subTotalWithTax
  total
  totalWithTax
  currencyCode
  shipping
  shippingWithTax
  shippingLines {
    id
    discountedPriceWithTax
    shippingMethod {
      id
      code
      name
      fulfillmentHandlerCode
      description
      __typename
    }
    __typename
  }
  taxSummary {
    description
    taxBase
    taxRate
    taxTotal
    __typename
  }
  shippingAddress {
    ...OrderAddress
    __typename
  }
  billingAddress {
    ...OrderAddress
    __typename
  }
  payments {
    ...PaymentWithRefunds
    __typename
  }
  fulfillments {
    ...Fulfillment
    __typename
  }
  modifications {
    id
    createdAt
    isSettled
    priceChange
    note
    payment {
      id
      amount
      __typename
    }
    lines {
      orderLineId
      quantity
      __typename
    }
    refund {
      id
      paymentId
      total
      __typename
    }
    surcharges {
      id
      __typename
    }
    __typename
  }
  __typename
}

Here are some sample response times running on my local machine:

Version Response Times (ms or s) Average Response Time Difference
v12 173 ms, 174 ms, 160 ms, 148 ms, 366 ms, 163 ms, 168 ms 193.14 ms
v16 4 s, 4.4 s, 4.7 s, 4 s, 6 s, 4 s 4.52 s +4.326 s (4,326 ms)

Expected behavior

v16 should be the same as v12.

Environment (please complete the following information):

  • @vendure/core version: 2.3+
  • Nodejs version: any
  • Database (mysql/postgres etc): as above
@michaelbromley michaelbromley added type: bug 🐛 Something isn't working @vendure/core labels Aug 28, 2024
@michaelbromley
Copy link
Member Author

To investigate this issue I enabled TypeORM query logging and made the above GraphQL query.

I copied the resulting query from the log and ran it directly against the DB via PHPStorm.

Here's the full SQL query 
SELECT "order"."createdAt"                                                                      AS "order_createdAt",
       "order"."updatedAt"                                                                      AS "order_updatedAt",
       "order"."type"                                                                           AS "order_type",
       "order"."code"                                                                           AS "order_code",
       "order"."state"                                                                          AS "order_state",
       "order"."active"                                                                         AS "order_active",
       "order"."orderPlacedAt"                                                                  AS "order_orderPlacedAt",
       "order"."couponCodes"                                                                    AS "order_couponCodes",
       "order"."shippingAddress"                                                                AS "order_shippingAddress",
       "order"."billingAddress"                                                                 AS "order_billingAddress",
       "order"."currencyCode"                                                                   AS "order_currencyCode",
       "order"."id"                                                                             AS "order_id",
       "order"."aggregateOrderId"                                                               AS "order_aggregateOrderId",
       "order"."customerId"                                                                     AS "order_customerId",
       "order"."taxZoneId"                                                                      AS "order_taxZoneId",
       "order"."subTotal"                                                                       AS "order_subTotal",
       "order"."subTotalWithTax"                                                                AS "order_subTotalWithTax",
       "order"."shipping"                                                                       AS "order_shipping",
       "order"."shippingWithTax"                                                                AS "order_shippingWithTax",
       "order__order_lines"."createdAt"                                                         AS "order__order_lines_createdAt",
       "order__order_lines"."updatedAt"                                                         AS "order__order_lines_updatedAt",
       "order__order_lines"."quantity"                                                          AS "order__order_lines_quantity",
       "order__order_lines"."orderPlacedQuantity"                                               AS "order__order_lines_orderPlacedQuantity",
       "order__order_lines"."listPriceIncludesTax"                                              AS "order__order_lines_listPriceIncludesTax",
       "order__order_lines"."adjustments"                                                       AS "order__order_lines_adjustments",
       "order__order_lines"."taxLines"                                                          AS "order__order_lines_taxLines",
       "order__order_lines"."id"                                                                AS "order__order_lines_id",
       "order__order_lines"."sellerChannelId"                                                   AS "order__order_lines_sellerChannelId",
       "order__order_lines"."shippingLineId"                                                    AS "order__order_lines_shippingLineId",
       "order__order_lines"."productVariantId"                                                  AS "order__order_lines_productVariantId",
       "order__order_lines"."initialListPrice"                                                  AS "order__order_lines_initialListPrice",
       "order__order_lines"."listPrice"                                                         AS "order__order_lines_listPrice",
       "order__order_lines"."taxCategoryId"                                                     AS "order__order_lines_taxCategoryId",
       "order__order_lines"."featuredAssetId"                                                   AS "order__order_lines_featuredAssetId",
       "order__order_lines"."orderId"                                                           AS "order__order_lines_orderId",
       "order__order_lines__order__order_lines_featuredAsset"."createdAt"                       AS "order__order_lines__order__order_lines_featuredAsset_createdAt",
       "order__order_lines__order__order_lines_featuredAsset"."updatedAt"                       AS "order__order_lines__order__order_lines_featuredAsset_updatedAt",
       "order__order_lines__order__order_lines_featuredAsset"."name"                            AS "order__order_lines__order__order_lines_featuredAsset_name",
       "order__order_lines__order__order_lines_featuredAsset"."type"                            AS "order__order_lines__order__order_lines_featuredAsset_type",
       "order__order_lines__order__order_lines_featuredAsset"."mimeType"                        AS "order__order_lines__order__order_lines_featuredAsset_mimeType",
       "order__order_lines__order__order_lines_featuredAsset"."width"                           AS "order__order_lines__order__order_lines_featuredAsset_width",
       "order__order_lines__order__order_lines_featuredAsset"."height"                          AS "order__order_lines__order__order_lines_featuredAsset_height",
       "order__order_lines__order__order_lines_featuredAsset"."fileSize"                        AS "order__order_lines__order__order_lines_featuredAsset_fileSize",
       "order__order_lines__order__order_lines_featuredAsset"."source"                          AS "order__order_lines__order__order_lines_featuredAsset_source",
       "order__order_lines__order__order_lines_featuredAsset"."preview"                         AS "order__order_lines__order__order_lines_featuredAsset_preview",
       "order__order_lines__order__order_lines_featuredAsset"."focalPoint"                      AS "order__order_lines__order__order_lines_featuredAsset_focalPoint",
       "order__order_lines__order__order_lines_featuredAsset"."id"                              AS "order__order_lines__order__order_lines_featuredAsset_id",
       "order__order_lines__order__order_lines_productVariant"."createdAt"                      AS "order__order_lines__order__order_lines_productVariant_createdAt",
       "order__order_lines__order__order_lines_productVariant"."updatedAt"                      AS "order__order_lines__order__order_lines_productVariant_updatedAt",
       "order__order_lines__order__order_lines_productVariant"."deletedAt"                      AS "order__order_lines__order__order_lines_productVariant_deletedAt",
       "order__order_lines__order__order_lines_productVariant"."enabled"                        AS "order__order_lines__order__order_lines_productVariant_enabled",
       "order__order_lines__order__order_lines_productVariant"."sku"                            AS "order__order_lines__order__order_lines_productVariant_sku",
       "order__order_lines__order__order_lines_productVariant"."outOfStockThreshold"            AS "b3b2d6eefbbf54e2aa56f2f8bb9ab2ba62d66153",
       "order__order_lines__order__order_lines_productVariant"."useGlobalOutOfStockThreshold"   AS "cbf68c861889489e7e160acd8e7c09dd447a3734",
       "order__order_lines__order__order_lines_productVariant"."trackInventory"                 AS "6706c69b0860b12b05415b355faf7d8e3ab80f29",
       "order__order_lines__order__order_lines_productVariant"."id"                             AS "order__order_lines__order__order_lines_productVariant_id",
       "order__order_lines__order__order_lines_productVariant"."productId"                      AS "order__order_lines__order__order_lines_productVariant_productId",
       "order__order_lines__order__order_lines_productVariant"."featuredAssetId"                AS "3536b083fd1364798a8d95647c4b78d3737cd22a",
       "order__order_lines__order__order_lines_productVariant"."taxCategoryId"                  AS "e1a6b560e78290830d865665172e06f4790e9dfc",
       "e2ab70a1709fde614246a222e2828e59ce741e53"."createdAt"                                   AS "e2ab70a1709fde614246a222e2828e59ce741e53_createdAt",
       "e2ab70a1709fde614246a222e2828e59ce741e53"."updatedAt"                                   AS "e2ab70a1709fde614246a222e2828e59ce741e53_updatedAt",
       "e2ab70a1709fde614246a222e2828e59ce741e53"."name"                                        AS "e2ab70a1709fde614246a222e2828e59ce741e53_name",
       "e2ab70a1709fde614246a222e2828e59ce741e53"."isDefault"                                   AS "e2ab70a1709fde614246a222e2828e59ce741e53_isDefault",
       "e2ab70a1709fde614246a222e2828e59ce741e53"."id"                                          AS "e2ab70a1709fde614246a222e2828e59ce741e53_id",
       "order__order_shippingLines"."createdAt"                                                 AS "order__order_shippingLines_createdAt",
       "order__order_shippingLines"."updatedAt"                                                 AS "order__order_shippingLines_updatedAt",
       "order__order_shippingLines"."listPriceIncludesTax"                                      AS "order__order_shippingLines_listPriceIncludesTax",
       "order__order_shippingLines"."adjustments"                                               AS "order__order_shippingLines_adjustments",
       "order__order_shippingLines"."taxLines"                                                  AS "order__order_shippingLines_taxLines",
       "order__order_shippingLines"."id"                                                        AS "order__order_shippingLines_id",
       "order__order_shippingLines"."shippingMethodId"                                          AS "order__order_shippingLines_shippingMethodId",
       "order__order_shippingLines"."listPrice"                                                 AS "order__order_shippingLines_listPrice",
       "order__order_shippingLines"."orderId"                                                   AS "order__order_shippingLines_orderId",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."createdAt"                                   AS "0fb3797f9c89af87a52450c304b80753991e0b41_createdAt",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."updatedAt"                                   AS "0fb3797f9c89af87a52450c304b80753991e0b41_updatedAt",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."deletedAt"                                   AS "0fb3797f9c89af87a52450c304b80753991e0b41_deletedAt",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."code"                                        AS "0fb3797f9c89af87a52450c304b80753991e0b41_code",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."checker"                                     AS "0fb3797f9c89af87a52450c304b80753991e0b41_checker",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."calculator"                                  AS "0fb3797f9c89af87a52450c304b80753991e0b41_calculator",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."fulfillmentHandlerCode"                      AS "0fb3797f9c89af87a52450c304b80753991e0b41_fulfillmentHandlerCode",
       "0fb3797f9c89af87a52450c304b80753991e0b41"."id"                                          AS "0fb3797f9c89af87a52450c304b80753991e0b41_id",
       "order__order_aggregateOrder"."createdAt"                                                AS "order__order_aggregateOrder_createdAt",
       "order__order_aggregateOrder"."updatedAt"                                                AS "order__order_aggregateOrder_updatedAt",
       "order__order_aggregateOrder"."type"                                                     AS "order__order_aggregateOrder_type",
       "order__order_aggregateOrder"."code"                                                     AS "order__order_aggregateOrder_code",
       "order__order_aggregateOrder"."state"                                                    AS "order__order_aggregateOrder_state",
       "order__order_aggregateOrder"."active"                                                   AS "order__order_aggregateOrder_active",
       "order__order_aggregateOrder"."orderPlacedAt"                                            AS "order__order_aggregateOrder_orderPlacedAt",
       "order__order_aggregateOrder"."couponCodes"                                              AS "order__order_aggregateOrder_couponCodes",
       "order__order_aggregateOrder"."shippingAddress"                                          AS "order__order_aggregateOrder_shippingAddress",
       "order__order_aggregateOrder"."billingAddress"                                           AS "order__order_aggregateOrder_billingAddress",
       "order__order_aggregateOrder"."currencyCode"                                             AS "order__order_aggregateOrder_currencyCode",
       "order__order_aggregateOrder"."id"                                                       AS "order__order_aggregateOrder_id",
       "order__order_aggregateOrder"."aggregateOrderId"                                         AS "order__order_aggregateOrder_aggregateOrderId",
       "order__order_aggregateOrder"."customerId"                                               AS "order__order_aggregateOrder_customerId",
       "order__order_aggregateOrder"."taxZoneId"                                                AS "order__order_aggregateOrder_taxZoneId",
       "order__order_aggregateOrder"."subTotal"                                                 AS "order__order_aggregateOrder_subTotal",
       "order__order_aggregateOrder"."subTotalWithTax"                                          AS "order__order_aggregateOrder_subTotalWithTax",
       "order__order_aggregateOrder"."shipping"                                                 AS "order__order_aggregateOrder_shipping",
       "order__order_aggregateOrder"."shippingWithTax"                                          AS "order__order_aggregateOrder_shippingWithTax",
       "order__order_sellerOrders"."createdAt"                                                  AS "order__order_sellerOrders_createdAt",
       "order__order_sellerOrders"."updatedAt"                                                  AS "order__order_sellerOrders_updatedAt",
       "order__order_sellerOrders"."type"                                                       AS "order__order_sellerOrders_type",
       "order__order_sellerOrders"."code"                                                       AS "order__order_sellerOrders_code",
       "order__order_sellerOrders"."state"                                                      AS "order__order_sellerOrders_state",
       "order__order_sellerOrders"."active"                                                     AS "order__order_sellerOrders_active",
       "order__order_sellerOrders"."orderPlacedAt"                                              AS "order__order_sellerOrders_orderPlacedAt",
       "order__order_sellerOrders"."couponCodes"                                                AS "order__order_sellerOrders_couponCodes",
       "order__order_sellerOrders"."shippingAddress"                                            AS "order__order_sellerOrders_shippingAddress",
       "order__order_sellerOrders"."billingAddress"                                             AS "order__order_sellerOrders_billingAddress",
       "order__order_sellerOrders"."currencyCode"                                               AS "order__order_sellerOrders_currencyCode",
       "order__order_sellerOrders"."id"                                                         AS "order__order_sellerOrders_id",
       "order__order_sellerOrders"."aggregateOrderId"                                           AS "order__order_sellerOrders_aggregateOrderId",
       "order__order_sellerOrders"."customerId"                                                 AS "order__order_sellerOrders_customerId",
       "order__order_sellerOrders"."taxZoneId"                                                  AS "order__order_sellerOrders_taxZoneId",
       "order__order_sellerOrders"."subTotal"                                                   AS "order__order_sellerOrders_subTotal",
       "order__order_sellerOrders"."subTotalWithTax"                                            AS "order__order_sellerOrders_subTotalWithTax",
       "order__order_sellerOrders"."shipping"                                                   AS "order__order_sellerOrders_shipping",
       "order__order_sellerOrders"."shippingWithTax"                                            AS "order__order_sellerOrders_shippingWithTax",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."createdAt"              AS "c7dba4b2e0e3c7b5b73edc310ff7c2d9ddd870d5",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."updatedAt"              AS "6f0d0ee5224b463a99d647917c2671b9889729ce",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."code"                   AS "98b37086c5e91b6e60f465211c1feb6698652d4a",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."token"                  AS "583a32f3edf1c2c8c13517546e8c112504cc1395",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."description"            AS "218c60533df29a2711a5a796971c3d4d31a8403b",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."defaultLanguageCode"    AS "3325f2823dd7e3f5a9349f8a41256c48d083f019",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."availableLanguageCodes" AS "d0c9a0f21b905249c7f42df3f8887b1df883f68e",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."defaultCurrencyCode"    AS "12b43eef16dfd4211727a6d8155344ccab6d7170",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."availableCurrencyCodes" AS "84fdc82071356810304e9400a1ac485db0dc7e4b",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."trackInventory"         AS "1e5a27cf65d8a83b7e69fc8c4f1610a54cfc5acb",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."outOfStockThreshold"    AS "97a8a6d34b616134da63ef9f9affd00b16172752",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."pricesIncludeTax"       AS "393cda01c18e6ca1de597f9ff8e635eab7576b60",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."id"                     AS "0e7cbe18390c1cb4e87eb0d536af861a02fb986b",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."sellerId"               AS "3e85d2b29f9b3246ad51382c12ccaf48cfe134b8",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."defaultTaxZoneId"       AS "eb6b18b7c4f8801a602d19bc67f20e5f59f613c4",
       "order__order_sellerOrders__order__order_sellerOrders_channels"."defaultShippingZoneId"  AS "991d7850321577b05cc2bbafb34d1d80fde77116",
       "order__order_customer"."createdAt"                                                      AS "order__order_customer_createdAt",
       "order__order_customer"."updatedAt"                                                      AS "order__order_customer_updatedAt",
       "order__order_customer"."deletedAt"                                                      AS "order__order_customer_deletedAt",
       "order__order_customer"."title"                                                          AS "order__order_customer_title",
       "order__order_customer"."firstName"                                                      AS "order__order_customer_firstName",
       "order__order_customer"."lastName"                                                       AS "order__order_customer_lastName",
       "order__order_customer"."phoneNumber"                                                    AS "order__order_customer_phoneNumber",
       "order__order_customer"."emailAddress"                                                   AS "order__order_customer_emailAddress",
       "order__order_customer"."id"                                                             AS "order__order_customer_id",
       "order__order_customer"."userId"                                                         AS "order__order_customer_userId",
       "order__order_surcharges"."createdAt"                                                    AS "order__order_surcharges_createdAt",
       "order__order_surcharges"."updatedAt"                                                    AS "order__order_surcharges_updatedAt",
       "order__order_surcharges"."description"                                                  AS "order__order_surcharges_description",
       "order__order_surcharges"."listPriceIncludesTax"                                         AS "order__order_surcharges_listPriceIncludesTax",
       "order__order_surcharges"."sku"                                                          AS "order__order_surcharges_sku",
       "order__order_surcharges"."taxLines"                                                     AS "order__order_surcharges_taxLines",
       "order__order_surcharges"."id"                                                           AS "order__order_surcharges_id",
       "order__order_surcharges"."listPrice"                                                    AS "order__order_surcharges_listPrice",
       "order__order_surcharges"."orderId"                                                      AS "order__order_surcharges_orderId",
       "order__order_surcharges"."orderModificationId"                                          AS "order__order_surcharges_orderModificationId",
       "order__order_promotions"."createdAt"                                                    AS "order__order_promotions_createdAt",
       "order__order_promotions"."updatedAt"                                                    AS "order__order_promotions_updatedAt",
       "order__order_promotions"."deletedAt"                                                    AS "order__order_promotions_deletedAt",
       "order__order_promotions"."startsAt"                                                     AS "order__order_promotions_startsAt",
       "order__order_promotions"."endsAt"                                                       AS "order__order_promotions_endsAt",
       "order__order_promotions"."couponCode"                                                   AS "order__order_promotions_couponCode",
       "order__order_promotions"."perCustomerUsageLimit"                                        AS "order__order_promotions_perCustomerUsageLimit",
       "order__order_promotions"."usageLimit"                                                   AS "order__order_promotions_usageLimit",
       "order__order_promotions"."enabled"                                                      AS "order__order_promotions_enabled",
       "order__order_promotions"."conditions"                                                   AS "order__order_promotions_conditions",
       "order__order_promotions"."actions"                                                      AS "order__order_promotions_actions",
       "order__order_promotions"."priorityScore"                                                AS "order__order_promotions_priorityScore",
       "order__order_promotions"."id"                                                           AS "order__order_promotions_id",
       "order__order_payments"."createdAt"                                                      AS "order__order_payments_createdAt",
       "order__order_payments"."updatedAt"                                                      AS "order__order_payments_updatedAt",
       "order__order_payments"."method"                                                         AS "order__order_payments_method",
       "order__order_payments"."state"                                                          AS "order__order_payments_state",
       "order__order_payments"."errorMessage"                                                   AS "order__order_payments_errorMessage",
       "order__order_payments"."transactionId"                                                  AS "order__order_payments_transactionId",
       "order__order_payments"."metadata"                                                       AS "order__order_payments_metadata",
       "order__order_payments"."id"                                                             AS "order__order_payments_id",
       "order__order_payments"."amount"                                                         AS "order__order_payments_amount",
       "order__order_payments"."orderId"                                                        AS "order__order_payments_orderId",
       "order__order_payments__order__order_payments_refunds"."createdAt"                       AS "order__order_payments__order__order_payments_refunds_createdAt",
       "order__order_payments__order__order_payments_refunds"."updatedAt"                       AS "order__order_payments__order__order_payments_refunds_updatedAt",
       "order__order_payments__order__order_payments_refunds"."method"                          AS "order__order_payments__order__order_payments_refunds_method",
       "order__order_payments__order__order_payments_refunds"."reason"                          AS "order__order_payments__order__order_payments_refunds_reason",
       "order__order_payments__order__order_payments_refunds"."state"                           AS "order__order_payments__order__order_payments_refunds_state",
       "order__order_payments__order__order_payments_refunds"."transactionId"                   AS "ecc31b363ecca2bec7745e5e2d7fafb98a9ed21c",
       "order__order_payments__order__order_payments_refunds"."metadata"                        AS "order__order_payments__order__order_payments_refunds_metadata",
       "order__order_payments__order__order_payments_refunds"."id"                              AS "order__order_payments__order__order_payments_refunds_id",
       "order__order_payments__order__order_payments_refunds"."paymentId"                       AS "order__order_payments__order__order_payments_refunds_paymentId",
       "order__order_payments__order__order_payments_refunds"."items"                           AS "order__order_payments__order__order_payments_refunds_items",
       "order__order_payments__order__order_payments_refunds"."shipping"                        AS "order__order_payments__order__order_payments_refunds_shipping",
       "order__order_payments__order__order_payments_refunds"."adjustment"                      AS "order__order_payments__order__order_payments_refunds_adjustment",
       "order__order_payments__order__order_payments_refunds"."total"                           AS "order__order_payments__order__order_payments_refunds_total",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."createdAt"                                   AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_createdAt",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."updatedAt"                                   AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_updatedAt",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."quantity"                                    AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_quantity",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."id"                                          AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_id",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."orderLineId"                                 AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_orderLineId",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."refundId"                                    AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_refundId",
       "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."discriminator"                               AS "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca_discriminator",
       "order__order_fulfillments"."createdAt"                                                  AS "order__order_fulfillments_createdAt",
       "order__order_fulfillments"."updatedAt"                                                  AS "order__order_fulfillments_updatedAt",
       "order__order_fulfillments"."state"                                                      AS "order__order_fulfillments_state",
       "order__order_fulfillments"."trackingCode"                                               AS "order__order_fulfillments_trackingCode",
       "order__order_fulfillments"."method"                                                     AS "order__order_fulfillments_method",
       "order__order_fulfillments"."handlerCode"                                                AS "order__order_fulfillments_handlerCode",
       "order__order_fulfillments"."id"                                                         AS "order__order_fulfillments_id",
       "order__order_fulfillments__order__order_fulfillments_lines"."createdAt"                 AS "ac1a760bdd192f26a354d30de1f2c0dde56cbe1c",
       "order__order_fulfillments__order__order_fulfillments_lines"."updatedAt"                 AS "de46d689dc23f8ec9aa9c0e40923d940ff512966",
       "order__order_fulfillments__order__order_fulfillments_lines"."quantity"                  AS "2ae7bece64931b369def99a528e61e0d4dbdef97",
       "order__order_fulfillments__order__order_fulfillments_lines"."id"                        AS "order__order_fulfillments__order__order_fulfillments_lines_id",
       "order__order_fulfillments__order__order_fulfillments_lines"."fulfillmentId"             AS "fa1f4527ea5df2f38c2f0892ed2661208aa24a2d",
       "order__order_fulfillments__order__order_fulfillments_lines"."orderLineId"               AS "cdce9c9d74d2fcd98f96f911a249cc417a96ea03",
       "order__order_fulfillments__order__order_fulfillments_lines"."discriminator"             AS "8fc2e1df857fc7d3e410748d4f90eaecc94cf2e0",
       "order__order_modifications"."createdAt"                                                 AS "order__order_modifications_createdAt",
       "order__order_modifications"."updatedAt"                                                 AS "order__order_modifications_updatedAt",
       "order__order_modifications"."note"                                                      AS "order__order_modifications_note",
       "order__order_modifications"."shippingAddressChange"                                     AS "order__order_modifications_shippingAddressChange",
       "order__order_modifications"."billingAddressChange"                                      AS "order__order_modifications_billingAddressChange",
       "order__order_modifications"."id"                                                        AS "order__order_modifications_id",
       "order__order_modifications"."priceChange"                                               AS "order__order_modifications_priceChange",
       "order__order_modifications"."orderId"                                                   AS "order__order_modifications_orderId",
       "order__order_modifications"."paymentId"                                                 AS "order__order_modifications_paymentId",
       "order__order_modifications"."refundId"                                                  AS "order__order_modifications_refundId",
       "order__order_modifications__order__order_modifications_payment"."createdAt"             AS "4f6483ed527596916591d0f1690d020b2eeb7d33",
       "order__order_modifications__order__order_modifications_payment"."updatedAt"             AS "82bc0d99d9489b1a8332145a114ee6731e7e06dd",
       "order__order_modifications__order__order_modifications_payment"."method"                AS "944edc66349f0095bb2b851cd1059b195b628248",
       "order__order_modifications__order__order_modifications_payment"."state"                 AS "e6f07ee1daf903f80258f0bbf41a58d12bc90f61",
       "order__order_modifications__order__order_modifications_payment"."errorMessage"          AS "2cc1e3942403ba017a73d9291c6b10cdae206354",
       "order__order_modifications__order__order_modifications_payment"."transactionId"         AS "e5f0942ce8d312bcc04e5d20c924548c51e45ca6",
       "order__order_modifications__order__order_modifications_payment"."metadata"              AS "eddaa15f170b933d5bb4f4d37db975b5947f24cc",
       "order__order_modifications__order__order_modifications_payment"."id"                    AS "071ce31de64401c0af7be856dcb0f9d5275a4a8d",
       "order__order_modifications__order__order_modifications_payment"."amount"                AS "1232bcfdd360c4168cd16d82052816a6d7366d5a",
       "order__order_modifications__order__order_modifications_payment"."orderId"               AS "d57913d1c664b90e809145033340a734b901639e",
       "order__order_modifications__order__order_modifications_lines"."createdAt"               AS "916ac54afcf2af749fd16d9ae1533efb778e8b94",
       "order__order_modifications__order__order_modifications_lines"."updatedAt"               AS "99e1a292fa1e90d0b33bbb5dce631c7ad8c0d06b",
       "order__order_modifications__order__order_modifications_lines"."quantity"                AS "31a74e373d633e3c2067db6a7055f1078cf4eba8",
       "order__order_modifications__order__order_modifications_lines"."id"                      AS "order__order_modifications__order__order_modifications_lines_id",
       "order__order_modifications__order__order_modifications_lines"."modificationId"          AS "4b697954f4467427bbb361fa6f170038e40dafdf",
       "order__order_modifications__order__order_modifications_lines"."orderLineId"             AS "c7160216e201ce7414b595146cd1045c9794575b",
       "order__order_modifications__order__order_modifications_lines"."discriminator"           AS "d3ad6d688de46fdea960a464ea5a28584f3412d8",
       "order__order_modifications__order__order_modifications_refund"."createdAt"              AS "436ee055c4ab675f375f5748dc71123ae78f8151",
       "order__order_modifications__order__order_modifications_refund"."updatedAt"              AS "369333f6687f39485779b0846e5de0ad057b5f6e",
       "order__order_modifications__order__order_modifications_refund"."method"                 AS "ee8cb67c1d995f3f1ef3f3e11de7d193eae229f2",
       "order__order_modifications__order__order_modifications_refund"."reason"                 AS "203edc4d09f1585a57bf93fceabae739fabbaea3",
       "order__order_modifications__order__order_modifications_refund"."state"                  AS "06968c7e6366d10021ad6a1afa71bc1cc632b49f",
       "order__order_modifications__order__order_modifications_refund"."transactionId"          AS "827dc7aad85278c0c3a3fa51cd568692205bdbb5",
       "order__order_modifications__order__order_modifications_refund"."metadata"               AS "3da440fdbce7b3fd0b7a730145211d431e7635ff",
       "order__order_modifications__order__order_modifications_refund"."id"                     AS "53ea7567e6ebf9b18c866c98a660b824901e7516",
       "order__order_modifications__order__order_modifications_refund"."paymentId"              AS "b2546c4ed661248543097170799faad77c484434",
       "order__order_modifications__order__order_modifications_refund"."items"                  AS "a0c2413a8314ec495af08a5d67271eafa3c9a355",
       "order__order_modifications__order__order_modifications_refund"."shipping"               AS "aefa1c55c48ea06c77eb52088303de3f60778d04",
       "order__order_modifications__order__order_modifications_refund"."adjustment"             AS "7b19750fb8fbe3d78af922184d71132043b58354",
       "order__order_modifications__order__order_modifications_refund"."total"                  AS "a169d6630b36f02089ada7754ebc64d26dbfeaab",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."createdAt"                                   AS "0218e86292ca999e1315efd412cd2bb9caae01c5_createdAt",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."updatedAt"                                   AS "0218e86292ca999e1315efd412cd2bb9caae01c5_updatedAt",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."description"                                 AS "0218e86292ca999e1315efd412cd2bb9caae01c5_description",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."listPriceIncludesTax"                        AS "0218e86292ca999e1315efd412cd2bb9caae01c5_listPriceIncludesTax",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."sku"                                         AS "0218e86292ca999e1315efd412cd2bb9caae01c5_sku",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."taxLines"                                    AS "0218e86292ca999e1315efd412cd2bb9caae01c5_taxLines",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."id"                                          AS "0218e86292ca999e1315efd412cd2bb9caae01c5_id",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."listPrice"                                   AS "0218e86292ca999e1315efd412cd2bb9caae01c5_listPrice",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."orderId"                                     AS "0218e86292ca999e1315efd412cd2bb9caae01c5_orderId",
       "0218e86292ca999e1315efd412cd2bb9caae01c5"."orderModificationId"                         AS "0218e86292ca999e1315efd412cd2bb9caae01c5_orderModificationId",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."createdAt"                                   AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_createdAt",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."updatedAt"                                   AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_updatedAt",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."currencyCode"                                AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_currencyCode",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."id"                                          AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_id",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."channelId"                                   AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_channelId",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."price"                                       AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_price",
       "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."variantId"                                   AS "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8_variantId",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."createdAt"                                   AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_createdAt",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."updatedAt"                                   AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_updatedAt",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."languageCode"                                AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_languageCode",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."name"                                        AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_name",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."id"                                          AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_id",
       "9b359819a0693cdae1783b7021fcdfc7f73bc963"."baseId"                                      AS "9b359819a0693cdae1783b7021fcdfc7f73bc963_baseId",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."createdAt"                                   AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_createdAt",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."updatedAt"                                   AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_updatedAt",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."languageCode"                                AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_languageCode",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."name"                                        AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_name",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."description"                                 AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_description",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."id"                                          AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_id",
       "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."baseId"                                      AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_baseId",
       "order__order_customer__order__order_customer_user"."createdAt"                          AS "order__order_customer__order__order_customer_user_createdAt",
       "order__order_customer__order__order_customer_user"."updatedAt"                          AS "order__order_customer__order__order_customer_user_updatedAt",
       "order__order_customer__order__order_customer_user"."deletedAt"                          AS "order__order_customer__order__order_customer_user_deletedAt",
       "order__order_customer__order__order_customer_user"."identifier"                         AS "order__order_customer__order__order_customer_user_identifier",
       "order__order_customer__order__order_customer_user"."verified"                           AS "order__order_customer__order__order_customer_user_verified",
       "order__order_customer__order__order_customer_user"."lastLogin"                          AS "order__order_customer__order__order_customer_user_lastLogin",
       "order__order_customer__order__order_customer_user"."id"                                 AS "order__order_customer__order__order_customer_user_id",
       "order__order_promotions__order__order_promotions_translations"."createdAt"              AS "8b647e5938acb1c022c15d8c2aeedc9909ddd9bf",
       "order__order_promotions__order__order_promotions_translations"."updatedAt"              AS "66c115134ed64a1a0891bc0c54d55b03aa9fcb58",
       "order__order_promotions__order__order_promotions_translations"."languageCode"           AS "d4d9504cfffef8ab7ed0ecf3a998f0f58dada26b",
       "order__order_promotions__order__order_promotions_translations"."name"                   AS "de6945323ed73bc416a76d16ea8b8c9ccc8613b3",
       "order__order_promotions__order__order_promotions_translations"."description"            AS "f36f4eb3502bf05c640552458806333c498ff948",
       "order__order_promotions__order__order_promotions_translations"."id"                     AS "dfaa47c4f400679919ec887bc84d27df86c6d71e",
       "order__order_promotions__order__order_promotions_translations"."baseId"                 AS "ec49eba2e201da6c035579e0f40392a79867c761"
FROM "public"."order" "order"
         LEFT JOIN "public"."order_line" "order__order_lines" ON "order__order_lines"."orderId" = "order"."id"
         LEFT JOIN "public"."asset" "order__order_lines__order__order_lines_featuredAsset"
                   ON "order__order_lines__order__order_lines_featuredAsset"."id" =
                      "order__order_lines"."featuredAssetId"
         LEFT JOIN "public"."product_variant" "order__order_lines__order__order_lines_productVariant"
                   ON "order__order_lines__order__order_lines_productVariant"."id" =
                      "order__order_lines"."productVariantId"
         LEFT JOIN "public"."tax_category" "e2ab70a1709fde614246a222e2828e59ce741e53"
                   ON "e2ab70a1709fde614246a222e2828e59ce741e53"."id" =
                      "order__order_lines__order__order_lines_productVariant"."taxCategoryId"
         LEFT JOIN "public"."shipping_line" "order__order_shippingLines"
                   ON "order__order_shippingLines"."orderId" = "order"."id"
         LEFT JOIN "public"."shipping_method" "0fb3797f9c89af87a52450c304b80753991e0b41"
                   ON "0fb3797f9c89af87a52450c304b80753991e0b41"."id" = "order__order_shippingLines"."shippingMethodId"
         LEFT JOIN "public"."order" "order__order_aggregateOrder"
                   ON "order__order_aggregateOrder"."id" = "order"."aggregateOrderId"
         LEFT JOIN "public"."order" "order__order_sellerOrders"
                   ON "order__order_sellerOrders"."aggregateOrderId" = "order"."id"
         LEFT JOIN "public"."order_channels_channel" "a7911e6beb0a62a03fc9de6525f58e9356fae563"
                   ON "a7911e6beb0a62a03fc9de6525f58e9356fae563"."orderId" = "order__order_sellerOrders"."id"
         LEFT JOIN "public"."channel" "order__order_sellerOrders__order__order_sellerOrders_channels"
                   ON "order__order_sellerOrders__order__order_sellerOrders_channels"."id" =
                      "a7911e6beb0a62a03fc9de6525f58e9356fae563"."channelId"
         LEFT JOIN "public"."customer" "order__order_customer" ON "order__order_customer"."id" = "order"."customerId"
         LEFT JOIN "public"."surcharge" "order__order_surcharges" ON "order__order_surcharges"."orderId" = "order"."id"
         LEFT JOIN "public"."order_promotions_promotion" "order_order__order_promotions"
                   ON "order_order__order_promotions"."orderId" = "order"."id"
         LEFT JOIN "public"."promotion" "order__order_promotions"
                   ON "order__order_promotions"."id" = "order_order__order_promotions"."promotionId"
         LEFT JOIN "public"."payment" "order__order_payments" ON "order__order_payments"."orderId" = "order"."id"
         LEFT JOIN "public"."refund" "order__order_payments__order__order_payments_refunds"
                   ON "order__order_payments__order__order_payments_refunds"."paymentId" = "order__order_payments"."id"
         LEFT JOIN "public"."order_line_reference" "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"
                   ON "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."refundId" =
                      "order__order_payments__order__order_payments_refunds"."id" AND
                      "6d9a166ae1ff7466534d31e779ac8e0ba4f9c5ca"."discriminator" = 'RefundLine'
         LEFT JOIN "public"."order_fulfillments_fulfillment" "order_order__order_fulfillments"
                   ON "order_order__order_fulfillments"."orderId" = "order"."id"
         LEFT JOIN "public"."fulfillment" "order__order_fulfillments"
                   ON "order__order_fulfillments"."id" = "order_order__order_fulfillments"."fulfillmentId"
         LEFT JOIN "public"."order_line_reference" "order__order_fulfillments__order__order_fulfillments_lines"
                   ON "order__order_fulfillments__order__order_fulfillments_lines"."fulfillmentId" =
                      "order__order_fulfillments"."id" AND
                      "order__order_fulfillments__order__order_fulfillments_lines"."discriminator" = 'FulfillmentLine'
         LEFT JOIN "public"."order_modification" "order__order_modifications"
                   ON "order__order_modifications"."orderId" = "order"."id"
         LEFT JOIN "public"."payment" "order__order_modifications__order__order_modifications_payment"
                   ON "order__order_modifications__order__order_modifications_payment"."id" =
                      "order__order_modifications"."paymentId"
         LEFT JOIN "public"."order_line_reference" "order__order_modifications__order__order_modifications_lines"
                   ON "order__order_modifications__order__order_modifications_lines"."modificationId" =
                      "order__order_modifications"."id" AND
                      "order__order_modifications__order__order_modifications_lines"."discriminator" =
                      'OrderModificationLine'
         LEFT JOIN "public"."refund" "order__order_modifications__order__order_modifications_refund"
                   ON "order__order_modifications__order__order_modifications_refund"."id" =
                      "order__order_modifications"."refundId"
         LEFT JOIN "public"."surcharge" "0218e86292ca999e1315efd412cd2bb9caae01c5"
                   ON "0218e86292ca999e1315efd412cd2bb9caae01c5"."orderModificationId" =
                      "order__order_modifications"."id"
         LEFT JOIN "public"."product_variant_price" "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"
                   ON "9308aa0daa07fbbac46f090d21748a5ef0cdc2c8"."variantId" =
                      "order__order_lines__order__order_lines_productVariant"."id"
         LEFT JOIN "public"."product_variant_translation" "9b359819a0693cdae1783b7021fcdfc7f73bc963"
                   ON "9b359819a0693cdae1783b7021fcdfc7f73bc963"."baseId" =
                      "order__order_lines__order__order_lines_productVariant"."id"
         LEFT JOIN "public"."shipping_method_translation" "231dbf002def041cf2bcc7adc1d27d2db28cf82c"
                   ON "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."baseId" =
                      "0fb3797f9c89af87a52450c304b80753991e0b41"."id"
         LEFT JOIN "public"."user" "order__order_customer__order__order_customer_user"
                   ON "order__order_customer__order__order_customer_user"."id" = "order__order_customer"."userId"
         LEFT JOIN "public"."promotion_translation" "order__order_promotions__order__order_promotions_translations"
                   ON "order__order_promotions__order__order_promotions_translations"."baseId" =
                      "order__order_promotions"."id"
         LEFT JOIN "public"."order_channels_channel" "order_channel" ON "order_channel"."orderId" = "order"."id"
         LEFT JOIN "public"."channel" "channel" ON "channel"."id" = "order_channel"."channelId"
WHERE "order"."id" = 1
  AND "channel"."id" = 1
ORDER BY order__order_lines."createdAt" ASC, order__order_lines."productVariantId" ASC -- PARAMETERS: [1,1]

That single query is responsible for the slow response time, as expected. This is not too surprising, since the query features 32 left joins and is therefore operating on many thousands of rows in the DB's memory.

What's interesting is that somehow v12 was able to deal with this just fine. In any case, it is worth optimizing this to simplify the query.

Why is the query so big?

Look at the resolver for the order query:

vendure/packages/core/src/api/resolvers/admin/order.resolver.ts

Lines 61 to 69 in 26e77d7

@Query()
@Allow(Permission.ReadOrder)
async order(
@Ctx() ctx: RequestContext,
@Args() args: QueryOrderArgs,
@Relations(Order) relations: RelationPaths<Order>,
): Promise<Order | undefined> {
return this.orderService.findOne(ctx, args.id, relations);
}

In particular, this part @Relations(Order) relations: RelationPaths<Order> is an optimization that inspects the incoming GraphQL request in order to determine up-front what relations need to be joined. The intention is to optimize against the "N + 1 problem" where the resolution of individual fields can lead to too many small queries to the DB, which can often be slower than allowing the DB to join the data up-front.

More info can be found here: https://docs.vendure.io/reference/typescript-api/request/relations-decorator/

However, in this case the resulting query is just too complex, so we need to reign in this optimization.

Here are some tests of alternative configurations:

No relations decorator

Let's remove the relations decorator altogether. Note that in this case, a default set of relations will be used in the query, which makes this much less efficient when only a small selection of relations (or none) are needed. We'll test this just as a baseline.

    orders(
        @Ctx() ctx: RequestContext,
        @Args() args: QueryOrdersArgs,
    ): Promise<PaginatedList<Order>> {
        return this.orderService.findAll(ctx, args.options || undefined);
    }

v12: 317, 169, 180, 190, 334, 220, 200, 166, 364, 197
v16: 187, 186, 226, 391, 252, 188, 192, 218, 223, 410

Relation depth 2

The default depth of the relations is 3. Let's set it to 2.

    @Query()
    @Allow(Permission.ReadOrder)
    async order(
        @Ctx() ctx: RequestContext,
        @Args() args: QueryOrderArgs,
        @Relations({ entity: Order, depth: 2 })
        relations: RelationPaths<Order>,
    ): Promise<Order | undefined> {
        return this.orderService.findOne(ctx, args.id, relations);
    }

v12: 162, 157, 152, 165, 337, 193, 182, 223, 197
v16: 4.5s, 3.9s, 4.1s, 4.0s, 4.5s

Relation depth 1

Join only the top-level relations in the query. Code is as above but with depth: 1.

v12: 267, 261, 265, 273, 439, 369, 267, 258, 297
v16: 261, 267, 253, 261, 398, 326, 265, 248, 270

Omit aggregate & seller orders, depth 3

Keep the relations depth at the default 3, but just omit the aggregateOrder & sellerOrders relations.

    @Query()
    @Allow(Permission.ReadOrder)
    async order(
        @Ctx() ctx: RequestContext,
        @Args() args: QueryOrderArgs,
        @Relations({ entity: Order, depth: 3, omit: ['aggregateOrder', 'sellerOrders'] })
        relations: RelationPaths<Order>,
    ): Promise<Order | undefined> {
        return this.orderService.findOne(ctx, args.id, relations);
    }

v12: 133, 341, 153, 159, 137, 138, 160, 192, 126
v16: 157, 289, 178, 125, 142, 123, 305, 368, 132

Omit aggregate & seller orders, depth 2

As above, but with depth: 2.

v12: 167, 130, 140, 157, 305, 176, 205, 156, 159, 340, 138
v16: 179, 168, 311, 158, 154, 164, 148, 135, 224, 148, 275

Omit aggregate & seller orders, depth 1

As above, but with depth: 1.

v12: 301, 254, 255, 253, 246, 473, 304, 270, 249, 256
v16: 249, 246, 246, 437, 269, 277, 334, 253, 285, 438

Summary

Here's a summary of all the above tests (thanks ChatGPT 😉 )

Postgres v12

Configuration Raw Response Times (ms) Average Response Time (ms) Relative Speed
No relations decorator 317, 169, 180, 190, 334, 220, 200, 166, 364, 197 233.7 1.85x slower
Relation depth 2 162, 157, 152, 165, 337, 193, 182, 223, 197 196.4 1.55x slower
Relation depth 1 267, 261, 265, 273, 439, 369, 267, 258, 297 299.5 2.36x slower
Omit aggregate & seller orders, depth 3 133, 341, 153, 159, 137, 138, 160, 192, 126 171.0 Fastest
Omit aggregate & seller orders, depth 2 167, 130, 140, 157, 305, 176, 205, 156, 159, 340, 138 179.1 1.05x slower
Omit aggregate & seller orders, depth 1 301, 254, 255, 253, 246, 473, 304, 270, 249, 256 295.7 2.31x slower

Postgres v16

Configuration Raw Response Times (ms or s) Average Response Time (ms) Relative Speed
No relations decorator 187, 186, 226, 391, 252, 188, 192, 218, 223, 410 247.3 ms 1.30x slower
Relation depth 2 4.5s, 3.9s, 4.1s, 4.0s, 4.5s 4,200 ms 22.05x slower
Relation depth 1 261, 267, 253, 261, 398, 326, 265, 248, 270 293.2 ms 1.54x slower
Omit aggregate & seller orders, depth 3 157, 289, 178, 125, 142, 123, 305, 368, 132 201.2 ms 1.06x slower
Omit aggregate & seller orders, depth 2 179, 168, 311, 158, 154, 164, 148, 135, 224, 148, 275 190.5 ms Fastest
Omit aggregate & seller orders, depth 1 249, 246, 246, 437, 269, 277, 334, 253, 285, 438 302.4 ms 1.59x slower

Conclusion

It is a toss-up between "Omit aggregate & seller orders, depth 3" & "Omit aggregate & seller orders, depth 2" - there are only slight differences with v12 marginally faster on one, and v16 marginally faster on the other.

We'll go with keeping the default depth of 3.

@michaelbromley michaelbromley mentioned this issue Aug 28, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type: bug 🐛 Something isn't working @vendure/core
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@michaelbromley