QueryException: Non-selective query (it is selective)

I am trying to query a Custom Object called Lead_Assignment__c based on some criteria. Original query below:

List<Lead_Assignment__c> leadAssignments = [
    SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
    WHERE (Segment__c = :MID_MARKET_SEGMENT AND Zip_Code__c IN :midMarketZipCodes)
    OR (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)
];

I populate midMarketZipCodes and enterpriseZipCodes based on Leads passed in from my Lead trigger. When I try to save one lead (Enterprise), I get the following exception:

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)

So I broke out the query to debug easier:

String soqlQuery= 'SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c' +
    ' WHERE (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)';
system.debug(soqlQuery);
system.debug(ENTERPRISE_SEGMENT);
system.debug(enterpriseZipCodes);
List<Lead_Assignment__c> leadAssignments = baseQueryClause + enterpriseClause;
/*
* Results in:
* SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
* WHERE (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)
* Enterprise
* 93502
*/

I copy-pasted this Query into the Query Editor and substituted in ‘Enterprise’ and ‘93502’. It returned one result, exactly what I was expecting. I substituted those values into soqlQuery so the query was identical to what I entered in the Query Editor, and I still get the error.

Final Query:

'SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
WHERE (Segment__c = 'Enterprise' AND Zip_Code__c = '93502')'

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

So…this is a strange result but it works. It seems that when an sObject has more than 100,000 records, queries on this object need a WHERE clause that references an indexed field, even if that reference does not affect the results. The docs state:

A query is selective when one of the query filters is on an indexed
field and the query filter reduces the resulting number of rows below
a system-defined threshold. The performance of the SOQL query improves
when two or more filters used in the WHERE clause meet the mentioned
conditions.

However, the claim that the filter must reduce the resulting number of rows does not seem to be accurate. Before and after I add that clause, I get one result.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0