Openbravo Issue Tracking System - Retail Modules | |||||
| View Issue Details | |||||
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0043081 | Retail Modules | Web POS | public | 2020-01-31 11:53 | 2020-02-06 10:36 |
| Reporter | ALopetegui | ||||
| Assigned To | Retail | ||||
| Priority | high | Severity | major | Reproducibility | have not tried |
| Status | new | Resolution | open | ||
| Platform | OS | 5 | OS Version | ||
| Product Version | |||||
| Target Version | Fixed in Version | ||||
| Merge Request Status | |||||
| Review Assigned To | |||||
| OBNetwork customer | |||||
| Support ticket | |||||
| Regression level | |||||
| Regression date | |||||
| Regression introduced in release | |||||
| Regression introduced by commit | |||||
| Triggers an Emergency Pack | No | ||||
| Summary | 0043081: In the Login, preference load has bad performance | ||||
| Description | In the login, the preferences are loaded filtering by VisibleAt_Org_ID, VisibleAt_Role_ID and VisibleAt_Client_ID. However, there is not any index in those columns, and the query makes a sequential scan of all ad_preference table, and with more than 100K preferences could be a problem. This is the explain plan: https://explain.depesz.com/s/HYv1 [^] | ||||
| Steps To Reproduce | With more than 100K preferences, do login and check the preferences load query. This is the query: select adpreferen0_.AD_Preference_ID as AD_Prefe1_81_, adpreferen0_.AD_Client_ID as AD_Clien2_81_, adpreferen0_.AD_Org_ID as AD_Org_I3_81_, adpreferen0_.IsActive as IsActive4_81_, adpreferen0_.Created as Created5_81_, adpreferen0_.CreatedBy as CreatedB6_81_, adpreferen0_.Updated as Updated7_81_, adpreferen0_.UpdatedBy as UpdatedB8_81_, adpreferen0_.AD_Window_ID as AD_Windo9_81_, adpreferen0_.AD_User_ID as AD_User10_81_, adpreferen0_.Attribute as Attribu11_81_, adpreferen0_.Value as Value12_81_, adpreferen0_.Property as Propert13_81_, adpreferen0_.IsPropertyList as IsPrope14_81_, adpreferen0_.VisibleAt_Client_ID as Visible15_81_, adpreferen0_.VisibleAt_Org_ID as Visible16_81_, adpreferen0_.VisibleAt_Role_ID as Visible17_81_, adpreferen0_.Selected as Selecte18_81_, adpreferen0_.AD_Module_ID as AD_Modu19_81_, adpreferen0_.Inherited_From as Inherit20_81_ from AD_Preference adpreferen0_ where (adpreferen0_.VisibleAt_Client_ID='XXX' or coalesce(adpreferen0_.VisibleAt_Client_ID, 'XX')='0') and (adpreferen0_.VisibleAt_Role_ID='XX' or adpreferen0_.VisibleAt_Role_ID is null) and (coalesce(adpreferen0_.VisibleAt_Org_ID, '0') in ('XX' , 'XX' ) and (adpreferen0_.AD_User_ID='XX' or adpreferen0_.AD_User_ID is null) and (adpreferen0_.AD_Org_ID in ('XX' , '0' , )) and (adpreferen0_.AD_Client_ID in ('XX' , '0')) and adpreferen0_.IsActive='Y' order by adpreferen0_.AD_Preference_ID | ||||
| Proposed Solution | In the analyzed customer, the way to organize preferences is with VisibleAt_Role_ID column, so the best way to improve the query is creating an index in that column: CREATE INDEX <index-name> ON AD_PREFERENCE (VisibleAt_Role_ID) This is the explain plan after the index: https://explain.depesz.com/s/SKyt [^] Note: If can be other clients with other ways to split preferences, also could be interesting to create indexes on columns VisibleAt_Org_ID and VisibleAt_Client_ID. | ||||
| Additional Information | |||||
| Tags | Performance | ||||
| Relationships | |||||
| Attached Files | |||||
| Issue History | |||||
| Date Modified | Username | Field | Change | ||
| 2020-01-31 11:53 | ALopetegui | New Issue | |||
| 2020-01-31 11:53 | ALopetegui | Assigned To | => Retail | ||
| 2020-01-31 11:53 | ALopetegui | Triggers an Emergency Pack | => No | ||
| 2020-02-06 10:36 | ALopetegui | Tag Attached: Performance | |||
| There are no notes attached to this issue. |