How can adding a Subselect make a SOQL query non-selective?

I have a SOQL Query with subquery that fails to execute because it is non-selective. I don’t understand why and cannot interpret the results. All I know is that tearing parent und sub query apart solves this.

I have CustomObjectA__c and CustomObjectB__c which can be N-M linked via Linker__c. In my query I am fetching all childs of some CustomObjectA___c records:


for(CustomChildObjectA__c a : [SELECT TextField__c 
                              FROM CustomChildObjectA__c 
                              WHERE mdr_Parent__c IN (SELECT mdr_CustomObjectA__c 
                                                      FROM LinkerObject__c 
                                                      WHERE mdr_CustomObjectB__c = 'a0CG000000UW2LT')]) {

Rewriting the code with two seperate queries instead of one with subquery solves this:


List<LinkerObject__c> linker = [SELECT mdr_CustomObjectA__c 
                                FROM LinkerObject__c 
                                WHERE mdr_CustomObjectB__c = 'a0CG000000UW2LT'];
if(!linker.isEmpty()) {                             
   for(CustomChildObjectA__c a : [SELECT TextField__c 
                                  FROM CustomChildObjectA__c 
                                  WHERE mdr_Parent__c IN :linker]) {

In the system there are only a handful of LinkerObjects__c so the inner query is not only using an autoindex MDR filter but also return very few results.

Few data, only auto-index-filters, and extra selective subquery. How can this be non-selective??

This is the result of the query plan:
**enter image description here**


From the documentation on Large Data Volumes best practices: (Page 10)

In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause can be indexed. In this situation, decompose the single query into multiple queries, each of which should have one WHERE filter, and then combine the results.


Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

So, while you’ve got two selective queries – the combination of the two isn’t selective and the optimizer isn’t able to use indexes. Most likely because of the usage of the foreign key field (Lookup: mdr_Parent__c) in the where clause because the results of the query in the where clause could (theoretically) include nulls and a table scan will be performed to find the matching rows as no index can be used in that situation.

Queries which reference a lookup field will result in a table scan even if your where clause specifies qualifiers like WHERE mylookupfield__c != null.

Small Backstory

I’ve had to troubleshoot non-selective queries recently where what I thought was written was a good/best practice, specifically checking for != null or = null on lookup field values (as well as other types) in an effort to make the query more efficient. I was under the assumption that this would improve performance by excluding or including rows which had null values in the lookup fields by making use of an index. Unfortunately, this is not the case. These queries perform a table scan and it’s extremely inefficient, especially noticeable with large volumes.

The most impressive optimization I’ve seen recently was a query (in a batch) which was taking 13 minutes to return ~400k records. The platform would shut down the batch when it reached the max processing time and the solution eventually needed to scale to a data volume of 2M+ records. Optimizations were in order.

After reading that LDV best practices document and then changing the structure of the query being performed the difference was quite staggering. The rewritten query returned 1.6M records in under 3 minutes and now scales incredibly well.

Beware of table scans because of referencing foreign keys in where clauses.

Source : Link , Question Author : Robert Sösemann , Answer Author : Mark Pond

Leave a Comment