Surcharges slow down order retrieval

[RoyalFoxy]

Describe the bug
Adding many surcharges to an order makes the retrieval of the order (with surcharges) significantly slower.

To Reproduce
Steps to reproduce the behavior:

1. Use minimal Reproduction
2. Create Order
3. Add 30 product variants to order
4. Call addSurchargeToOrder graphql mutation 60 times to add 60 simple dummy surcharges
5. Look at created order in backend
6. Notice increased network time for query OrderDetailQuery (~200ms)
7. Execute OrderDetailQuery without surcharges on Order type
8. Notice normal network time for query OrderDetailQuery (~25ms)

Expected behavior
The surcharges should not impact query time on the Order

Environment (please complete the following information):

• @vendure/core version: 3.0.6
• Nodejs version: 22.8.0
• Database (mysql/postgres etc): sqlite (reproducible) & postgres (our production)

Additional context
Full reproducible

[RoyalFoxy]

As additional info:
On our production environment we have a customer that created an order with 17 surcharges and 15 order lines. The network time when executing OrderDetailQuery is roughly 20s and without surcharges it's roughly 500ms

The same customer also created an order with 30 surcharges and 63 order lines. The server ends up running into a memory heap allocation error and crashes.

Note that our production uses postgres and the reproducible uses sqlite so that could also be a major factor why our production is this much slower but even with sqlite it's a significant slow down on an otherwise vanilla vendure instance.

[RoyalFoxy]

Additionally we've ran some tests with the db query that seems to retrieve the majority of the data

Full postgres DB 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"."customFieldsShippingdateoptioncode" AS "order_customFieldsShippingdateoptioncode",
  "order"."customFieldsShippingdate" AS "order_customFieldsShippingdate",
  "order"."customFieldsAdditionaloptionsphonenotification" AS "order_customFieldsAdditionaloptionsphonenotification",
  "order"."customFieldsAdditionaloptionsphonenotificationnumber" AS "order_customFieldsAdditionaloptionsphonenotificationnumber",
  "order"."customFieldsAdditionaloptionstruckwithcrane" AS "order_customFieldsAdditionaloptionstruckwithcrane",
  "order"."customFieldsPaymentmethodcode" AS "order_customFieldsPaymentmethodcode",
  "order"."customFieldsUseshippingaddressasbillingaddress" AS "order_customFieldsUseshippingaddressasbillingaddress",
  "order"."customFieldsReference" AS "order_customFieldsReference",
  "order"."customFieldsNote" AS "order_customFieldsNote",
  "order"."customFieldsElectronicpaymentmethodcode" AS "order_customFieldsElectronicpaymentmethodcode",
  "order"."customFieldsElectronicpaymenttransactionid" AS "order_customFieldsElectronicpaymenttransactionid",
  "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"."taxCategoryId" AS "order__order_lines_taxCategoryId",
  "order__order_lines"."initialListPrice" AS "order__order_lines_initialListPrice",
  "order__order_lines"."listPrice" AS "order__order_lines_listPrice",
  "order__order_lines"."featuredAssetId" AS "order__order_lines_featuredAssetId",
  "order__order_lines"."orderId" AS "order__order_lines_orderId",
  "order__order_lines"."customFieldsCutid" AS "order__order_lines_customFieldsCutid",
  "order__order_lines"."customFieldsReference" AS "order__order_lines_customFieldsReference",
  "order__order_lines"."customFieldsPackseparately" AS "order__order_lines_customFieldsPackseparately",
  "order__order_lines"."customFieldsLabelindividually" AS "order__order_lines_customFieldsLabelindividually",
  "order__order_lines"."customFieldsCertificate" AS "order__order_lines_customFieldsCertificate",
  "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_featuredAsset"."customFieldsAlignment" AS "7b03580fad6b81b91075d1cea35aabae5f6a3af2",
  "order__order_lines__order__order_lines_featuredAsset"."customFieldsImportid" AS "64f73fe11839bb6ea73489db64deb3ea436c4901",
  "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"."featuredAssetId" AS "3536b083fd1364798a8d95647c4b78d3737cd22a",
  "order__order_lines__order__order_lines_productVariant"."taxCategoryId" AS "e1a6b560e78290830d865665172e06f4790e9dfc",
  "order__order_lines__order__order_lines_productVariant"."productId" AS "order__order_lines__order__order_lines_productVariant_productId",
  "order__order_lines__order__order_lines_productVariant"."customFieldsWeight" AS "a5774eb0bfd873fe3ad8d4557b5a668a2da0a84f",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCertificateavailable" AS "bf4ef42af413c7a61baf34f9f6a2ffc36b531c75",
  "order__order_lines__order__order_lines_productVariant"."customFieldsShape" AS "fa57fde94561ce60c53130925a2f0f8f562f0a52",
  "order__order_lines__order__order_lines_productVariant"."customFieldsAssortmentgroup" AS "1bd530ae8628b73dfbf057a4dc80ff4296da174b",
  "order__order_lines__order__order_lines_productVariant"."customFieldsLinediscountgroup" AS "41228a6640e0727804c1d6be2b01635aa6d41c8c",
  "order__order_lines__order__order_lines_productVariant"."customFieldsIscuttable" AS "2e2efe7e61df5e0b42e41a71411a47c71e1e7e88",
  "order__order_lines__order__order_lines_productVariant"."customFieldsLength" AS "2e1846c4983a2899e4660bc2cab87f086dc92bae",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCuttingdiameter" AS "91c81281a8f57fad9cefe56fa2903877bcfc9acb",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCutcostsfrom1" AS "d04b6b55e169ed52329ceefcbffc9eae608c262e",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCutcostsfrom2" AS "9caa0a0128c526cd50c71486aabb432b203effad",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCutcostsfrom5" AS "4add7017394b352de957982fa1d3148a2837d536",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCutcostsfrom10" AS "d1d2d2fa3340e37bb929b3b48e76cbea060785f9",
  "order__order_lines__order__order_lines_productVariant"."customFieldsCutcostsfrom20" AS "18333cb453b97c58d293a5c59b0073f0877350ae",
  "order__order_lines__order__order_lines_productVariant"."customFieldsLengthvariantsku" AS "53bd2999cf1d7eea0ecbdb7fff373d81a9cd8f80",
  "order__order_lines__order__order_lines_productVariant"."customFieldsErpposition" AS "9b63079eda8425d5369dd462d39445724cf99092",
  "order__order_lines__order__order_lines_productVariant"."customFieldsMarkupgroup" AS "6f93aedc099332daa0c45b7b6004813eeffe8a84",
  "order__order_lines__order__order_lines_productVariant"."customFieldsPricecalculationunit" AS "0a1b7f5b3d36b36503881a0b198d1db001968687",
  "order__order_lines__order__order_lines_productVariant"."customFieldsSalesunit" AS "300d12da11203ece212930adef509d708bd65e15",
  "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_lines__order__order_lines_customFields_cut"."createdAt" AS "e7a88ef544d12f9a959871cdfd3471a2dd680137",
  "order__order_lines__order__order_lines_customFields_cut"."updatedAt" AS "dd7a9c261d2038f8706ba2a9fb9bd37c344e5dd6",
  "order__order_lines__order__order_lines_customFields_cut"."length" AS "order__order_lines__order__order_lines_customFields_cut_length",
  "order__order_lines__order__order_lines_customFields_cut"."angleA" AS "order__order_lines__order__order_lines_customFields_cut_angleA",
  "order__order_lines__order__order_lines_customFields_cut"."angleB" AS "order__order_lines__order__order_lines_customFields_cut_angleB",
  "order__order_lines__order__order_lines_customFields_cut"."id" AS "order__order_lines__order__order_lines_customFields_cut_id",
  "order__order_lines__order__order_lines_customFields_cut"."codeId" AS "order__order_lines__order__order_lines_customFields_cut_codeId",
  "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",
  "0fb3797f9c89af87a52450c304b80753991e0b41"."customFieldsNeedsshippingaddress" AS "76fb7a9409350867c364096f3d03e8d87b75c622",
  "0fb3797f9c89af87a52450c304b80753991e0b41"."customFieldsSortorder" AS "0fb3797f9c89af87a52450c304b80753991e0b41_customFieldsSortorder",
  "0fb3797f9c89af87a52450c304b80753991e0b41"."customFieldsIseligible" AS "0fb3797f9c89af87a52450c304b80753991e0b41_customFieldsIseligible",
  "0fb3797f9c89af87a52450c304b80753991e0b41"."customFieldsEligibilitymessage" AS "7e92fba557c38ac105a2376571fd5ae921e8ea57",
  "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_customer"."customFieldsSalutation" AS "order__order_customer_customFieldsSalutation",
  "order__order_customer"."customFieldsCreditrating" AS "order__order_customer_customFieldsCreditrating",
  "order__order_customer"."customFieldsShippingcondition" AS "order__order_customer_customFieldsShippingcondition",
  "order__order_customer"."customFieldsShippingflatratevalue" AS "order__order_customer_customFieldsShippingflatratevalue",
  "order__order_customer"."customFieldsCertificateprice" AS "order__order_customer_customFieldsCertificateprice",
  "order__order_customer"."customFieldsUnverifiedcustomeraccountnumber" AS "ab5ba64bed24c8e6003a1e6d37d02e4a7c861e63",
  "order__order_customer"."customFieldsCustomeraccountnumber" AS "order__order_customer_customFieldsCustomeraccountnumber",
  "order__order_customer"."customFieldsCustomertype" AS "order__order_customer_customFieldsCustomertype",
  "order__order_customer"."customFieldsLanguage" AS "order__order_customer_customFieldsLanguage",
  "order__order_customer"."customFieldsPromotionpackage" AS "order__order_customer_customFieldsPromotionpackage",
  "order__order_customer"."customFieldsPricegroup" AS "order__order_customer_customFieldsPricegroup",
  "order__order_customer"."customFieldsMarkupgroup" AS "order__order_customer_customFieldsMarkupgroup",
  "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_promotions"."customFieldsImportid" AS "order__order_promotions_customFieldsImportid",
  "order__order_promotions"."customFieldsCode" AS "order__order_promotions_customFieldsCode",
  "order__order_promotions"."customFieldsValue" AS "order__order_promotions_customFieldsValue",
  "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",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."createdAt" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_createdAt",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."updatedAt" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_updatedAt",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."deletedAt" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_deletedAt",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."code" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_code",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."type" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_type",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."pieceCount" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_pieceCount",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."angleCount" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_angleCount",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."position" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_position",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."id" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_id",
  "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."imageId" AS "147fff80cf402b6fde4fb4513ebaa04d41ff5c96_imageId",
  "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",
  "231dbf002def041cf2bcc7adc1d27d2db28cf82c"."customFieldsNotice" AS "231dbf002def041cf2bcc7adc1d27d2db28cf82c_customFieldsNotice",
  "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"."cut" "order__order_lines__order__order_lines_customFields_cut" ON "order__order_lines__order__order_lines_customFields_cut"."id" = "order__order_lines"."customFieldsCutid"
  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"."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"."cut_code" "147fff80cf402b6fde4fb4513ebaa04d41ff5c96" ON "147fff80cf402b6fde4fb4513ebaa04d41ff5c96"."id" = "order__order_lines__order__order_lines_customFields_cut"."codeId"
  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" = $2
ORDER BY
  order__order_lines."createdAt" ASC,
  order__order_lines."productVariantId" ASC

This query takes 6.1s to complete where most of the time is spent sorting the order lines

And without the sort it takes 148ms

With the sort but without the surcharges in the same query the query takes 199ms

Also it's very weird that adding the surcharges increases the rows from around 3000 to over 50'000. These stats belong to the order with 17 surcharges and 15 order lines that takes around 12 seconds to complete on my machine and 20s on the production env

[michaelbromley]

Thanks for this detailed report. Right now I have no idea why this performs in this way. Definitely needs some thorough further investigation.