search

vendure-ecommerce / vendure

The commerce platform with customization in its DNA.
https://www.vendure.io
Other
5.79k stars 1.03k forks source link

Surcharges slow down order retrieval #3225

Open RoyalFoxy opened 1 day ago

RoyalFoxy commented 1 day ago

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):

Additional context Full reproducible

RoyalFoxy commented 1 day ago

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 commented 1 day ago

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

Full postgres DB Query ```sql 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

image

And without the sort it takes 148ms

image

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

image

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 commented 1 day ago

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