explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uo5J

Settings
# exclusive inclusive rows x rows loops node
1. 1.756 5,155.662 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.27..339.22 rows=1 width=569) (actual time=853.683..5,155.662 rows=6 loops=1)

  • Output: ctmile."nMileID", 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", milehist."nActualOntimeReason", milereas."sReasName", milereas."sReasCode", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual
  • Join Filter: (milereas."nReasID" = milehist."nActualOntimeReason")
  • Rows Removed by Join Filter: 486
  • Buffers: shared hit=9,444,177
2. 0.020 5,153.840 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.27..334.33 rows=1 width=102) (actual time=853.349..5,153.840 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", milehist."nActualOntimeReason
  • Join Filter: (ctmile."sMileType" <> ALL ('{ARRIVE,DEPART}'::"ctMileType"[]))
  • Buffers: shared hit=9,444,069
3. 0.023 0.060 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.85..21.22 rows=1 width=98) (actual time=0.031..0.060 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.016 0.016 ↑ 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.014..0.016 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.021 0.021 ↑ 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.009..0.021 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.006 5,153.760 ↓ 0.0 0 6

Subquery Scan on milehist (cost=0.43..313.10 rows=1 width=8) (actual time=858.960..858.960 rows=0 loops=6)

  • Output: milehist."nMileID", milehist."nActualOntimeReason", allhist."nHistID
  • Filter: (milehist."nMileID" = ctmile."nMileID")
  • Buffers: shared hit=9,444,060
7. 0.012 5,153.754 ↓ 0.0 0 6

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

  • Output: allhist."nMileID", allhist."nActualOntimeReason", allhist."nHistID
  • Buffers: shared hit=9,444,060
8. 5,153.742 5,153.742 ↓ 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=858.957..858.957 rows=0 loops=6)

  • Output: allhist."nMileID", allhist."nActualOntimeReason", allhist."nHistID
  • 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.005..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.407 ms
Execution time : 5,156.132 ms