Load/ Reload of delivery Days window takes too long.

[metas-mk]

Is this a bug or feature request?

Feature Request

What is the current behavior?

The Load/ reload of delivery days window takes much too long. https://w101.metasfresh.com:8443/window/540110

Belongs to this frontend issue: metasfresh/metasfresh-webui-frontend-legacy#847

[teosarca]

The SQL query which takes ages is:

-- Duration: 1.298e+04 ms (Avg. 1.298e+04 ms / 1 executions)
-- Stacktrace:  <~~~ SqlViewDataRepository.retrievePage:307 <- DefaultView.getPage:253 <- ViewRestController.getViewData:191 <~~~ WebConfig$1.doFilter:82 <~~~ ServletLoggingFilter.doFilter:89 <~~~ CORSFilter.doFilter:85 <~~~ CORSFilter.doFilter:85

SELECT master.*,

  (SELECT NULLIF (TRIM(AD_Org.Name), '') FROM AD_Org WHERE AD_Org.AD_Org_ID=master.AD_Org_ID ) AS AD_Org_ID$Display,

  (SELECT NULLIF (TRIM(M_TourVersion.Name), '') FROM M_TourVersion WHERE M_TourVersion.M_TourVersion_ID=master.M_TourVersion_ID ) AS M_TourVersion_ID$Display,

  (SELECT concat_ws('_', NULLIF (TRIM(C_BPartner.Name), ''), NULLIF (TRIM(C_BPartner.Value), '')) FROM C_BPartner WHERE C_BPartner.C_BPartner_ID=master.C_BPartner_ID ) AS C_BPartner_ID$Display,

  (SELECT NULLIF (TRIM(C_BPartner_Location.Name),'') FROM C_BPartner_Location WHERE C_BPartner_Location.C_BPartner_Location_ID=master.C_BPartner_Location_ID ) AS C_BPartner_Location_ID$Display,

  (SELECT NULLIF (TRIM(M_Tour.Name), '') FROM M_Tour WHERE M_Tour.M_Tour_ID=master.M_Tour_ID ) AS M_Tour_ID$Display,

  NULL AS _sel_Parent_ID
FROM
  (SELECT sum(M_DeliveryDay.QtyDelivered_TU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                   ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyDelivered_TU_Accum,
          sum(M_DeliveryDay.QtyDelivered_LU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                   ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyDelivered_LU_Accum,
          M_DeliveryDay.M_Tour_Instance_ID AS M_Tour_Instance_ID,
          M_DeliveryDay.QtyDelivered_TU AS QtyDelivered_TU,
          M_DeliveryDay.QtyDelivered_LU AS QtyDelivered_LU,
          M_DeliveryDay.SeqNo AS SeqNo,
          sum(M_DeliveryDay.QtyToDeliver_TU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                   ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyToDeliver_TU_Accum,
          M_DeliveryDay.QtyToDeliver_TU AS QtyToDeliver_TU,
          sum(M_DeliveryDay.QtyToDeliver_LU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                   ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyToDeliver_LU_Accum,
          M_DeliveryDay.QtyToDeliver_LU AS QtyToDeliver_LU,
          M_DeliveryDay.DeliveryDateTimeMax AS DeliveryDateTimeMax,
          sum(M_DeliveryDay.QtyOrdered_TU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                 ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyOrdered_TU_Accum,
          M_DeliveryDay.QtyOrdered_TU AS QtyOrdered_TU,
          sum(M_DeliveryDay.QtyOrdered_LU) over (partition BY M_DeliveryDay.M_Tour_Instance_ID
                                                 ORDER BY SeqNo nulls FIRST, M_DeliveryDay.M_DeliveryDay_ID) AS QtyOrdered_LU_Accum,
          M_DeliveryDay.QtyOrdered_LU AS QtyOrdered_LU,
          M_DeliveryDay.AD_Org_ID AS AD_Org_ID,
          M_DeliveryDay.M_TourVersion_ID AS M_TourVersion_ID,
          M_DeliveryDay.C_BPartner_ID AS C_BPartner_ID,
          M_DeliveryDay.C_BPartner_Location_ID AS C_BPartner_Location_ID,
          M_DeliveryDay.DeliveryDate AS DeliveryDate,
          M_DeliveryDay.BufferHours AS BufferHours,
          M_DeliveryDay.IsManual AS IsManual,
          M_DeliveryDay.IsActive AS IsActive,
          M_DeliveryDay.Processed AS Processed,
          M_DeliveryDay.IsToBeFetched AS IsToBeFetched,
          M_DeliveryDay.Updated AS Updated,
          M_DeliveryDay.UpdatedBy AS UpdatedBy,
          M_DeliveryDay.Created AS Created,
          M_DeliveryDay.CreatedBy AS CreatedBy,
          M_DeliveryDay.AD_Client_ID AS AD_Client_ID,
          M_DeliveryDay.M_Tour_ID AS M_Tour_ID,
          M_DeliveryDay.M_DeliveryDay_ID AS M_DeliveryDay_ID,
          sel.Line AS _sel_SeqNo,
          sel.UUID AS _sel_UUID,
          sel.Record_ID AS _sel_Record_ID
   FROM T_WEBUI_ViewSelection sel
   LEFT OUTER JOIN M_DeliveryDay ON (M_DeliveryDay.M_DeliveryDay_ID = sel.Record_ID)
) master
WHERE _sel_UUID='540110-37a69e92-94a5-4d70-acdc-bcb3db6f1f75'
  AND _sel_SeqNo BETWEEN 1 AND 20
ORDER BY _sel_SeqNo -- Parameters[3]: {1=540110-37a69e92-94a5-4d70-acdc-bcb3db6f1f75, 2=1, 3=20}

Query plan:

[teosarca]

Update: it seems postgresql was optimizing wrongly the generated SQL for selecting view page.
And it seems that's happening when we have some SQL columns which are using window functions.

After moving the UUID filtering closer to T_WEBUI_ViewSelection table everything is fast again.

[metas-lc]

IT
opened window/540110 => instantly loaded
refresh => instantly refreshed