Query on FieldPermissions object is super slow

Question

We have created VF page that shows the access to fields for all profiles and permission. This page runs supper slow. When we analyse the the code with developer console we realized that this issue is due to following query.

SELECT Id, ParentId, Parent.Name, Parent.Profile.Name, Parent.ProfileId, PermissionsRead, PermissionsEdit FROM FieldPermissions WHERE Field ='SCMC__Address__c.SCMC__Create_New__c' AND (PermissionsEdit = true OR PermissionsRead = true)

In some cases this query takes 25+ seconds to return result. which result into very slow performance of page.

When we further analyse the query with explain call, we found that it not using any index and doing full table scan every time.

Now, I am queries that how salesforce use same table on all other places(for ex standard account detail page) and quickly decide whether user has access to particulate field or not. All other standers pages which use this table in background to decide field accessibility runs super fast but when i query same table its super slow.

Is there something that i am missing here or salesforce have some other way to determine field accessibility.

Answer 1

It turns none of these (SobjectType, Field & PermissionsRead ) are indexed so when i query then it do full table scan.

Now the main part of question how its so fast on standard pages ...well this table has index on Parent.ProfileId. When SFDC rendered standard pages it already have users profile id and permission sets so it can quickly run SOQL using index. but in my case i need all profiles where they have access to particulate field and so i cant use index. That it why my query is super slow.