2

tl;dr: I believe that my particular query is selective, but Salesforce occasionally insists it isn't. Evidence follows. What am I missing?

====

Every now and again (7-8 occurrences since 2017-1-1), one particular query in one of my triggers is causing me issues, giving me the following error

Asset: execution of AfterUpdate

caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when:

  1. The filter value includes null (for instance binding with a list that contains null)
  2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

Trigger.Asset: line 20, column 1

The relevant section of trigger code is this

trigger Asset on Asset (after insert, after update, after delete) {

    List<Id> acctIds = new List<Id>();
    List<Asset> assetList = new List<Asset>();
    if (Trigger.isDelete) {
        assetList = Trigger.old;
    } else {
        assetList = Trigger.new;
    }
    for (Asset ass : assetList) {
        // Create list of accounts to update
        if (ass.AccountId <> null) acctIds.add(ass.AccountId);
    }

    if (acctIds.size() > 0) {
        List<Asset> allAssets = new List<Asset>([select Id,AccountId,Total_Amount__c,Product2Id,Product_Family__c,Allocated_NetEx_MRC_Total__c,NetEx_Rollup__c from Asset where AccountId in :acctIds and Status = 'Billing']);

        // The rest of the trigger below this point is omitted
        // The query on the line above is line 20 (or it would be, if I had included
        //   the 4 lines of comments at the very top of the trigger).
        // Don't let the 'allAssets' variable name fool you, it only queries 
        //   the Assets that have Status = 'Billing' and are related to an Account
        //   that at least one of the Asset records in Trigger.new is related to.
    }
}

Breaking out the problematic query for readability

SELECT Id, AccountId, Total_Amount__c, Product2Id, Product_Family__c,
    Allocated_NetEx_MRC_Total__c, NetEx_Rollup__c 
FROM Asset 
WHERE AccountId IN :acctIds AND Status = 'Billing'

I can't for the life of me figure out why this is giving me a non-selective query error.

In the org where this is an issue, there are a total of 471,939 Asset records.

I've attempted to recreate the worst-case scenario that I believe this query should experience (given that it is part of a trigger). Using the following query, I was able to gather the list of the 200 Account records with the highest number of Assets being billed:

SELECT AccountId, COUNT(Id) FROM Asset WHERE Status = 'Billing' GROUP BY AccountId ORDER BY COUNT(Id) DESC LIMIT 200

I then plugged those 200 Account Ids into the problematic query. This simulates the case where each of the Asset records in the trigger context variables is for a separate Account, and each Account happens to be one of the 200 with the most Asset records being billed.

The query plan tool (through the developer console) gives me the following

Result of query plan

From Query Plan Tool (How to & FAQ)

Cost:

The cost of the query compared to the Force.com Query Optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective.

In running my 'worst-case scenario' query via anonymous apex, the query runs just fine, and returns a total of 6037 records.

If I go further and query with the top 800 Accounts, I only end up with 11,311 records (by which time I'm firmly in the long-tail section of the distribution of Assets, each additional record having 7 or fewer Assets being billed).

I'm fairly sure that this particular query should check all the required boxes that would make it selective:

  • Query cost is shown as 0.353152... in the worst case I can imagine happening
  • Leading operation type is 'Index'
  • WHERE clause uses a Standard index filter
  • Number of records in the resultset is 6037 in my worst-case scenario
  • The selectivity threshold for the first million records using a standard index is 30% (based on Query Search Optimization cheat sheet), 30% of 471,939 is 141,571
  • No negative filters in the WHERE clause
  • No nulls in the list of Account Ids

Am I missing something in working out the selectivity of my query? Any ideas on why Salesforce occasionally says it isn't selective?

4
  • Do you perhaps have some sort of record skew? Meaning Account records which parent a large number of Asset records? Ah never mind, I see you did select those records with the most children.
    – Adrian Larson
    Mar 30, 2017 at 20:07
  • @AdrianLarson depends on your definition of 'large'. The top 5 Accounts for number of Asset records being billed contain 961, 295, 192, 154, and 150. I haven't yet worked out how many total Assets those top 5 Accounts have.
    – Derek F
    Mar 30, 2017 at 20:10
  • Certainly doesn't account for the error with those figures. Have you opened a case with support? Maybe you would get that error if you ran this query as someone who lacks read permission on the AccountId field...
    – Adrian Larson
    Mar 30, 2017 at 20:11
  • 1
    @AdrianLarson I have not, but removing the Status = 'Billing' from my 'top Accounts' query reveals there is a single Account with 89,802 Assets associated to it. That is most definitely a large number. I think skew is definitely the answer here. Now I just need to figure out how the hell a single Account has that many Assets...
    – Derek F
    Mar 30, 2017 at 20:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.