[SalesForce] QueryException: Non-selective query against large object type for custom object with no records

I have a custom object in a managed package that currently has no records in the customers org. Lets call it DFB__ProductUdfMap__c (namespaces etc… have been changed to protect the innocent). The Storage Usage Data Management page doesn't show the custom object – presumably because it has no records.

This custom object is queried as part of a trigger on updating Opportunity records.

For this particular customer the following exception is being encountered with upserts to OpportunityLineItem records, which are subsequently firing the Opportunity triggers.

Upsert failed. First exception on row 0 with id 00k600000000001AAA; first error:
CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, DFB.ProductOpportunityTrigger: execution of AfterUpdate caused by:
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) (DFB) : []

Capturing the Debug log when this exception occurs shows the last log entry as:

16:11:35.859 (859740543)|SOQL_EXECUTE_BEGIN|[500]|Aggregations:0|SELECT Id, Name, DFB__DynamicPropertyID__c, DFB__DynamicPropertyType__c, DFB__ProductUdf__c FROM ProductUdfMap__c

The actual query in the managed package on line 500 does include a where clause.

Select Id, Name, DynamicPropertyID__c, DynamicPropertyType__c, ProductUdf__c 
from ProductUdfMap__c
where ObjectType__c = 'Order'

The fields DynamicPropertyID__c, ObjectType__c, and AB2__DynamicPropertyType__c are all formula fields via the indexed lookup ProductUdf__c. While this particular org has no records for the custom object it isn't expected to have more than few dozen records in the extreme case.

Executing the full query in the developer console returns no rows.

The developer console gives the Query Plan as:

Cardinality: 7869700
Fields:
Leading Operation Type: TableScan
Cost: 5.0277874…
sObject Cardinality: 7869700
sObject Type: DFB__ProductUdfMap__c

Query Plan Cardinality

Reducing the SOQL query to just the ID field gives the same results.

Why is the cardinality in the millions when there aren't any records?

I assume this is the problem, although it seems extremely odd given there are no records.

Is it something to do with the formula fields?

Expanding out the formula in the where clause to use the actual lookup gives an additional Query Plan with a much better Cardinality:

select Id from DFB__ProductUdfMap__c 
where DFB__ProductUdf__r.DFB__ObjectType__c = 'Order'

enter image description here

The Cost is still greater than 1, so I'm not in the selective range yet.

Comment from @ca_peterson:

some of the internal stats about table size update nightly – maybe that job failed? Support can force an update

I created one mock custom record and left it overnight. Now when I get the query plan for the exact same query:

select Id from DFB__ProductUdfMap__c 
where DFB__ProductUdf__r.DFB__ObjectType__c = 'Order'

enter image description here

The original SOQL using the formula field in the where clause also produces the same Cost of 0.65 with an sObject Cardinality of 1.

Best Answer

I don't believe there's any published documentation on this but I do know that, at the time of writing at least, the table statistics used for query selectivity checking (and query plans) are updated on a nightly basis.

Based on that the easiest option you have is to wait up to 24hrs for this statistics update to run. I know independently to this that custom objects can re-use the key prefix, and seemingly actual physical location, or previously deleted custom objects.

My hypothesis is that your custom object is re-using an old object's physical location and the old statistics are somehow still associated with it. If I'm right then the nightly run should clear this up and your SObject Cardinality should be down to the expected 0, with the correspondingly low cost.

Related Solutions

[SalesForce] “Non-selective query against large object type” only occurs in trigger context despite filtering on lookups

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.

[SalesForce] QueryException: Non-selective query – Production But Not Sandbox

This error message tells you everything you need to know! Take a closer look at these component parts:

Trigger.Lead_LTS_Update: line 11, column 1

That line number should correspond to this query:

SELECT ... FROM Lead

So actually it's the Lead object that has too many records and requires a selective query.

Non-selective query against large object type (more than 200000 rows)

Well that explains why your sandbox environment wouldn't throw this error while your production environment would. Unless you're working with a full sandbox, it seems unlikely to have 200k records there.

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:

Check if your Seller_Id__c and Live_to_site__c fields are indexed, as indexed fields are more selective to filter on. If they are not, you may need to open a case to get them indexed.

The filter value includes null (for instance binding with a list that contains null)

To my eye this point seems the most likely cause for this error. If you have an Account record where Account_Id__c = null, then your actSellerIds collection will contain null. For this reason, I might prefer to use Set<String> to make removal simpler:

Set<String> sellerIds = new Set<String>();
for (Account record : trigger.new)
    if (record.Items_w_Inv_in_Portal__c > 25)
        sellerIds.add(record.Account_Id__c);
sellerIds.remove(null);

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)

This point could cause the error you observe on update, but not insert. If you had a notable percentage of your Lead records with one common Seller_Id__c, then you would fall outside the Selectivity Thresholds outlined in this Query And Search Optimization Cheat Sheet.

Best Answer

Related Solutions

[SalesForce] “Non-selective query against large object type” only occurs in trigger context despite filtering on lookups

[SalesForce] QueryException: Non-selective query – Production But Not Sandbox

Related Topic