explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s23A : Optimization for: plan #REVc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.859 5,400.695 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.98..379.65 rows=1 width=1,193) (actual time=829.446..5,400.695 rows=6 loops=1)

  • Output: milereas."sReasName", milereas."sReasCode", cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cast_json_arr_int(cttransorder."jDenorms", 'aSalesOrganizationIDs'::text), cast_json_arr_int(cttransorder."jDenorms", 'aSupplierIDs'::text), origloca."sCtry", cast_json_int(cttransorder."jDenorms", 'nOrigLocaID'::text), destloca."sCtry", cast_json_int(cttransorder."jDenorms", 'nDestLocaID'::text), cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", mddiv."sDivName", mddiv."sDivCode", mdcarr."sCarrName", mdcarr."sCarrCode", mdsvclevels."sSvcLevelName", mdsvclevels."sSvcLevelCode", (cttransorder."jDenorms" ->> 'sFinalTransportModeCode'::text), (cttransorder."jDenorms" ->> 'sFinalTransportModeName'::text), ctmile."sMileType", ctmile."sMileStatus", timezone((mdloca."sTimezone")::text, timezone('UTC'::text, ctmile."dtSchedlFra")), timezone((mdloca."sTimezone")::text, timezone('UTC'::text, ctmile."dtSchedlTil")), timezone((mdloca."sTimezone")::text, timezone('UTC'::text, ctmile."dtStartActual")), mdloca."sLocaName", mdloca."sLocaType", mdloca."sCtry", mdloca."sCity", mdloca."sPost", concat_ws(' '::text, replace((mdloca."sAddr")::text, '
  • Join Filter: (mdsvclevels."nSvcLevelID" = cttransorder."nSvcLevelID")
  • Rows Removed by Join Filter: 84
  • Buffers: shared hit=9,444,345
2. 0.025 5,398.812 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.98..377.29 rows=1 width=813) (actual time=829.119..5,398.812 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", milereas."sReasName", milereas."sReasCode", origloca."sCtry", destloca."sCtry", mdloca."sTimezone", mdloca."sLocaName", mdloca."sLocaType", mdloca."sCtry", mdloca."sCity", mdloca."sPost", mdloca."sAddr", mdloca."sUnit", mdloca."fLat", mdloca."fLong", mddiv."sDivName", mddiv."sDivCode", mdcarr."sCarrName", mdcarr."sCarrCode
  • Buffers: shared hit=9,444,195
3. 0.031 5,398.757 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.84..369.13 rows=1 width=792) (actual time=829.110..5,398.757 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", milereas."sReasName", milereas."sReasCode", origloca."sCtry", destloca."sCtry", mdloca."sTimezone", mdloca."sLocaName", mdloca."sLocaType", mdloca."sCtry", mdloca."sCity", mdloca."sPost", mdloca."sAddr", mdloca."sUnit", mdloca."fLat", mdloca."fLong", mddiv."sDivName", mddiv."sDivCode
  • Join Filter: (mddiv."nDivID" = cttransorder."nDivID")
  • Rows Removed by Join Filter: 6
  • Buffers: shared hit=9,444,183
4. 0.024 5,398.708 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.84..368.10 rows=1 width=564) (actual time=829.099..5,398.708 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", milereas."sReasName", milereas."sReasCode", origloca."sCtry", destloca."sCtry", mdloca."sTimezone", mdloca."sLocaName", mdloca."sLocaType", mdloca."sCtry", mdloca."sCity", mdloca."sPost", mdloca."sAddr", mdloca."sUnit", mdloca."fLat", mdloca."fLong
  • Buffers: shared hit=9,444,177
5. 0.235 5,398.666 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.55..360.04 rows=1 width=460) (actual time=829.092..5,398.666 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", ctmile."nLocaID", milereas."sReasName", milereas."sReasCode", origloca."sCtry", destloca."sCtry
  • Buffers: shared hit=9,444,159
6. 0.528 5,398.395 ↓ 6.0 6 1

Nested Loop Left Join (cost=315.02..351.47 rows=1 width=457) (actual time=829.049..5,398.395 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", ctmile."nLocaID", milereas."sReasName", milereas."sReasCode", origloca."sCtry
  • Buffers: shared hit=9,444,117
7. 0.016 5,397.825 ↓ 6.0 6 1

Nested Loop Left Join (cost=314.48..342.91 rows=1 width=454) (actual time=828.938..5,397.825 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", ctmile."nLocaID", milereas."sReasName", milereas."sReasCode
  • Buffers: shared hit=9,444,075
8. 5,397.747 5,397.803 ↓ 6.0 6 1

Nested Loop Left Join (cost=314.34..342.74 rows=1 width=102) (actual time=828.935..5,397.803 rows=6 loops=1)

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", ctmile."nLocaID", milehist."nActualOntimeReason
  • Join Filter: (ctmile."sMileType" <> ALL ('{ARRIVE,DEPART}'::"ctMileType"[]))
  • Buffers: shared hit=9,444,075
9. 0.019 0.050 ↓ 6.0 6 1

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

  • Output: cttransorder."nTransOrderID", cttransorder."nDivID", cttransorder."nCarrID", cttransorder."nSvcLevelID", cttransorder."jDenorms", cttransorder."sTransOrderCode", cttransorder."sFullTransOrderStatus", cttransorder."sTransOrderStatus", ctmile."sMileType", ctmile."sMileStatus", ctmile."dtSchedlFra", ctmile."dtSchedlTil", ctmile."dtStartActual", ctmile."nMileID", ctmile."nLocaID
  • Join Filter: (cttransorder."nTransOrderID" = ctmile."nTransOrderID")
  • Buffers: shared hit=9
10. 0.013 0.013 ↑ 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.012..0.013 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
11. 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=44) (actual time=0.009..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
12. 0.000 0.006 ↓ 0.0 0 6

Index Scan using ct_miles_hist__pkey on argus.ct_miles_hist milehist (cost=313.49..321.51 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=6)

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

SubPlan (for Index Scan)

14. 0.006 5,397.702 ↓ 0.0 0 6

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

  • Output: allhist."nHistID
  • Buffers: shared hit=9,444,066
15. 5,397.696 5,397.696 ↓ 0.0 0 6

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

  • Output: allhist."nHistID
  • Filter: ((allhist."nMileID" = ctmile."nMileID") AND (allhist."sAction" = 'UPDATE_ACTUAL'::"ctMileHistoryType"))
  • Rows Removed by Filter: 2,059,258
  • Buffers: shared hit=9,444,066
16. 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" = milehist."nActualOntimeReason")
17. 0.042 0.042 ↑ 1.0 1 6

Index Scan using md_locas__pkey on argus.md_locas origloca (cost=0.54..8.55 rows=1 width=7) (actual time=0.007..0.007 rows=1 loops=6)

  • Output: origloca."nLocaID", origloca."bDeleted", origloca."sLocaCode", origloca."sLocaName", origloca."sLocaType", origloca."sAddr", origloca."sUnit", origloca."sCity", origloca."sProv", origloca."sPost", origloca."sCtry", origloca."sTimezone", origloca."fLat", origloca."fLong", origloca."sGeocodeQuality", origloca."nGeofenceInner", origloca."nGeofenceOuter", origloca."jContact", origloca."nDivID", origloca."aLinkedCusts", origloca."bCourierBase", origloca."sInstructions", origloca."dtInsert", origloca."nGeocodeQuality", origloca."aMdHandlingIDs", origloca."bCostCenter", origloca."sEngineerNumber", origloca."jCustomsContact", origloca."sCompanyNumber", origloca."sSource
  • Index Cond: (origloca."nLocaID" = cast_json_int(cttransorder."jDenorms", 'nOrigLocaID'::text))
  • Buffers: shared hit=18
18. 0.036 0.036 ↑ 1.0 1 6

Index Scan using md_locas__pkey on argus.md_locas destloca (cost=0.54..8.55 rows=1 width=7) (actual time=0.005..0.006 rows=1 loops=6)

  • Output: destloca."nLocaID", destloca."bDeleted", destloca."sLocaCode", destloca."sLocaName", destloca."sLocaType", destloca."sAddr", destloca."sUnit", destloca."sCity", destloca."sProv", destloca."sPost", destloca."sCtry", destloca."sTimezone", destloca."fLat", destloca."fLong", destloca."sGeocodeQuality", destloca."nGeofenceInner", destloca."nGeofenceOuter", destloca."jContact", destloca."nDivID", destloca."aLinkedCusts", destloca."bCourierBase", destloca."sInstructions", destloca."dtInsert", destloca."nGeocodeQuality", destloca."aMdHandlingIDs", destloca."bCostCenter", destloca."sEngineerNumber", destloca."jCustomsContact", destloca."sCompanyNumber", destloca."sSource
  • Index Cond: (destloca."nLocaID" = cast_json_int(cttransorder."jDenorms", 'nDestLocaID'::text))
  • Buffers: shared hit=18
19. 0.018 0.018 ↑ 1.0 1 6

Index Scan using md_locas__pkey on argus.md_locas mdloca (cost=0.29..8.05 rows=1 width=112) (actual time=0.002..0.003 rows=1 loops=6)

  • Output: mdloca."nLocaID", mdloca."bDeleted", mdloca."sLocaCode", mdloca."sLocaName", mdloca."sLocaType", mdloca."sAddr", mdloca."sUnit", mdloca."sCity", mdloca."sProv", mdloca."sPost", mdloca."sCtry", mdloca."sTimezone", mdloca."fLat", mdloca."fLong", mdloca."sGeocodeQuality", mdloca."nGeofenceInner", mdloca."nGeofenceOuter", mdloca."jContact", mdloca."nDivID", mdloca."aLinkedCusts", mdloca."bCourierBase", mdloca."sInstructions", mdloca."dtInsert", mdloca."nGeocodeQuality", mdloca."aMdHandlingIDs", mdloca."bCostCenter", mdloca."sEngineerNumber", mdloca."jCustomsContact", mdloca."sCompanyNumber", mdloca."sSource
  • Index Cond: (mdloca."nLocaID" = ctmile."nLocaID")
  • Buffers: shared hit=18
20. 0.018 0.018 ↓ 2.0 2 6

Seq Scan on argus.md_divs mddiv (cost=0.00..1.01 rows=1 width=232) (actual time=0.003..0.003 rows=2 loops=6)

  • Output: mddiv."nDivID", mddiv."bDeleted", mddiv."sDivCode", mddiv."sDivName", mddiv."sConfirmCurrency", mddiv."fConfirmAmount", mddiv."dtInsert", mddiv."jSettings", mddiv."sArriveSchedlLogic
  • Buffers: shared hit=6
21. 0.030 0.030 ↑ 1.0 1 6

Index Scan using md_carrs__pkey on argus.md_carrs mdcarr (cost=0.14..8.16 rows=1 width=25) (actual time=0.004..0.005 rows=1 loops=6)

  • Output: mdcarr."nCarrID", mdcarr."bDeleted", mdcarr."sCarrCode", mdcarr."sCarrName", mdcarr."nDivID", mdcarr."sDefaultCurrency", mdcarr."sLangCode", mdcarr."bVendor", mdcarr."jProfile", mdcarr."jAddress", mdcarr."dtInsert", mdcarr."bPlanboard", mdcarr."jSettings", mdcarr."nPaymentTerms", mdcarr."sPaymentTerms", mdcarr."sRoutingMode", mdcarr."bInvoiceConfirmation", mdcarr."aVehiTypeID", mdcarr."nDefaultVehiTypeID", mdcarr."sCarrierReference", mdcarr."sCustomerReference", mdcarr."sSupplierReference", mdcarr."sWarehouseReference", mdcarr."aInvoiceGrouping", mdcarr."jContact", mdcarr."sWasteLicenseNumber", mdcarr."dWasteLicenseExpirationDate", mdcarr."sPrinterCarrierCode", mdcarr."nMdCarrierIntegrationID
  • Index Cond: (mdcarr."nCarrID" = cttransorder."nCarrID")
  • Buffers: shared hit=12
22. 0.024 0.024 ↓ 1.1 15 6

Seq Scan on argus.md_svclevels mdsvclevels (cost=0.00..1.14 rows=14 width=232) (actual time=0.002..0.004 rows=15 loops=6)

  • Output: mdsvclevels."nSvcLevelID", mdsvclevels."bDeleted", mdsvclevels."sSvcLevelCode", mdsvclevels."sSvcLevelName", mdsvclevels."nDivID", mdsvclevels."sSalesBillingLogic", mdsvclevels."sRateSrcDistance", mdsvclevels."sRateSrcDriveTime", mdsvclevels."bRateSrcWeight", mdsvclevels."dtInsert", mdsvclevels."bSalesBillingLogicApplyAll", mdsvclevels."sPurchBillingLogic", mdsvclevels."bPurchBillingLogicApplyAll", mdsvclevels."sSalesRateCalcType
  • Buffers: shared hit=6
Planning time : 2.944 ms
Execution time : 5,401.461 ms