Loading

Make Salesforce Platform SOQL Query Selective

Publiseringsdato: Apr 2, 2026
Beskrivelse

A non-selective query may cause different programmatic elements (like an Apex trigger or batch Apex class) to fail. When querying large objects, special design considerations must be taken into consideration.

SOQL (Salesforce Object Query Language) is the query language used to search and retrieve data from Salesforce objects. The performance of a SOQL query depends on the presence of a selective filter. If a SOQL query contains at least one selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be non-selective and will require a full table scan.

Real-Life Example Scenario

When querying Accounts with custom filter criteria, such as finding all accounts created this year with a specific industry type, you need to ensure your query uses indexed fields efficiently. For example, if you're querying a custom object like Appointment__c with 240,000 records and filtering by CreatedDate and Status__c, understanding which fields are indexed and how selective they are determines whether your query performs well or times out.

Løsning

The complexity of the SOQL query has a massive influence on whether a field is suitable for indexing. We strongly recommended reviewing the following materials to fully understand how standard and custom indexes are different, and how they're employed.


Keep in mind - While every effort has been made to ensure this article explained how the Salesforce SOQL query optimizer works at a high level, its behavior can change without notice to accommodate additional performance enhancements. Developers are advised to use the Query Plan view (available since Summer '14) in the Developer Console, to be able to tune unselective queries.

The performance of a SOQL will depend on the presence of a selective filter. If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be un-selective and will require a full table scan.

From the perspective of the query optimizer, a filter can be simple or composite. A simple filter would be each of the field expressions (<field> <operator> <value>) in a condition expression that uses the "AND" operator. In contrast, the result of joining 2 or more field expressions via the "OR" operator is a composite filter. 

  • fieldExpression1 AND fieldExpression2 - shows 2 simple filters
  • fieldExpression1 OR fieldExpression2 - is a composite filter
  • fieldExpression1 AND (fieldExpression2 OR fieldExpression3) - shows 2 filters (one simple, and one composite).

Understanding Query Selectivity

  • Determine if it has an index.
    • If the filter is on a standard field, it'll have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate,   SystemModstamp).
    • Custom fields will have an index if they have been marked as Unique or External Id
  • If the filter doesn't have an index, it won't be considered for optimization.
  • If the filter has an index, determine how many records it would return:
    • For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1   million total targeted records, which you could reach only if you had more than 5.6 million total records.
    • For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million.  In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.
  • If the filter exceeds the threshold,it won't be considered for optimization.
  • If the filter doesn't exceed the threshold, this filter IS selective, and the query optimizer will consider it for optimization.

Examples:

1. Get the SOQL query and replace the list of fields with the count() operator:
SELECT count() FROM Appointment__c WHERE CreatedDate = this_year AND Status__c = 'Available'

2. Check the number of active records in the table:
Select count() from Appointment__c
# of records: 239619
Threshold for standard indexes: 30% * 239619 = 71885
Threshold for custom indexes: 10% * 239619 = 23961

3. The only filter in the WHERE clause with an index is CreatedDate, so we will only consider the filter on this field.
Partial SOQL query: Select count() from Appointment__c where CreatedDate = this_year
# of records: 84567 > threshold for standard indexes
Is the used filter selective? NOT

4. Let's check though if the filter on Status__c is selective:
Partial SOQL query: Select count() from Appointment__c where Status__c = 'Available'
# of records: 14397 < threshold for custom indexes

5. If Status__c is custom indexed, "Status__c = 'Available'" would be a selective filter, and hence the SOQL query would be selective.
 

Indexing Strategy

To Custom Index a field - Create a new case and provide the affected SOQL query, and user Id that can be used for testing, as well as the values of any binding variables used. 
Custom indexes, when used improperly, can actually slow query results. It's best to create filter conditions that are selective so Force.com scans only the rows necessary in the objects your queries target. Force.com query optimizer doesn't use an index to drive queries containing  non-selective filter conditions, even if the fields those filter conditions reference already have indexes on them. 
 

Checklist of details to include in Support case for a custom index

Be sure that you include this information in your request, the reason why this information needs to be provided is outlined below. 

  1. Fields to be indexed - Makes sure we know which fields to focus on and which fields to leave alone. 
  2. SOQL query with bind values -  Including the fields as part of filter criteria. We may need a query which has a filter on Severity__c, if you have provide login access we can query some sample values for custom field Severity__c and we can run the query.
    • Select Id, Case_Number__c from Case where Severity__c = 'Critical' - Correct, here Critical is a bind value.
    • Select Id, Case_Number__c  from case where Severity__c = :severityLevel - Incorrect, we can't run this query
  3.  Login access - With login access, we'll be able to replicate and understand the issue correctly.
  4. Error messages/time stamps - Any error logs/time-stamps will help us in understanding the need for index.
  5. Steps to replicate the error - ensures that we follow the same path you took so we can better understand the issue. 
  6. Evaluate possibilities of adding additional filters - Sometimes, adding any additional filters to the query without impacting the functionality can resolve the issue without having to add an index. Where we can't index or where the index turns out to be negative, we recommend adding additional filters to the query to reduce the number of resulting records. If this isn't possible, provide the reason you won't be able to add additional filters so that we can check for additional possibilities.
  7.  If information for steps 4 and 5 are not known, substitute them with justification into why this is needed as indexing a field without proper reasoning can result in a decrease in performance.

What happens if more than one simple filter is selective? The query optimizer will choose the one with lower cost to drive the execution plan of the query.

How do you know if a composite filter is selective? The optimizer will determine if each of the simple filters is selective, and if in aggregate the number of returned records doesn't exceed the thresholds mentioned above. If these conditions are met, the filter will be selective.

Knowledge-artikkelnummer

000385218

 
Laster
Salesforce Help | Article