"Non-selective query against large object type" only occurs in trigger context despite filtering on lookups

Question

I have a very simple after delete trigger that searches for orphan records and deletes them:

List<my_ns__Orphanable_Object__c> orphans = Database.query(
    ' SELECT ' +
        ' Id ' +
    ' FROM ' +
        ' my_ns__Orphanable_Object__c ' +
    ' WHERE ' +
        ' my_ns__Lookup1__c = null ' +
        ' AND my_ns__Lookup2__c = null ' +
        ' AND my_ns__Lookup3__c = null ');

One of our customers has a large amount of data in the "Orphanable Object" and recieves this error when deleting a record:

System.QueryException: Non-selective query against large object type (more than 100000 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)

What I can not understand is; why does this only happen in the trigger context?
To debug the issue I ran the same code in the devloper console and it ran without issue and the orphans list contained ~250 records. This also removed the possibitlity that the query was returning too many results.

Answer 1

That error appears here and a few similar threads I found that triggers are more sensitive to this restriction, although I can't find official documentation on it.

Your my_ns__Orphanable_Object__c table does contain more than 100k rows, right? If so, the returned row count is irrelevant; SFDC is complaining that your query requires a tablescan, and doesn't allow that in this scenario.

To stop the exception, you need to adjust your criteria to be "selective". Specifically, you need to filter on an indexed column. Based on the column names, it looks like you are filtering on lookups, which is good because they are indexed. (The common fix for normal string or number columns is to mark them as External ID, which indexes them.)

But you are filtering on the NULL value, which Salesforce does not consider selective. That's why I think you are seeing the error.

I'm sure there are more elegant workarounds, but the immediate one that comes to mind is:

• create a new hidden numeric field, something like Deletable__c. Mark it External ID.
• create a field update workflow that populates that field with a 1 when any of the lookups are null, otherwise a 0.
• use that field in your trigger query.

Totally untested and as I said, there is probably a more elegant workaround, but something like that should work. You could also consider filtering on LastModifiedDate if feasible since date fields are indexed as well.

Answer 2

Two things. First, you should check out the Query Plan tool, which might help you here. Second, I would recommend using a formula checkbox to determine records where all three lookups are null.

Is_Orphan__c
    AND(
        ISNULL(my_ns__Lookup1__c),
        ISNULL(my_ns__Lookup2__c),
        ISNULL(my_ns__Lookup3__c)
    )

Then your query becomes:

SELECT Id FROM my_ns__Orphanable_Object__c WHERE Is_Orphan__c = true

No checks against null so no table scan.