Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0040615 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Modules] Advanced Warehouse Operations | major | always | 2019-04-15 12:29 | 2019-05-31 14:46 | |||
| Reporter | joniturralde93 | View Status | public | |||||
| Assigned To | vmromanos | |||||||
| Priority | urgent | Resolution | unable to reproduce | Fixed in Version | ||||
| Status | closed | Fix in branch | Fixed in SCM revision | |||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Regression date | ||||||||
| Regression introduced by commit | ||||||||
| Regression level | ||||||||
| Review Assigned To | vmromanos | |||||||
| Regression introduced in release | ||||||||
| Summary | 0040615: Task loading can be very slow [Frontend] | |||||||
| Description | Performance problem when loading tasks. - 1100 products (160 of them with assigned attribute sets (a lot of possible values)) - 163 attributes In local machine, the requests lasts for around 30 seconds. Attached both executed queries in backend and explain analyze for each one. | |||||||
| Steps To Reproduce | - Login/refresh awo frontend, or click menu option "Refresh" | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  Query1 [^] (10,406 bytes) 2019-04-15 12:29 [Show Content] [Hide Content]2019-04-15 10:27:17.017 CEST [17885] tad@desguacesAWO LOG: duration: 15794.468 ms execute <unnamed>/C_295: select distinct case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.OBAWO_Task_ID else coalesce(obawo_task0_.OBAWO_Receptionlist_ID, obawo_task0_.OBAWO_Pickinglist_ID) end as col_0_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Created else obawo_rece1_.Created end as col_1_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.M_Product_ID else null end as col_2_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then ad_column_identifier_std('m_product', product22_.M_Product_ID) else null end as col_3_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then trim(product22_.UPC) else null end as col_4_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then uom17_.C_UOM_ID else null end as col_5_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_getauomgtin(uom17_.C_UOM_ID, product22_.M_Product_ID) else null end as col_6_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_getauomconversionrate(uom17_.C_UOM_ID, product22_.M_Product_ID) else null end as col_7_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then uom24_.C_UOM_ID else null end as col_8_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes26_.M_AttributeSet_ID else null end as col_9_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes11_.M_AttributeSetInstance_ID else null end as col_10_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes11_.Description else null end as col_11_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then m_getjsondescription(attributes11_.M_AttributeSetInstance_ID, 'Y', '140') else null end as col_12_0_, cast(null as char(1)) as col_13_0_, cast(null as char(1)) as col_14_0_, cast(null as char(1)) as col_15_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_Qty else null end as col_16_0_, cast(null as char(1)) as col_17_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_Locator_To_ID else null end as col_18_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator5_.Value else null end as col_19_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator5_.Barcode else null end as col_20_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.M_Locator_ID else null end as col_21_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.Value else null end as col_22_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.Barcode else null end as col_23_0_, cast(null as char(1)) as col_24_0_, cast(null as char(1)) as col_25_0_, cast(null as char(1)) as col_26_0_, cast(null as char(1)) as col_27_0_, cast(null as char(1)) as col_28_0_, cast(null as char(1)) as col_29_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_M_Refinventory_ID else null end as col_30_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg15_.Value else null end as col_31_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Confirmed_M_Refinventory_ID else null end as col_32_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg16_.Value else null end as col_33_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg10_.M_RefInventory_ID else null end as col_34_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg10_.Value else null end as col_35_0_, obawo_inte12_.IsExternalRouting as col_36_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte12_.Externalwarehouse_Scope else null end as col_37_0_, obawo_inte12_.IsRemoveReference as col_38_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte19_.OBAWO_Internal_Routing_Area_ID else null end as col_39_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte21_.OBAWO_Internal_Routing_Area_ID else null end as col_40_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte12_.OBAWO_Internal_Routing_ID else null end as col_41_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Obawo_Inv_Tran_Type_ID else null end as col_42_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_43_0_, case when obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_44_0_, obawo_task13_.OBAWO_Task_Type_ID as col_45_0_, obawo_task13_.Color as col_46_0_, obawo_task13_.Name as col_47_0_, obawo_task13_.Value as col_48_0_, obawo_task13_.Obawo_Base_Task_Type_ID as col_49_0_, obawo_base14_.Value as col_50_0_, obawo_base14_.Name as col_51_0_, obawo_batc2_.Obawo_Batchoftasks_ID as col_52_0_, obawo_batc2_.Documentno as col_53_0_, obawo_batc2_.Created as col_54_0_, obawo_rece1_.Obawo_Receptionlist_ID as col_55_0_, obawo_rece1_.Documentno as col_56_0_, obawo_rece1_.Created as col_57_0_, obawo_rece1_.Issotrx as col_58_0_, obawo_pick3_.Obawo_Pickinglist_ID as col_59_0_, obawo_pick3_.Documentno as col_60_0_, obawo_pick3_.Created as col_61_0_, materialmg4_.M_RefInventory_ID as col_62_0_, materialmg4_.Value as col_63_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_64_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Priority else obawo_getchildtaskmaxpriority(coalesce(obawo_task0_.OBAWO_Receptionlist_ID, obawo_task0_.OBAWO_Pickinglist_ID), '100') end as col_65_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Obawo_Travel_Sequence else '000000000' end as col_66_0_, coalesce(obawo_task0_.Document_Info, ' ') as col_67_0_ from OBAWO_task obawo_task0_ left outer join OBAWO_receptionlist obawo_rece1_ on obawo_task0_.OBAWO_Receptionlist_ID=obawo_rece1_.Obawo_Receptionlist_ID left outer join OBAWO_BatchOfTasks obawo_batc2_ on obawo_task0_.OBAWO_Batchoftasks_ID=obawo_batc2_.Obawo_Batchoftasks_ID left outer join OBAWO_Pickinglist obawo_pick3_ on obawo_task0_.OBAWO_Pickinglist_ID=obawo_pick3_.Obawo_Pickinglist_ID left outer join M_RefInventory materialmg4_ on obawo_pick3_.M_Refinventory_ID=materialmg4_.M_RefInventory_ID left outer join M_Locator locator5_ on obawo_task0_.Expected_Locator_To_ID=locator5_.M_Locator_ID left outer join M_Locator locator6_ on obawo_task0_.Expected_Locator_From_ID=locator6_.M_Locator_ID left outer join M_Locator locator7_ on obawo_task0_.Confirmed_Locator_To_ID=locator7_.M_Locator_ID left outer join M_Locator locator8_ on obawo_task0_.Confirmed_Locator_From_ID=locator8_.M_Locator_ID left outer join M_Storage_Detail materialmg9_ on obawo_task0_.M_Storage_Detail_ID=materialmg9_.M_Storage_Detail_ID left outer join M_RefInventory materialmg10_ on materialmg9_.M_RefInventory_ID=materialmg10_.M_RefInventory_ID left outer join M_AttributeSetInstance attributes11_ on obawo_task0_.Expected_Attribute_ID=attributes11_.M_AttributeSetInstance_ID left outer join OBAWO_internal_routing obawo_inte12_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte12_.OBAWO_Internal_Routing_ID inner join OBAWO_task_type obawo_task13_ on obawo_task0_.OBAWO_Task_Type_ID=obawo_task13_.OBAWO_Task_Type_ID inner join OBAWO_base_task_type obawo_base14_ on obawo_task13_.Obawo_Base_Task_Type_ID=obawo_base14_.OBAWO_Base_Task_Type_ID left outer join M_RefInventory materialmg15_ on obawo_task0_.Expected_M_Refinventory_ID=materialmg15_.M_RefInventory_ID left outer join M_RefInventory materialmg16_ on obawo_task0_.Confirmed_M_Refinventory_ID=materialmg16_.M_RefInventory_ID left outer join C_UOM uom17_ on obawo_task0_.C_Aum=uom17_.C_UOM_ID left outer join OBAWO_internal_routing obawo_inte18_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte18_.OBAWO_Internal_Routing_ID left outer join OBAWO_internal_routing_area obawo_inte19_ on obawo_inte18_.Internal_Routing_Area_From_ID=obawo_inte19_.OBAWO_Internal_Routing_Area_ID left outer join OBAWO_internal_routing obawo_inte20_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte20_.OBAWO_Internal_Routing_ID left outer join OBAWO_internal_routing_area obawo_inte21_ on obawo_inte20_.Internal_Routing_Area_To_ID=obawo_inte21_.OBAWO_Internal_Routing_Area_ID left outer join M_Product product22_ on obawo_task0_.M_Product_ID=product22_.M_Product_ID left outer join M_Product product23_ on obawo_task0_.M_Product_ID=product23_.M_Product_ID left outer join C_UOM uom24_ on product23_.C_UOM_ID=uom24_.C_UOM_ID left outer join M_Product product25_ on obawo_task0_.M_Product_ID=product25_.M_Product_ID left outer join M_AttributeSet attributes26_ on product25_.M_AttributeSet_ID=attributes26_.M_AttributeSet_ID where obawo_task0_.AD_User_ID='100' and not (exists (select 1 from OBAWO_Error_Task obawo_erro27_ cross join OBAWO_errors obawo_erro28_ where obawo_erro27_.Obawo_Errors_ID=obawo_erro28_.Obawo_Errors_ID and obawo_erro27_.Obawo_Task_ID=obawo_task0_.OBAWO_Task_ID and obawo_erro28_.Taskstatus='N' and obawo_erro28_.Blocks='Y')) and obawo_task0_.M_Warehouse_ID='FCA81F56E9E643E096E86F5F379459FB' and obawo_task0_.Status='AV' order by col_65_0_ desc, col_66_0_, col_22_0_ asc, col_19_0_ asc, col_1_0_ asc limit '100'
2019-04-15 10:27:17.017 CEST [17885] tad@desguacesAWO DETAIL: parameters: $1 = '100', $2 = '100', $3 = 'FCA81F56E9E643E096E86F5F379459FB', $4 = 'AV', $5 = '100'
Query2 [^] (10,810 bytes) 2019-04-15 12:31 [Show Content] [Hide Content]2019-04-15 10:27:32.840 CEST [17885] tad@desguacesAWO LOG: duration: 15789.956 ms execute <unnamed>/C_296: select distinct case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.OBAWO_Task_ID else coalesce(obawo_task0_.OBAWO_Receptionlist_ID, obawo_task0_.OBAWO_Pickinglist_ID) end as col_0_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Created else obawo_rece1_.Created end as col_1_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.M_Product_ID else null end as col_2_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then ad_column_identifier_std('m_product', product22_.M_Product_ID) else null end as col_3_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then trim(product22_.UPC) else null end as col_4_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then uom17_.C_UOM_ID else null end as col_5_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_getauomgtin(uom17_.C_UOM_ID, product22_.M_Product_ID) else null end as col_6_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_getauomconversionrate(uom17_.C_UOM_ID, product22_.M_Product_ID) else null end as col_7_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then uom24_.C_UOM_ID else null end as col_8_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes26_.M_AttributeSet_ID else null end as col_9_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes11_.M_AttributeSetInstance_ID else null end as col_10_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then attributes11_.Description else null end as col_11_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then m_getjsondescription(attributes11_.M_AttributeSetInstance_ID, 'Y', '140') else null end as col_12_0_, cast(null as char(1)) as col_13_0_, cast(null as char(1)) as col_14_0_, cast(null as char(1)) as col_15_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_Qty else null end as col_16_0_, cast(null as char(1)) as col_17_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_Locator_To_ID else null end as col_18_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator5_.Value else null end as col_19_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator5_.Barcode else null end as col_20_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.M_Locator_ID else null end as col_21_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.Value else null end as col_22_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then locator6_.Barcode else null end as col_23_0_, cast(null as char(1)) as col_24_0_, cast(null as char(1)) as col_25_0_, cast(null as char(1)) as col_26_0_, cast(null as char(1)) as col_27_0_, cast(null as char(1)) as col_28_0_, cast(null as char(1)) as col_29_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Expected_M_Refinventory_ID else null end as col_30_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg15_.Value else null end as col_31_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Confirmed_M_Refinventory_ID else null end as col_32_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg16_.Value else null end as col_33_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg10_.M_RefInventory_ID else null end as col_34_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then materialmg10_.Value else null end as col_35_0_, obawo_inte12_.IsExternalRouting as col_36_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte12_.Externalwarehouse_Scope else null end as col_37_0_, obawo_inte12_.IsRemoveReference as col_38_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte19_.OBAWO_Internal_Routing_Area_ID else null end as col_39_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte21_.OBAWO_Internal_Routing_Area_ID else null end as col_40_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_inte12_.OBAWO_Internal_Routing_ID else null end as col_41_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Obawo_Inv_Tran_Type_ID else null end as col_42_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_43_0_, case when obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_44_0_, obawo_task13_.OBAWO_Task_Type_ID as col_45_0_, obawo_task13_.Color as col_46_0_, obawo_task13_.Name as col_47_0_, obawo_task13_.Value as col_48_0_, obawo_task13_.Obawo_Base_Task_Type_ID as col_49_0_, obawo_base14_.Value as col_50_0_, obawo_base14_.Name as col_51_0_, obawo_batc2_.Obawo_Batchoftasks_ID as col_52_0_, obawo_batc2_.Documentno as col_53_0_, obawo_batc2_.Created as col_54_0_, obawo_rece1_.Obawo_Receptionlist_ID as col_55_0_, obawo_rece1_.Documentno as col_56_0_, obawo_rece1_.Created as col_57_0_, obawo_rece1_.Issotrx as col_58_0_, obawo_pick3_.Obawo_Pickinglist_ID as col_59_0_, obawo_pick3_.Documentno as col_60_0_, obawo_pick3_.Created as col_61_0_, materialmg4_.M_RefInventory_ID as col_62_0_, materialmg4_.Value as col_63_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then false else true end as col_64_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Priority else obawo_getchildtaskmaxpriority(coalesce(obawo_task0_.OBAWO_Receptionlist_ID, obawo_task0_.OBAWO_Pickinglist_ID), '100') end as col_65_0_, case when obawo_task0_.Behaveasgroup='N' and obawo_task0_.BehaveAsGroupWithRI='N' then obawo_task0_.Obawo_Travel_Sequence else '000000000' end as col_66_0_, coalesce(obawo_task0_.Document_Info, ' ') as col_67_0_ from OBAWO_task obawo_task0_ left outer join OBAWO_receptionlist obawo_rece1_ on obawo_task0_.OBAWO_Receptionlist_ID=obawo_rece1_.Obawo_Receptionlist_ID left outer join OBAWO_BatchOfTasks obawo_batc2_ on obawo_task0_.OBAWO_Batchoftasks_ID=obawo_batc2_.Obawo_Batchoftasks_ID left outer join OBAWO_Pickinglist obawo_pick3_ on obawo_task0_.OBAWO_Pickinglist_ID=obawo_pick3_.Obawo_Pickinglist_ID left outer join M_RefInventory materialmg4_ on obawo_pick3_.M_Refinventory_ID=materialmg4_.M_RefInventory_ID left outer join M_Locator locator5_ on obawo_task0_.Expected_Locator_To_ID=locator5_.M_Locator_ID left outer join M_Locator locator6_ on obawo_task0_.Expected_Locator_From_ID=locator6_.M_Locator_ID left outer join M_Locator locator7_ on obawo_task0_.Confirmed_Locator_To_ID=locator7_.M_Locator_ID left outer join M_Locator locator8_ on obawo_task0_.Confirmed_Locator_From_ID=locator8_.M_Locator_ID left outer join M_Storage_Detail materialmg9_ on obawo_task0_.M_Storage_Detail_ID=materialmg9_.M_Storage_Detail_ID left outer join M_RefInventory materialmg10_ on materialmg9_.M_RefInventory_ID=materialmg10_.M_RefInventory_ID left outer join M_AttributeSetInstance attributes11_ on obawo_task0_.Expected_Attribute_ID=attributes11_.M_AttributeSetInstance_ID left outer join OBAWO_internal_routing obawo_inte12_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte12_.OBAWO_Internal_Routing_ID inner join OBAWO_task_type obawo_task13_ on obawo_task0_.OBAWO_Task_Type_ID=obawo_task13_.OBAWO_Task_Type_ID inner join OBAWO_base_task_type obawo_base14_ on obawo_task13_.Obawo_Base_Task_Type_ID=obawo_base14_.OBAWO_Base_Task_Type_ID left outer join M_RefInventory materialmg15_ on obawo_task0_.Expected_M_Refinventory_ID=materialmg15_.M_RefInventory_ID left outer join M_RefInventory materialmg16_ on obawo_task0_.Confirmed_M_Refinventory_ID=materialmg16_.M_RefInventory_ID left outer join C_UOM uom17_ on obawo_task0_.C_Aum=uom17_.C_UOM_ID left outer join OBAWO_internal_routing obawo_inte18_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte18_.OBAWO_Internal_Routing_ID left outer join OBAWO_internal_routing_area obawo_inte19_ on obawo_inte18_.Internal_Routing_Area_From_ID=obawo_inte19_.OBAWO_Internal_Routing_Area_ID left outer join OBAWO_internal_routing obawo_inte20_ on obawo_task0_.OBAWO_Internal_Routing_ID=obawo_inte20_.OBAWO_Internal_Routing_ID left outer join OBAWO_internal_routing_area obawo_inte21_ on obawo_inte20_.Internal_Routing_Area_To_ID=obawo_inte21_.OBAWO_Internal_Routing_Area_ID left outer join M_Product product22_ on obawo_task0_.M_Product_ID=product22_.M_Product_ID left outer join M_Product product23_ on obawo_task0_.M_Product_ID=product23_.M_Product_ID left outer join C_UOM uom24_ on product23_.C_UOM_ID=uom24_.C_UOM_ID left outer join M_Product product25_ on obawo_task0_.M_Product_ID=product25_.M_Product_ID left outer join M_AttributeSet attributes26_ on product25_.M_AttributeSet_ID=attributes26_.M_AttributeSet_ID where obawo_task0_.AD_User_ID='100' and (obawo_task0_.OBAWO_Receptionlist_ID is null or obawo_task0_.OBAWO_Receptionlist_ID in ('3B4350AE39A64FA5A6846856E49810AC' , '4749FBD65E524807BABA3745FD27984C' , 'E68EAFE01FA64637B43DD34558E804BE' , 'EFDDA798E48F43D69FEC679B63477913')) and not (exists (select 1 from OBAWO_Error_Task obawo_erro27_ cross join OBAWO_errors obawo_erro28_ where obawo_erro27_.Obawo_Errors_ID=obawo_erro28_.Obawo_Errors_ID and obawo_erro27_.Obawo_Task_ID=obawo_task0_.OBAWO_Task_ID and obawo_erro28_.Taskstatus='N' and obawo_erro28_.Blocks='Y')) and obawo_task0_.M_Warehouse_ID='FCA81F56E9E643E096E86F5F379459FB' and obawo_task0_.Status='AV' order by col_65_0_ desc, col_66_0_, col_22_0_ asc, col_19_0_ asc, col_1_0_ asc limit '100'
2019-04-15 10:27:32.840 CEST [17885] tad@desguacesAWO DETAIL: parameters: $1 = '100', $2 = '100', $3 = '3B4350AE39A64FA5A6846856E49810AC', $4 = '4749FBD65E524807BABA3745FD27984C', $5 = 'E68EAFE01FA64637B43DD34558E804BE', $6 = 'EFDDA798E48F43D69FEC679B63477913', $7 = 'FCA81F56E9E643E096E86F5F379459FB', $8 = 'AV', $9 = '100'
explain1 [^] (30,543 bytes) 2019-04-15 12:31 [Show Content] [Hide Content]Limit (cost=16775.64..16790.14 rows=100 width=2415) (actual time=15016.023..15016.188 rows=4 loops=1)
-> Unique (cost=16775.64..16883.37 rows=743 width=2415) (actual time=15016.023..15016.187 rows=4 loops=1)
-> Sort (cost=16775.64..16777.49 rows=743 width=2415) (actual time=15016.017..15016.023 rows=137 loops=1)
Sort Key: (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.priority ELSE obawo_getchildtaskm
axpriority(COALESCE(obawo_task0_.obawo_receptionlist_id, obawo_task0_.obawo_pickinglist_id), '100'::character varying) END) DESC, (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar)
AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.obawo_travel_sequence ELSE '000000000'::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bp
char) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_tas
k0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator5_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwit
hri = 'N'::bpchar)) THEN obawo_task0_.created ELSE obawo_rece1_.created END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)
) THEN obawo_task0_.obawo_task_id ELSE COALESCE(obawo_task0_.obawo_receptionlist_id, obawo_task0_.obawo_pickinglist_id) END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND
(obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.m_product_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_tas
k0_.behaveasgroupwithri = 'N'::bpchar)) THEN ad_column_identifier_std('m_product'::character varying, product22_.m_product_id) ELSE NULL::character varying END), (CASE WHEN ((obawo_task
0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN btrim((product22_.upc)::text) ELSE NULL::text END), (CASE WHEN ((obawo_task0_.behaveasgroup =
'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN uom17_.c_uom_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (ob
awo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_getauomgtin(uom17_.c_uom_id, product22_.m_product_id) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgrou
p = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_getauomconversionrate(uom17_.c_uom_id, product22_.m_product_id) ELSE NULL::character varying END), (CAS
E WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN uom24_.c_uom_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0
_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes26_.m_attributeset_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.be
haveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes11_.m_attributesetinstance_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0
_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes11_.description ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveas
group = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN m_getjsondescription(attributes11_.m_attributesetinstance_id, 'Y'::character varying, '140'::character va
rying) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_qty ELS
E NULL::numeric END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_locator_to_id ELSE NULL::ch
aracter varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator5_.barcode ELSE NULL::character varying EN
D), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.m_locator_id ELSE NULL::character varying END), (CASE WHE
N ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.barcode ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.b
ehaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task
0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg15_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup
= 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.confirmed_m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveas
group = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg16_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpc
har) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg10_.m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar)
AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg10_.value ELSE NULL::character varying END), obawo_inte12_.isexternalrouting, (CASE WHEN ((obawo_task0_.behaveasgro
up = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte12_.externalwarehouse_scope ELSE NULL::character varying END), obawo_inte12_.isremovereference, (C
ASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte19_.obawo_internal_routing_area_id ELSE NULL::character varying
END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte21_.obawo_internal_routing_area_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte12_.obawo_internal_routing_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.obawo_inv_tran_type_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN false ELSE true END), (CASE WHEN (obawo_task0_.behaveasgroupwithri = 'N'::bpchar) THEN false ELSE true END), obawo_task13_.obawo_task_type_id, obawo_task13_.color, obawo_task13_.name, obawo_task13_.value, obawo_task13_.obawo_base_task_type_id, obawo_base14_.value, obawo_base14_.name, obawo_batc2_.obawo_batchoftasks_id, obawo_batc2_.documentno, obawo_batc2_.created, obawo_rece1_.obawo_receptionlist_id, obawo_rece1_.documentno, obawo_rece1_.created, obawo_rece1_.issotrx, obawo_pick3_.obawo_pickinglist_id, obawo_pick3_.documentno, obawo_pick3_.created, materialmg4_.m_refinventory_id, materialmg4_.value, (COALESCE(obawo_task0_.document_info, ' '::character varying))
Sort Method: quicksort Memory: 95kB
-> Hash Left Join (cost=1182.55..16740.21 rows=743 width=2415) (actual time=114.853..15014.148 rows=137 loops=1)
Hash Cond: ((product25_.m_attributeset_id)::text = (attributes26_.m_attributeset_id)::text)
-> Hash Left Join (cost=1160.84..15654.05 rows=743 width=2989) (actual time=7.324..71.957 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product25_.m_product_id)::text)
-> Nested Loop Left Join (cost=966.28..15457.54 rows=743 width=2956) (actual time=6.352..70.652 rows=137 loops=1)
-> Hash Left Join (cost=966.14..15339.03 r=743 width=2896) (actual time=6.323..69.615 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product23_.m_product_id)::text)
-> Hash Left Join (cost=771.58..15142.52 rows=743 width=2874) (actual time=5.539..68.486 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product22_.m_product_id)::text)
-> Nested Loop Anti Join (cost=577.02..14946.01 rows=743 width=2763) (actual time=4.192..66.833 rows=137 loops=1)
Join Filter: ((obawo_erro27_.obawo_task_id)::text = (obawo_task0_.obawo_task_id)::text)
-> Nested Loop Left Join (cost=576.88..14915.34 rows=743 width=2763) (actual time=4.188..66.595 rows=137 loops=1)
-> Hash Left Join (cost=576.74..14788.14 rows=743 width=2763) (actual time=4.179..66.138 rows=137 loops=1)
Hash Cond: ((obawo_task0_.confirmed_m_refinventory_id)::text = (materialmg16_.m_refinventory_id)::text)
-> Hash Left Join (cost=463.29..14672.74 rows=743 width=2754) (actual time=3.547..65.182 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_m_refinventory_id)::text = (materialmg15_.m_refinventory_id)::text)
-> Hash Left Join (cost=349.84..14557.34 rows=743 width=2745) (actual time=2.859..64.197 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte18_.obawo_internal_routing_id)::text)
-> Hash Left Join (cost=347.23..14544.51 rows=743 width=2696) (actual time=2.829..63.797 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte20_.obawo_internal_routing_id)::text)
-> Nested Loop (cost=344.61..14531.68 rows=743 width=2614) (actual time=2.794..63.465 rows=137 loops=1)
Join Filter: ((obawo_task13_.obawo_base_task_type_id)::text = (obawo_base14_.obawo_base_task_type_id)::text)
Rows Removed by Join Filter: 274
-> Nested Loop Left Join (cost=344.61..14486.77 rows=743 width=2378) (actual time=2.782..62.927 rows=137 loops=1)
-> Nested Loop Left Join (cost=344.19..8465.99 rows=743 width=2326 (actual time=2.757..48.432 rows=137 loops=1)
-> Hash Join (cost=343.91..8246.03 rows=743 width=2317) (actual time=2.742..42.608 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_task_type_id)::text = (obawo_task13_.obawo_task_type_id)::text)
-> Hash Left Join (cost=341.08..8241.02 rows=743 width=1812 (actual time=2.694..42.246 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte12_.obawo_internal_routing_id)::text)
-> Nested Loop Left Join (cost=339.74..8237.18 rows=743 width=1576) (actual time=2.668..41.902 rows=137 loops=1)
-> Hash Left Join (cost=339.32..2446.26 rows=743 width=1576) (actual time=2.637..7.614 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_locator_from_id)::text = (locator6_.m_locator_id)::text)
-> Hash Left Join (cost=337.96..2442.44 rows=743 width=1351) (actual time=2.621..7.332 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_locator_to_id)::text = (locator5_.m_locator_id)::text)
-> Hash Left Join (cost=336.60..2438.61 rows=743 width=1175) (actual time=2.596..7.035 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_batchoftasks_id)::text = (obawo_batc2_.obawo_batchoftasks_id)::text)
-> Hash Left Join (cost=109.27..2209.34 rows=743 width=1166) (actual time=0.719..4.898 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_receptionlist_id)::text = (obawo_rece1_.obawo_receptionlist_id)::text)
-> Hash Left Join (cost=97.92..2196.04 rows=743 width=990) (actual time=0.707..4.646 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_pickinglist_id)::text = (obawo_pick3_.obawo_pickinglist_id)::text)
-> Index Scan using obawo_task_status_ix on obawo_task obawo_task0_ (cost=0.42..2095.72 rows=743 width=846) (actual time=0.031..3.587 rows=137 loops=1)
Index Cond: ((status)::text = 'AV'::text)
Filter: (((ad_user_id)::text = '100'::text) AND ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text))
Rows Removed by Filter: 852
-> Hash (cost=97.30..97.30 rows=16 width=210) (actual time=0.665..0.665 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Right Join (cost=1.36..97.30 rows=16 width=210) (actual time=0.031..0.658 rows=16 loops=1)
Hash Cond: ((materialmg4_.m_refinventory_id)::text = (obawo_pick3_.m_refinventory_id)::text)
-> Seq Scan on m_refinventory materialmg4_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.008..0.429 rows=2020 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=250) (actual time=0.016..0.016 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on obawo_pickinglist obawo_pick3_ (cost=0.00..1.16 rows=16 width=250) (actual time=0.007..0.009 rows=16 loops=1)
-> Hash (cost=10.60..10.60 rows=60 width=176) (actual time=0.002..0.002 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on obawo_receptionlist obawo_rece1_ (cost=0.00..10.60 rows=60 width=176) (actual time=0.002..0.002 rows=0 loops=1)
-> Hash (cost=176.59..176.59 rows=4059 width=42) (actual time=1.865..1.865 rows=4059 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 350kB
-> Seq Scan on obawo_batchoftasks obawo_batc2_ (cost=0.00..176.59 rows=4059 width=42) (actual time=0.008..1.144 rows=4059 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=258) (actual time=0.014..0.014 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on m_locator locator5_ (cost=0.00..1.16 rows=16 width=258) (actual time=0.008..0.010 rows=16 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=258) (actual time=0.008..0.008 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on m_locator locator6_ (cost=0.00..1.16 rows=16 width=258) (actual time=0.002..0.004 rows=16 loops=1)
-> Index Scan using m_storage_detail_key on m_storage_detail materialmg9_ (cost=0.42..7.79 rows=1 width=66) (actual time=0.245..0.245 rows=1 loops=137)
Index Cond: ((obawo_task0_.m_storage_detail_id)::text = (m_storage_detail_id)::text)
-> Hash (cost=1.15..1.15 rows=15 width=236) (actual time=0.014..0.014 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on obawo_internal_routing obawo_inte12_ (cost=0.00..1.15 rows=15 width=236) (actual time=0.007..0.010 rows=15 loops=1)
-> Hash (cost=2.37..2.37 rows=37 width=538) (actual time=0.036..0.036 rows=37 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on obawo_task_type obawo_task13_ (cost=0.00..2.37 rows=37 width=538) (actual time=0.012..0.024 rows=37 loops=1)
-> Index Scan using m_refinventory_key on m_refinventory materialmg10_ (cost=0.28..0.30 rows=1 width=42) (actual time=0.039..0.039 rows=1 loops=137)
Index Cond: ((materialmg9_.m_refinventory_id)::text = (m_refinventory_id)::text)
-> Index Scan using m_attributesetinstance_key on m_attributesetinstance attributes11_ (cost=0.42..8.10 rows=1 width=85) (actual time=0.102..0.102 rows=1 loops=137)
Index Cond: ((obawo_task0_.expected_attribute_id)::text = (m_attributesetinstance_id)::text)
-> Materialize (cost=0.00..1.06 rows=4 width=318) (actual time=0.001..0.001 rows=3 loops=137)
-> Seq Scan on obawo_base_task_type obawo_base14_ (cost=0.00..1.04 rows=4 width=318) (actual time=0.006..0.006 rows=3 loops=1)
-> Hash (cost=2.43..2.43 rows=15 width=164) (actual time=0.028..0.028 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Hash Left Join (cost=1.23..2.43 rows=15 width=164) (actual time=0.019..0.025 rows=15 loops=1)
Hash Cond: ((obawo_inte20_.internal_routing_area_to_id)::text = (obawo_inte21_.obawo_internal_routing_area_id)::text)
-> Seq Scan on obawo_internal_routing obawo_inte20_ (cost=0.00..1.15 rows=15 width=164) (actual time=0.001..0.002 rows=15 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=82) (actual time=0.011..0.011 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on obawo_internal_routing_area obawo_inte21_ (cost=0.00..1.10 rows=10 width=82) (actual time=0.006..0.007 rows=10 loops=1)
-> Hash (cost=2.43..2.43 rows=15 width=164) (actual time=0.023..0.023 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Hash Left Join (cost=1.23..2.43 rows=15 width=164) (actual time=0.013..0.018 rows=15 loops=1)
Hash Cond: ((obawo_inte18_.internal_routing_area_from_id)::text = (obawo_inte19_.obawo_internal_routing_area_id)::text)
-> Seq Scan on obawo_internal_routing obawo_inte18_ (cost=0.00..1.15 rows=15 width=164) (actual time=0.001..0.002 rows=15 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=82) (actual time=0.005..0.005 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on obawo_internal_routing_area obawo_inte19_ (cost=0.00..1.10 rows=10 width=82) (actual time=0.001..0.002 rows=10 loops=1)
-> Hash (cost=88.20..88.20 rows=2020 width=42) (actual time=0.677..0.677 rows=2020 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 163kB
-> Seq Scan on m_refinventory materialmg15_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.002..0.349 rows=2020 loops=1)
-> Hash (cost=88.20..88.20 rows=2020 width=42) (actual time=0.624..0.624 rows=2020 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 163kB
-> Seq Scan on m_refinventory materialmg16_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.002..0.298 rows=2020 loops=1)
-> Index Only Scan using c_uom_key on c_uom uom17_ (cost=0.14..0.17 rows=1 width=82) (actual time=0.001..0.001 rows=0 loops=137)
Index Cond: (c_uom_id = (obawo_task0_.c_aum)::text)
Heap Fetches: 0
-> Materialize (cost=0.14..19.53 rows=1 width=82) (actual time=0.000..0.000 rows=0 loops=137)
-> Nested Loop (cost=0.14..19.52 rows=1 width=82) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on obawo_errors obawo_erro28_ (cost=0.00..11.35 rows=1 width=82) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (((taskstatus)::text = 'N'::text) AND (blocks = 'Y'::bpchar))
-> Index Scan using obawo_idx_errortask_error on obawo_error_task obawo_erro27_ (cost=0.14..8.16 rows=1 width=164) (never executed)
Index Cond: ((obawo_errors_id)::text = (obawo_erro28_.obawo_errors_id)::text)
-> Hash (cost=165.36..165.36 rows=2336 width=111) (actual time=1.332..1.332 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 181kB
-> Seq Scan on m_product product22_ (cost=0.00..165.36 rows=2336 width=111) (actual time=0.008..0.967 rows=2336 loops=1)
-> Hash (cost=165.36..165.36 rows=2336 width=55) (actual time=0.767..0.767 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 233kB
-> Seq Scan on m_product product23_ (cost=0.00..165.36 rows=2336 width=55) (actual time=0.003..0.378 rows=2336 loops=1)
-> Index Only Scan using c_uom_key on c_uom uom24_ (cost=0.14..0.16 rows=1 width=82) (actual time=0.004..0.004 rows=1 loops=137)
Index Cond: (c_uom_id = (product23_.c_uom_id)::text)
Heap Fetches: 137
-> Hash (cost=165.36..165.36 rows=2336 width=66) (actual time=0.955..0.955 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 205kB
-> Seq Scan on m_product product25_ (cost=0.00..165.36 rows=2336 width=66) (actual time=0.002..0.568 rows=2336 loops=1)
-> Hash (cost=16.87..16.87 rows=387 width=32) (actual time=0.150..0.150 rows=387 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Seq Scan on m_attributeset attributes26_ (cost=0.00..16.87 rows=387 width=32) (actual time=0.004..0.090 rows=387 loops=1)
Planning time: 29.236 ms
Execution time: 15016.939 ms
(131 rows)
explain2 [^] (30,765 bytes) 2019-04-15 12:31 [Show Content] [Hide Content]Limit (cost=16781.16..16795.66 rows=100 width=2415) (actual time=14457.901..14458.162 rows=4 loops=1)
-> Unique (cost=16781.16..16888.89 rows=743 width=2415) (actual time=14457.901..14458.161 rows=4 loops=1)
-> Sort (cost=16781.16..16783.01 rows=743 width=2415) (actual time=14457.899..14457.911 rows=137 loops=1)
Sort Key: (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.priority ELSE ob
awo_getchildtaskmaxpriority(COALESCE(obawo_task0_.obawo_receptionlist_id, obawo_task0_.obawo_pickinglist_id), '100'::character varying) END) DESC, (CASE WHEN ((obawo_ta
sk0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.obawo_travel_sequence ELSE '000000000'::character varying END)
, (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.value ELSE NULL::character varying END), (
CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator5_.value ELSE NULL::character varying END), (CAS
E WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.created ELSE obawo_rece1_.created END), (CASE
WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.obawo_task_id ELSE COALESCE(obawo_task0_.obawo
_receptionlist_id, obawo_task0_.obawo_pickinglist_id) END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar))
THEN obawo_task0_.m_product_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::b
pchar)) THEN ad_column_identifier_std('m_product'::character varying, product22_.m_product_id) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgrou
p = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN btrim((product22_.upc)::text) ELSE NULL::text END), (CASE WHEN ((obawo_task0_.behaveasgroup
= 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN uom17_.c_uom_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_getauomgtin(uom17_.c_uom_id, product22_.m_product_id) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_getauomconversionrate(uom17_.c_uom_id, product22_.m_product_id) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN uom24_.c_uom_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes26_.m_attributeset_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes11_.m_attributesetinstance_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN attributes11_.description ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN m_getjsondescription(attributes11_.m_attributesetinstance_id, 'Y'::character varying, '140'::character varying) ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_qty ELSE NULL::numeric END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_locator_to_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator5_.barcode ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.m_locator_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN locator6_.barcode ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.expected_m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg15_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_task0_.confirmed_m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg16_.value ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg10_.m_refinventory_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN materialmg10_.value ELSE NULL::character varying END), obawo_inte12_.isexternalrouting, (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte12_.externalwarehouse_scope ELSE NULL::character varying END), obawo_inte12_.isremovereference, (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte19_.obawo_internal_routing_area_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte21_.obawo_internal_routing_area_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithri = 'N'::bpchar)) THEN obawo_inte12_.obawo_internal_routing_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.behaveasgroupwithr
i = 'N'::bpchar)) THEN obawo_task0_.obawo_inv_tran_type_id ELSE NULL::character varying END), (CASE WHEN ((obawo_task0_.behaveasgroup = 'N'::bpchar) AND (obawo_task0_.b
ehaveasgroupwithri = 'N'::bpchar)) THEN false ELSE true END), (CASE WHEN (obawo_task0_.behaveasgroupwithri = 'N'::bpchar) THEN false ELSE true END), obawo_task13_.obawo
_task_type_id, obawo_task13_.color, obawo_task13_.name, obawo_task13_.value, obawo_task13_.obawo_base_task_type_id, obawo_base14_.value, obawo_base14_.name, obawo_batc2
_.obawo_batchoftasks_id, obawo_batc2_.documentno, obawo_batc2_.created, obawo_rece1_.obawo_receptionlist_id, obawo_rece1_.documentno, obawo_rece1_.created, obawo_rece1_
.issotrx, obawo_pick3_.obawo_pickinglist_id, obawo_pick3_.documentno, obawo_pick3_.created, materialmg4_.m_refinventory_id, materialmg4_.value, (COALESCE(obawo_task0_.d
ocument_info, ' '::character varying))
Sort Method: quicksort Memory: 95kB
-> Hash Left Join (cost=1182.55..16745.73 rows=743 width=2415) (actual time=112.473..14455.705 rows=137 loops=1)
Hash Cond: ((product25_.m_attributeset_id)::text = (attributes26_.m_attributeset_id)::text)
-> Hash Left Join (cost=1160.84..15659.57 rows=743 width=2989) (actual time=6.703..61.972 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product25_.m_product_id)::text)
-> Nested Loop Left Join (cost=966.28..15463.06 rows=743 width=2956) (actual time=5.734..60.731 rows=137 loops=1)
-> Hash Left Join (cost=966.14..15344.55 rows=743 width=2896) (actual time=5.721..59.793 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product23_.m_product_id)::text)
-> Hash Left Join (cost=771.58..15148.04 rows=743 width=2874) (actual time=4.883..58.691 rows=137 loops=1)
Hash Cond: ((obawo_task0_.m_product_id)::text = (product22_.m_product_id)::text)
-> Nested Loop Anti Join (cost=577.02..14951.53 rows=743 width=2763) (actual time=3.763..57.303 rows=137 loops=1)
Join Filter: ((obawo_erro27_.obawo_task_id)::text = (obawo_task0_.obawo_task_id)::text)
-> Nested Loop Left Join (cost=576.88..14920.86 rows=743 width=2763) (actual time=3.760..57.118 rows=137 loops=1)
-> Hash Left Join (cost=576.74..14793.66 rows=743 width=2763) (actual time=3.753..56.710 rows=137 loops=1)
Hash Cond: ((obawo_task0_.confirmed_m_refinventory_id)::text = (materialmg16_.m_refinventory_id)::text)
-> Hash Left Join (cost=463.29..14678.26 rows=743 width=2754) (actual time=3.131..55.822 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_m_refinventory_id)::text = (materialmg15_.m_refinventory_id)::text)
-> Hash Left Join (cost=349.84..14562.86 rows=743 width=2745) (actual time=2.446..54.869 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte18_.obawo_internal_routing_id)::text)
-> Hash Left Join (cost=347.23..14550.03 rows=743 width=2696) (actual time=2.418..54.561 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte20_.obawo_internal_routing_id)::text)
-> Nested Loop (cost=344.61..14537.20 rows=743 width=2614) (actual time=2.382..54.239 rows=137 loops=1)
Join Filter: ((obawo_task13_.obawo_base_task_type_id)::text = (obawo_base14_.obawo_base_task_type_id)::text)
Rows Removed by Join Filter: 274
-> Nested Loop Left Join (cost=344.61..14492.29 rows=743 width=2378) (actual time=2.375..53.794 rows=137 loops=1)
-> Nested Loop Left Join (cost=344.19..8471.51 rows=743 width=2326) (actual time=2.350..38.726 rows=137 loops=1)
-> Hash Join (cost=343.91..8251.55 rows=743 width=2317) (actual time=2.335..34.063 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_task_type_id)::text = (obawo_task13_.obawo_task_type_id)::text)
-> Hash Left Join (cost=341.08..8246.54 rows=743 width=1812) (actual time=2.293..33.754 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_internal_routing_id)::text = (obawo_inte12_.obawo_internal_routing_id)::text)
-> Nested Loop Left Join (cost=339.74..8242.70 rows=743 width=1576 (actual time=2.270..33.477 rows=137 loops=1)
-> Hash Left Join (cost=339.32..2451.78 rows=743 width=1576 (actual time=2.246..7.361 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_locator_from_id)::text = (locator6_.m_locator_id)::text)
-> Hash Left Join (cost=337.96..2447.96 rows=743 width=1351 (actual time=2.230..7.107 rows=137 loops=1)
Hash Cond: ((obawo_task0_.expected_locator_to_id)::text = (locator5_.m_locator_id)::text)
-> Hash Left Join (cost=336.60..2444.13 rows=743 width=1175) (actual time=2.197..6.813 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_batchoftasks_id)::text = (obawo_batc2_.obawo_batchoftasks_id)::text)
-> Hash Left Join (cost=109.27..2214.86 rows=743 width=1166) (actual time=0.564..4.901 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_receptionlist_id)::text = (obawo_rece1_.obawo_receptionlist_id)::text)
-> Hash Left Join (cost=97.92..2201.56 rows=743 width=990) (actual time=0.555..4.703 rows=137 loops=1)
Hash Cond: ((obawo_task0_.obawo_pickinglist_id)::text = (obawo_pick3_.obawo_pickinglist_id)::text)
-> Index Scan using obawo_task_status_ix on obawo_task obawo_task0_ (cost=0.42..2101.24 rows=743 width=846) (actual time=0.029..3.836 rows=137 loops=1)
Index Cond: ((status)::text = 'AV'::text)
Filter: (((ad_user_id)::text = '100'::text) AND ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text) AND ((obawo_receptionlist_id IS NULL) OR ((obawo_receptionlist_id)::text = ANY ('{3B4350AE39A64FA5A6846856E49810AC,4749FBD65E524807BABA3745FD27984C,E68EAFE01FA64637B43DD34558E804BE,EFDDA798E48F43D69FEC679B63477913}'::text[]))))
Rows Removed by Filter: 852
-> Hash (cost=97.30..97.30 rows=16 width=210) (actual time=0.518..0.518 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Right Join (cost=1.36..97.30 rows=16 width=210) (actual time=0.025..0.510 rows=16 loops=1)
Hash Cond: ((materialmg4_.m_refinventory_id)::text = (obawo_pick3_.m_refinventory_id)::text)
-> Seq Scan on m_refinventory materialmg4_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.005..0.299 rows=2020 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=250) (actual time=0.013..0.014 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on obawo_pickinglist obawo_pick3_ (cost=0.00..1.16 rows=16 width=250) (actual time=0.006..0.008 rows=16 loops=1)
-> Hash (cost=10.60..10.60 rows=60 width=176) (actual time=0.002..0.002 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on obawo_receptionlist obawo_rece1_ (cost=0.00..10.60 rows=60 width=176) (actual time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=176.59..176.59 rows=4059 width=42 (actual time=1.619..1.619 rows=4059 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 350kB
-> Seq Scan on obawo_batchoftasks obawo_batc2_ (cost=0.00..176.59 rows=4059 width=42) (actual time=0.006..0.933 rows=4059 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=258) (actual time=0.016..0.016 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on m_locator locator5_ (cost=0.00..1.16 rows=16 width=258) (actual time=0.006..0.009 rows=16 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=258) (actual time=0.009..0.009 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on m_locator locator6_ (cost=0.00..1.16 rows=16 width=258) (actual time=0.002..0.004 rows=16 loops=1)
-> Index Scan using m_storage_detail_key on m_storage_detail materialmg9_ (cost=0.42..7.79 rows=1 width=66) (actual time=0.186..0.186 rows=1 loops=137)
Index Cond: ((obawo_task0_.m_storage_detail_id)::text = (m_storage_detail_id)::text)
-> Hash (cost=1.15..1.15 rows=15 width=236) (actual time=0.014..0.014 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on obawo_internal_routing obawo_inte12_ (cost=0.00..1.15 rows=15 width=236) (actual time=0.006..0.008 rows=15 loops=1)
-> Hash (cost=2.37..2.37 rows=37 width=538) (actual time=0.035..0.035 rows=37 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on obawo_task_type obawo_task13_ (cost=0.00..2.37 rows=37 width=538) (actual time=0.015..0.024 rows=37 loops=1)
-> Index Scan using m_refinventory_key on m_refinventory materialmg10_ (cost=0.28..0.30 rows=1 width=42) (actual time=0.031..0.031 rows=1 loops=137)
Index Cond: ((materialmg9_.m_refinventory_id)::text = (m_refinventory_id)::text)
-> Index Scan using m_attributesetinstance_key on m_attributesetinstance attributes11_ (cost=0.42..8.10 rows=1 width=85) (actual time=0.107..0.107 rows=1 loops=137)
Index Cond: ((obawo_task0_.expected_attribute_id)::text = (m_attributesetinstance_id)::text)
-> Materialize (cost=0.00..1.06 rows=4 width=318) (actual time=0.001..0.001 rows=3 loops=137)
-> Seq Scan on obawo_base_task_type obawo_base14_ (cost=0.00..1.04 rows=4 width=318 (actual time=0.003..0.003 rows=3 loops=1)
-> Hash (cost=2.43..2.43 rows=15 width=164) (actual time=0.028..0.028 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Hash Left Join (cost=1.23..2.43 rows=15 width=164) (actual time=0.018..0.023 rows=15 loops=1)
Hash Cond: ((obawo_inte20_.internal_routing_area_to_id)::text = (obawo_inte21_.obawo_internal_routing_area_id)::text)
-> Seq Scan on obawo_internal_routing obawo_inte20_ (cost=0.00..1.15 rows=15 width=164 (actual time=0.001..0.002 rows=15 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=82) (actual time=0.010..0.010 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on obawo_internal_routing_area obawo_inte21_ (cost=0.00..1.10 rows=10 width=82) (actual time=0.006..0.007 rows=10 loops=1)
-> Hash (cost=2.43..2.43 rows=15 width=164) (actual time=0.022..0.022 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Hash Left Join (cost=1.23..2.43 rows=15 width=164) (actual time=0.013..0.018 rows=15 loops=1)
Hash Cond: ((obawo_inte18_.internal_routing_area_from_id)::text = (obawo_inte19_.obawo_internal_routing_area_id)::text)
-> Seq Scan on obawo_internal_routing obawo_inte18_ (cost=0.00..1.15 rows=15 width=164) (actual time=0.001..0.002 rows=15 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=82) (actual time=0.005..0.005 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on obawo_internal_routing_area obawo_inte19_ (cost=0.00..1.10 rows=10 width=82) (actual time=0.001..0.002 rows=10 loops=1)
-> Hash (cost=88.20..88.20 rows=2020 width=42) (actual time=0.676..0.676 rows=2020 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 163kB
-> Seq Scan on m_refinventory materialmg15_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.004..0.328 rows=2020 loops=1)
-> Hash (cost=88.20..88.20 rows=2020 width=42) (actual time=0.614..0.614 rows=2020 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 163kB
-> Seq Scan on m_refinventory materialmg16_ (cost=0.00..88.20 rows=2020 width=42) (actual time=0.002..0.277 rows=2020 loops=1)
-> Index Only Scan using c_uom_key on c_uom uom17_ (cost=0.14..0.17 rows=1 width=82) (actual time=0.001..0.001 rows=0 loops=137)
Index Cond: (c_uom_id = (obawo_task0_.c_aum)::text)
Heap Fetches: 0
-> Materialize (cost=0.14..19.53 rows=1 width=82) (actual time=0.000..0.000 rows=0 loops=137)
-> Nested Loop (cost=0.14..19.52 rows=1 width=82) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on obawo_errors obawo_erro28_ (cost=0.00..11.35 rows=1 width=82) (actual time=0.001..0.002 rows=0 loops=1)
Filter: (((taskstatus)::text = 'N'::text) AND (blocks = 'Y'::bpchar))
-> Index Scan using obawo_idx_errortask_error on obawo_error_task obawo_erro27_ (cost=0.14..8.16 rows=1 width=164) (never executed)
Index Cond: ((obawo_errors_id)::text = (obawo_erro28_.obawo_errors_id)::text)
-> Hash (cost=165.36..165.36 rows=2336 width=111) (actual time=1.103..1.103 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 181kB
-> Seq Scan on m_product product22_ (cost=0.00..165.36 rows=2336 width=111) (actual time=0.006..0.742 rows=2336 loops=1)
-> Hash (cost=165.36..165.36 rows=2336 width=55) (actual time=0.825..0.825 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 233kB
-> Seq Scan on m_product product23_ (cost=0.00..165.36 rows=2336 width=55) (actual time=0.002..0.413 rows=2336 loops=1)
-> Index Only Scan using c_uom_key on c_uom uom24_ (cost=0.14..0.16 rows=1 width=82) (actual time=0.004..0.004 rows=1 loops=137)
Index Cond: (c_uom_id = (product23_.c_uom_id)::text)
Heap Fetches: 137
-> Hash (cost=165.36..165.36 rows=2336 width=66) (actual time=0.954..0.954 rows=2336 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 205kB
-> Seq Scan on m_product product25_ (cost=0.00..165.36 rows=2336 width=66) (actual time=0.002..0.574 rows=2336 loops=1)
-> Hash (cost=16.87..16.87 rows=387 width=32) (actual time=0.135..0.135 rows=387 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Seq Scan on m_attributeset attributes26_ (cost=0.00..16.87 rows=387 width=32) (actual time=0.003..0.076 rows=387 loops=1)
Planning time: 28.144 ms
Execution time: 14458.689 ms
(131 rows)
 explain1.png [^] (332,635 bytes) 2019-04-15 12:31 explain2.png [^] (312,940 bytes) 2019-04-15 12:31 | |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||||
|
|||||||||||||||||
 Relationships |
|
Notes |
|
|
(0112298) hgbot (developer) 2019-05-31 14:29 |
Repository: erp/pmods/org.openbravo.warehouse.advancedwarehouseoperations Changeset: 766c9914b537b9de245df97d1182d17461725afa Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> Date: Fri May 31 14:24:40 2019 +0200 URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.advancedwarehouseoperations/rev/766c9914b537b9de245df97d1182d17461725afa [^] Related to issue 40615: Added partial index over AD_User_ID This partial index will help to search for the available tasks by a given user. The index should be small as it's only working for tasks in available status (which should be just a few in a normal environment). Note that this index only works as designed in Postgres. In Oracle it will automatically be transformed to a (partial) index over the AD_User_ID, which is also good enough to improve the queries for getting the user's tasks. --- M src-db/database/model/tables/OBAWO_TASK.xml --- |
|
(0112300) vmromanos (manager) 2019-05-31 14:45 |
Issue rejected: it is working fine in AWO tip (to be published in 3.0PR19Q3). The two issues related are the ones that actually fixes it. The main problem was in the usage of OBAWO_GETCHILDTASKMAXPRIORITY db function, that is efficient enough for few records, but which adds a heavy performance penalty when it's run for hundreds of records in the same transaction. From one side the 0039438 changes the db function implementation so it can properly works with both Picking and Reception/Issue lists. From another side, as part of 3.0PR19Q3, we have redefined the volatility of every db function available in Openbravo (Core and external modules). As part of this exercise we changed this function's volatility (and actually many other functions more) from VOLATILE to STABLE, which adds a very important performance improvement in this scenario. See 0040865 for details. Only by applying the changesets for issue 0039438, and by redefining the OBAWO_GETCHILDTASKMAXPRIORITY volatility to STABLE directly in PG, the time to load in the customer instance goes from more than 50 seconds to few milliseconds. This is exactly what should be manually done in case an upgrade to 3.0PR19Q3 is not possible. Note that in this case the STABLE volatility would be lost in case of further database updates from source code. Finally, as part of the review, we have added a new partial index in the OBAWO_Task table over the AD_USER_ID, which will help to get faster the available tasks for a given user. Note that this index is actually not needed for fixing this concrete issue, but might improve performance in other related flows. |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2019-04-15 12:29 | joniturralde93 | New Issue | |
| 2019-04-15 12:29 | joniturralde93 | Assigned To | => Triage Finance |
| 2019-04-15 12:29 | joniturralde93 | File Added: Query1 | |
| 2019-04-15 12:29 | joniturralde93 | Resolution time | => 1554501600 |
| 2019-04-15 12:31 | joniturralde93 | File Added: Query2 | |
| 2019-04-15 12:31 | joniturralde93 | File Added: explain1 | |
| 2019-04-15 12:31 | joniturralde93 | File Added: explain2 | |
| 2019-04-15 12:31 | joniturralde93 | File Added: explain1.png | |
| 2019-04-15 12:31 | joniturralde93 | File Added: explain2.png | |
| 2019-04-15 12:31 | joniturralde93 | Description Updated | View Revisions |
| 2019-04-15 12:42 | Sandrahuguet | Assigned To | Triage Finance => guilleaer |
| 2019-04-15 12:43 | joniturralde93 | Resolution time | 1554501600 => 1557093600 |
| 2019-05-08 13:28 | marvintm | Resolution time | 1557093600 => 1558735200 |
| 2019-05-27 10:58 | marvintm | Resolution time | 1558735200 => 1559599200 |
| 2019-05-30 12:38 | vmromanos | Assigned To | guilleaer => vmromanos |
| 2019-05-30 12:38 | vmromanos | Status | new => acknowledged |
| 2019-05-31 12:20 | vmromanos | Status | acknowledged => scheduled |
| 2019-05-31 12:20 | vmromanos | Relationship added | related to 0039438 |
| 2019-05-31 13:08 | vmromanos | Relationship added | related to 0040865 |
| 2019-05-31 14:29 | hgbot | Checkin | |
| 2019-05-31 14:29 | hgbot | Note Added: 0112298 | |
| 2019-05-31 14:45 | vmromanos | Review Assigned To | => vmromanos |
| 2019-05-31 14:45 | vmromanos | Note Added: 0112300 | |
| 2019-05-31 14:45 | vmromanos | Status | scheduled => closed |
| 2019-05-31 14:46 | vmromanos | Resolution | open => unable to reproduce |
| 2022-09-06 17:18 | caristu | Category | Advance Warehouse Operations => Advanced Warehouse Operations |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |