explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xwsS : Optimization for: plan #REVc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.736 5,140.319 ↓ 6.0 6 1

Nested Loop Left Join (cost=330.60..359.54 rows=1 width=573) (actual time=868.601..5,140.319 rows=6 loops=1)

  • Output: ctmile."nMileID", allhist."nHistID", allhist."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
  • Buffers: shared hit=9,444,165
2. 5,138.519 5,138.577 ↓ 6.0 6 1

Nested Loop Left Join (cost=330.46..358.86 rows=1 width=106) (actual time=868.291..5,138.577 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", allhist."nHistID", allhist."nActualOntimeReason
  • Buffers: shared hit=9,444,069
3. 0.019 0.052 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.85..21.22 rows=1 width=98) (actual time=0.029..0.052 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.015 0.015 ↑ 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.015 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.018 0.018 ↑ 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.018 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.000 0.006 ↓ 0.0 0 6

Index Scan using ct_miles_hist__pkey on argus.ct_miles_hist allhist (cost=329.61..337.63 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=6)

  • Output: allhist."nHistID", allhist."nOrigHistID", allhist."nMileID", allhist."nUserEdit", allhist."dtEdit", allhist."dtEditFake", allhist."sAction", allhist."sMileStatus", allhist."dtStartActual", allhist."nSchedlChangeReason", allhist."nSchedlInputReason", allhist."nActualOntimeReason", allhist."nActualInputReason", allhist."sSchedlChangeRemark", allhist."sSchedlInputRemark", allhist."sActualOntimeRemark", allhist."sActualInputRemark", allhist."nOntimeAnte", allhist."nOntimePost", allhist."bOldest", allhist."bNewest", allhist."dtInsert", allhist."dtSchedlFra", allhist."dtSchedlTil
  • Index Cond: (allhist."nHistID" = (SubPlan 2))
  • Filter: (allhist."nMileID" = ctmile."nMileID")
7.          

SubPlan (for Index Scan)

8. 0.012 5,138.466 ↑ 1.0 1 6

Result (cost=329.18..329.19 rows=1 width=4) (actual time=856.410..856.411 rows=1 loops=6)

  • Output: $1
  • Buffers: shared hit=9,444,060
9.          

Initplan (for Result)

10. 0.012 5,138.454 ↓ 0.0 0 6

Limit (cost=0.43..329.18 rows=1 width=4) (actual time=856.409..856.409 rows=0 loops=6)

  • Output: allhist2."nHistID
  • Buffers: shared hit=9,444,060
11. 5,138.442 5,138.442 ↓ 0.0 0 6

Index Scan Backward using ct_miles_hist__pkey on argus.ct_miles_hist allhist2 (cost=0.43..105,200.16 rows=320 width=4) (actual time=856.407..856.407 rows=0 loops=6)

  • Output: allhist2."nHistID
  • Index Cond: (allhist2."nHistID" IS NOT NULL)
  • Filter: ((allhist2."nMileID" = ctmile."nMileID") AND (allhist2."sAction" = 'UPDATE_ACTUAL'::"ctMileHistoryType"))
  • Rows Removed by Filter: 2,059,433
  • Buffers: shared hit=9,444,060
12. 0.006 0.006 ↓ 0.0 0 6

Index Scan using md_reass__pkey on argus.md_reass milereas (cost=0.14..0.16 rows=1 width=360) (actual time=0.001..0.001 rows=0 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
  • Index Cond: (milereas."nReasID" = allhist."nActualOntimeReason")
Planning time : 0.656 ms
Execution time : 5,140.780 ms