explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1WYj : Optimization for: plan #REVc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.751 4,988.340 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.27..339.22 rows=1 width=573) (actual time=833.456..4,988.340 rows=6 loops=1)

  • Output: ctmile."nMileID", milehist2."nHistID", milehist2."nActualOntimeReason", cttransorder."nSvcLevelID", cttransorder."nCarrID", cttransorder."nDivID", cttransorder."nTransOrderID", false, cast_json_arr_int(cttransorder."jDenorms", 'aSalesOrganizationIDs'::text), cast_json_arr_int(cttransorder."jDenorms", 'aSupplierIDs'::text), cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", (cttransorder."jDenorms" ->> 'sFinalTransportModeCode'::text), (cttransorder."jDenorms" ->> 'sFinalTransportModeName'::text), ctmile."sMileType", ctmile."sMileStatus", milereas."sReasName", milereas."sReasCode", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual
  • Join Filter: (milereas."nReasID" = milehist2."nActualOntimeReason")
  • Rows Removed by Join Filter: 486
  • Buffers: shared hit=9,444,177
2. 0.023 4,986.523 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.27..334.33 rows=1 width=106) (actual time=833.122..4,986.523 rows=6 loops=1)

  • Output: cttransorder."nSvcLevelID", cttransorder."nCarrID", cttransorder."nDivID", cttransorder."nTransOrderID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."nMileID", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", milehist2."nHistID", milehist2."nActualOntimeReason
  • Buffers: shared hit=9,444,069
3. 0.020 0.050 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.85..21.22 rows=1 width=98) (actual time=0.027..0.050 rows=6 loops=1)

  • Output: cttransorder."nSvcLevelID", cttransorder."nCarrID", cttransorder."nDivID", cttransorder."nTransOrderID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."nMileID", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual
  • Join Filter: (cttransorder."nTransOrderID" = ctmile."nTransOrderID")
  • Buffers: shared hit=9
4. 0.014 0.014 ↑ 1.0 1 1

Index Scan using ct_transorders__pkey on argus.ct_transorders cttransorder (cost=0.42..8.44 rows=1 width=62) (actual time=0.013..0.014 rows=1 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."sTransOrderCode", cttransorder."dtInsert", cttransorder."nInsertUser", cttransorder."dtEdit", cttransorder."dtCompleted", cttransorder."bActive", cttransorder."nDivID", cttransorder."sOrderType", cttransorder."sTransOrderName", cttransorder."sTransOrderStatus", cttransorder."nCancelReasonID", cttransorder."sComments", cttransorder."dtTrackBegin", cttransorder."dtTrackEinde", cttransorder."dtTurnback", cttransorder."dtBeginSched", cttransorder."dtBeginActual", cttransorder."dtEindeSched", cttransorder."dtEindeActual", cttransorder."sOfficeHours", cttransorder."bOverrideHazard", cttransorder."bOverrideTemper", cttransorder."nCurrMileID", cttransorder."oSettings", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."aMatOrderID", cttransorder."dtCancelled", cttransorder."bUrgent", cttransorder."dtRouteInit", cttransorder."dtRouteCurr", cttransorder."dtRouteFinal", cttransorder."sDefaultCurrency", cttransorder."nCurrTripID", cttransorder."nSalesOrderID", cttransorder."sBillingStatus", cttransorder."dtReadyBilling", cttransorder."nBillingVehiTypeID", cttransorder."dtReadyReporting", cttransorder."bSalesOrderChanged", cttransorder."dtBilling", cttransorder."nDefaultCourierID", cttransorder."jDenorms", cttransorder."jFlags", cttransorder."aSrcTransOrderIDs", cttransorder."bVATExempt", cttransorder."aSalesOrderChanges", cttransorder."sDefaultLanguage", cttransorder."bak_sTransOrderMode", cttransorder."nRouteID", cttransorder."bAutoapplyPurchRates", cttransorder."nCtInvoiceID", cttransorder."sFullTransOrderStatus", cttransorder."bOverrideHeavy", cttransorder."bOverridePartSwap", cttransorder."bOverrideReturn", cttransorder."bOverrideSuperHeavy", cttransorder."fOverrideGoodsValue", cttransorder."fOverrideLmeter", cttransorder."fOverrideVolume", cttransorder."fOverrideGrossWeight", cttransorder."dtLastStopETA", cttransorder."bAutoapplyVehicleType", cttransorder."aOrderMails", cttransorder."nBillingCustomDestLocaID", cttransorder."sRateCalcType", cttransorder."nOverrideParcels", cttransorder."nOverridePallets", cttransorder."sDriverInfoComment", cttransorder."nOrderTypeID", cttransorder."jLastPosi", cttransorder."aExcludedOrderMails", cttransorder."sSource", cttransorder."jCustomData", cttransorder."sMainLicensePlate", cttransorder."sTrailerLicensePlate", cttransorder."sTransportModeOverride", cttransorder."sCarrierReference", cttransorder."sCustomerReference", cttransorder."sSupplierReference", cttransorder."sWarehouseReference", cttransorder."aCtInvoiceIDs", cttransorder."fOverrideMinTemp", cttransorder."fOverrideMaxTemp", cttransorder."fOverrideReqTemp", cttransorder."dtActivated", cttransorder."sPrinterUserName", cttransorder."nOverrideCompanyCodeID", cttransorder."nNotifyLocaID", cttransorder."jNotifyContact", cttransorder."nConsigneeLocaID", cttransorder."jConsigneeContact", cttransorder."nShipperLocaID", cttransorder."jShipperContact", cttransorder."nOverrideTransportModeID", cttransorder."fOverrideNetWeight", cttransorder."dtEstimatedCollection", cttransorder."sEstimatedCollectionTimezone", cttransorder."dtEstimatedDelivery", cttransorder."sEstimatedDeliveryTimezone", cttransorder."nOverrideOrganizationID", cttransorder."dtCarrierStatus", cttransorder."sCarrierStatusTimezone", cttransorder."sCarrierStatusOriginal", cttransorder."sCarrierStatusStandardised", cttransorder."nDispatcherUserID", cttransorder."dtTraceLoggingUntil
  • Index Cond: (cttransorder."nTransOrderID" = 114,761)
  • Buffers: shared hit=4
5. 0.016 0.016 ↑ 2.7 6 1

Index Scan using ct_miles__ntransorderid__idx on argus.ct_miles ctmile (cost=0.42..12.58 rows=16 width=40) (actual time=0.007..0.016 rows=6 loops=1)

  • Output: ctmile."nMileID", ctmile."nStopID", ctmile."nMatOrderID", ctmile."nTransOrderID", ctmile."nLocaID", ctmile."nMileSeq", ctmile."sMileCode", ctmile."nUpdateUser", ctmile."dtUpdate", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtStartActual", ctmile."nSchedlAnte", ctmile."nSchedlPost", ctmile."nActualAnte", ctmile."nActualPost", ctmile."nOntimeAnte", ctmile."nOntimePost", ctmile."oSettings", ctmile."bVisible", ctmile."nSalesOrderID", ctmile."dtInsert", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."sRecipient", ctmile."sSignature", ctmile."dtGoodsActual", ctmile."dtCarrierActual", ctmile."nInsertUser
  • Index Cond: (ctmile."nTransOrderID" = 114,761)
  • Buffers: shared hit=5
6. 0.012 4,986.450 ↓ 0.0 0 6

Subquery Scan on milehist2 (cost=0.43..313.10 rows=1 width=12) (actual time=831.075..831.075 rows=0 loops=6)

  • Output: milehist2."nHistID", milehist2."nActualOntimeReason", milehist2."nMileID
  • Filter: (milehist2."nMileID" = ctmile."nMileID")
  • Buffers: shared hit=9,444,060
7. 0.012 4,986.438 ↓ 0.0 0 6

Limit (cost=0.43..313.09 rows=1 width=12) (actual time=831.073..831.073 rows=0 loops=6)

  • Output: allhist."nHistID", allhist."nActualOntimeReason", allhist."nMileID
  • Buffers: shared hit=9,444,060
8. 4,986.426 4,986.426 ↓ 0.0 0 6

Index Scan Backward using ct_miles_hist__pkey on argus.ct_miles_hist allhist (cost=0.43..100,051.57 rows=320 width=12) (actual time=831.071..831.071 rows=0 loops=6)

  • Output: allhist."nHistID", allhist."nActualOntimeReason", allhist."nMileID
  • Filter: ((allhist."nMileID" = ctmile."nMileID") AND (allhist."sAction" = 'UPDATE_ACTUAL'::"ctMileHistoryType"))
  • Rows Removed by Filter: 2,059,433
  • Buffers: shared hit=9,444,060
9. 0.066 0.066 ↑ 1.3 81 6

Seq Scan on argus.md_reass milereas (cost=0.00..3.06 rows=106 width=360) (actual time=0.004..0.011 rows=81 loops=6)

  • Output: milereas."nReasID", milereas."DEL_bDeleted", milereas."nCustID", milereas."sReasCode", milereas."sReasName", milereas."bActualOntimeEarly", milereas."bActualOntimeLate", milereas."bActualInput", milereas."bSchedChange", milereas."bSchedInput", milereas."bIncident", milereas."bChangeLocation", milereas."bTripInvestig", milereas."bOrderCancel", milereas."bWeb", milereas."bMob", milereas."bKnockon", milereas."bControlCustomer", milereas."bControlThirdParty", milereas."nDivID", milereas."dtInsert", milereas."bCustomerPortal", milereas."bOrderCostReject", milereas."nSupplierID", milereas."nInsertUser", milereas."nUpdateUser", milereas."nDeleteUser", milereas."dtUpdate", milereas."dtDelete", milereas."bFixed
  • Buffers: shared hit=12
Planning time : 0.536 ms
Execution time : 4,988.795 ms