Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0043081Retail ModulesWeb POSpublic2020-01-31 11:532020-02-06 10:36
ReporterALopetegui 
Assigned ToRetail 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerGold
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0043081: In the Login, preference load has bad performance
DescriptionIn 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 ReproduceWith 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 SolutionIn 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
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-01-31 11:53ALopeteguiNew Issue
2020-01-31 11:53ALopeteguiAssigned To => Retail
2020-01-31 11:53ALopeteguiOBNetwork customer => Gold
2020-01-31 11:53ALopeteguiTriggers an Emergency Pack => No
2020-02-06 10:36ALopeteguiTag Attached: Performance

There are no notes attached to this issue.