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.

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

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.