System.QueryException: Non-selective query against large object type

A trigger on the Case object runs on every Case update. The trigger looks up related Accounts for SLA enforcement. On Monday morning a support manager bulk-updates 500 stale Cases to a new owner. The bulk update fails with System.QueryException: Non-selective query against large object type (more than 200000 rows). The trigger has been in production for two years without trouble. Other bulk updates have worked. Today's update broke.

What the platform is checking

Salesforce protects large objects (more than 200,000 rows) by requiring that triggers and synchronous queries against them use selective filters. A selective query is one where the WHERE clause includes a filter on an indexed field that returns a small enough fraction of the total rows. Salesforce's query optimizer evaluates the selectivity at execution time and, if the query is non-selective against a large object, raises a QueryException: Non-selective query against large object type.

The protection exists because non-selective queries on large objects can scan millions of rows. A single such query in a synchronous transaction can exceed the CPU and time limits, blow the heap, or impact the underlying database for other tenants. Forcing developers to write selective queries keeps the platform responsive.

The threshold is precise. Salesforce documentation calls out 200,000 rows as the boundary where this protection kicks in for triggers. Below that, even non-selective queries usually run fine; above that, the optimizer requires explicit selectivity through an indexed filter that returns 10% or fewer of the total rows (with some variation by index type).

The error fires only when the query runs against a "large" object. An Account with 50,000 rows does not trigger this; the same query with 250,000 rows does. Many teams encounter the error months or years after deploying the trigger, when their data volume crosses the threshold.

The broken example

A trigger on Case that looks up related Accounts to enforce an SLA rule:

trigger CaseTrigger on Case (before update) {
    Set<Id> accountIds = new Set<Id>();
    for (Case c : Trigger.new) {
        if (c.AccountId != null) {
            accountIds.add(c.AccountId);
        }
    }

    List<Account> related = [
        SELECT Id, SLA__c, Industry
        FROM Account
        WHERE Id IN :accountIds
        OR ParentId IN :accountIds
    ];

    for (Case c : Trigger.new) {
        // ... use related accounts to enforce SLA ...
    }
}

The query has two conditions joined by OR. The Id IN :accountIds clause is selective: a filter on the indexed Id field with a small set of values. The ParentId IN :accountIds clause looks like it should also be selective because ParentId is indexed.

The optimizer combines the two clauses with OR. An OR can sometimes be optimized into a union of two selective branches, but in this case the second branch (ParentId IN) does not have a leading equality on an indexed field; it ends up requiring a scan. When the Account object has 250,000 rows, the optimizer raises the non-selective query exception.

A second shape: a trigger on Contact that finds all Contacts at the same Account email domain:

trigger ContactTrigger on Contact (before insert) {
    Set<String> domains = new Set<String>();
    for (Contact c : Trigger.new) {
        if (c.Email != null) {
            domains.add(c.Email.substringAfter('@'));
        }
    }
    List<Contact> matching = [
        SELECT Id, AccountId
        FROM Contact
        WHERE Email LIKE :buildPattern(domains)
    ];
}

The Email LIKE filter is not selective enough on a Contact object with 500,000 rows. The query runs fine in development with 5,000 Contacts; it fails in production.

A third shape: a query that uses a formula field in the filter. Formula fields are not indexed, so any filter on a formula is non-selective by default. A trigger that filters on a formula against a large object will always fail.

The fix, three paths

Add an indexed filter that prunes the row set. The most reliable fix is to add a leading filter on an indexed field that limits the result set to a small fraction. Standard indexed fields include Id, Name, OwnerId, CreatedDate, LastModifiedDate, RecordTypeId, lookup relationships, and any field explicitly marked as External Id or Unique.

trigger CaseTrigger on Case (before update) {
    Set<Id> accountIds = new Set<Id>();
    for (Case c : Trigger.new) {
        if (c.AccountId != null) accountIds.add(c.AccountId);
    }

    List<Account> direct = [
        SELECT Id, SLA__c, Industry FROM Account WHERE Id IN :accountIds
    ];
    List<Account> children = [
        SELECT Id, SLA__c, Industry, ParentId FROM Account WHERE ParentId IN :accountIds
    ];

    List<Account> related = new List<Account>();
    related.addAll(direct);
    related.addAll(children);
}

Splitting the OR into two separate queries lets each query be evaluated independently for selectivity. The first query has a selective Id IN filter. The second query has a selective ParentId IN filter (assuming ParentId is indexed, which it is by default on lookup relationships).

Two queries instead of one means two SOQL statements (well under the 100-statement limit per transaction), but each is selective and the trigger no longer fails.

Request a custom index on the field you filter by. When the natural filter field is not indexed, Salesforce Support can create a custom index. Custom indexes are evaluated by the optimizer the same way standard indexes are. Submitting a request through a support case with the field name and a justification (data volume, query pattern, business need) usually gets the index created within a few days.

A custom index on a text field requires the values to be reasonably distinct. Salesforce will reject the request if the field has a small value set (which would not provide selectivity). For high-cardinality fields like an external system's record id, the index request is usually approved.

Move the work to async. When the trigger cannot be made selective inline (because the data shape genuinely requires a wide query), the right answer is to defer the work to an asynchronous job. A Queueable or Batch Apex job operates with different limits and is not subject to the same trigger-level non-selectivity check in all cases:

trigger CaseTrigger on Case (before update) {
    System.enqueueJob(new SlaEnforcementQueueable(Trigger.newMap.keySet()));
}

public class SlaEnforcementQueueable implements Queueable {
    private Set<Id> caseIds;
    public SlaEnforcementQueueable(Set<Id> ids) { this.caseIds = ids; }

    public void execute(QueueableContext qc) {
        // run the wider query in async context; update Cases after
    }
}

The async pattern adds latency (the change is visible in real-time but the SLA flag updates a moment later), which is usually acceptable.

The fixed example

A trigger that splits the related-account lookup into selective queries:

trigger CaseTrigger on Case (before update) {
    Set<Id> accountIds = new Set<Id>();
    for (Case c : Trigger.new) {
        if (c.AccountId != null) accountIds.add(c.AccountId);
    }
    if (accountIds.isEmpty()) return;

    Map<Id, Account> directs = new Map<Id, Account>([
        SELECT Id, SLA__c, Industry FROM Account WHERE Id IN :accountIds
    ]);

    Map<Id, Account> childrenByParent = new Map<Id, Account>();
    for (Account child : [
        SELECT Id, SLA__c, Industry, ParentId FROM Account WHERE ParentId IN :accountIds
    ]) {
        childrenByParent.put(child.ParentId, child);
    }

    for (Case c : Trigger.new) {
        if (c.AccountId == null) continue;
        Account direct = directs.get(c.AccountId);
        Account child = childrenByParent.get(c.AccountId);
        // ... enforce SLA using direct and/or child ...
    }
}

Each query is selective. The trigger handles bulk updates without failing.

Edge case: indexed field selectivity is dynamic

A field's index helps selectivity only when the filter returns a small fraction of the rows. Filtering by OwnerId = :userId is selective for a salesperson who owns 500 records but non-selective for a system user who owns 500,000 records.

The Salesforce optimizer evaluates selectivity at runtime based on the values bound into the query. The same query can be selective for one user and non-selective for another. Defensive code handles both cases.

Edge case: the 200,000-row threshold is approximate

The documentation calls out 200,000 rows as the boundary, but Salesforce reserves the right to apply the protection at lower volumes when other factors (storage tier, concurrent load) make it appropriate. Treating 200,000 as a hard line is risky; aim to write selective queries from the start.

Edge case: tooling and reports

Reports built in the standard report builder do not face the trigger-level check, but they have their own performance limits. A report that scans hundreds of thousands of rows can time out (see REQUEST_RUNNING_TOO_LONG). The principles are the same: filter on indexed columns, avoid non-selective text filters, use the Reports API's async pattern for large extracts.

Defensive habits

Always include a selective leading filter on triggers that query related objects. Even if the data is small today, the trigger will run for the lifetime of the org. Building selectivity in from day one means the trigger keeps working as the data grows.

Avoid OR filters across different selectivity dimensions. Split into separate queries when possible.

Identify which fields are indexed on each object. The Salesforce documentation lists standard indexes per object. Custom fields can be indexed on request. Document the indexed fields for objects your triggers query.

Use the Query Plan tool. The Developer Console exposes the optimizer's selectivity analysis for any query. A query labeled "Index" in the plan output is good; a query labeled "TableScan" is a future failure.

Test with realistic data volumes. A trigger that works with 1,000 records in a sandbox does not prove it works with 1,000,000 in production. Loading representative data into a partial-copy sandbox surfaces selectivity issues before they hit production.

Test patterns

A test that exercises the trigger with a bulk update:

@IsTest
static void caseTriggerHandlesBulkUpdate() {
    Account parent = new Account(Name='Parent');
    insert parent;
    List<Account> children = new List<Account>();
    for (Integer i = 0; i < 5; i++) {
        children.add(new Account(Name='Child ' + i, ParentId=parent.Id));
    }
    insert children;

    List<Case> cases = new List<Case>();
    for (Integer i = 0; i < 200; i++) {
        cases.add(new Case(Subject='Bulk ' + i, AccountId=parent.Id, Status='New', Origin='Web'));
    }
    insert cases;

    Test.startTest();
    for (Case c : cases) c.Status = 'Working';
    update cases;
    Test.stopTest();

    System.assertEquals(200, [SELECT count() FROM Case WHERE Status = 'Working']);
}

The test confirms a 200-record bulk update completes. It does not prove selectivity at the 200,000-row threshold (which a sandbox usually does not have), but it exercises the bulk path. For full-scale validation, run an integration test in a full-copy sandbox that mirrors production volume.

Diagnosing in production

When the error fires:

1. Identify the trigger and the failing query (the stack trace points to the line).
2. Check the object's row count via Setup or via SELECT COUNT() FROM <Object>.
3. Inspect the query's WHERE clause. Is the leading filter on an indexed field? Does it return a small fraction of the rows?
4. Run the Query Plan tool to confirm the optimizer's strategy.
5. Apply the appropriate fix: add a selective filter, request a custom index, or move to async.
6. Deploy and confirm the bulk operation succeeds.

The diagnosis is fast when the developer knows the selectivity rules. The fix is targeted.

Anti-pattern: relying on LIMIT to make a query selective

A query like SELECT Id FROM Account LIMIT 100 looks selective because it returns only 100 rows. The optimizer evaluates selectivity based on the WHERE clause, not the LIMIT. A non-selective WHERE with a LIMIT is still non-selective; the optimizer still scans the rows before applying the LIMIT.

Anti-pattern: catching the exception and ignoring it

Wrapping the query in a try-catch and skipping the related-account logic when the exception fires looks like a workaround. The real cost is that the SLA logic stops running silently. Records that should have been flagged are not. The right fix is to make the query selective.

Quick recovery checklist

1. Identify the failing query and its leading filter.
2. Confirm the object exceeded the 200,000-row threshold.
3. Add a selective filter or split into multiple selective queries.
4. Test with a bulk operation.
5. Deploy and confirm.

The error is a hard signal that the trigger's design needs to scale with the data. Addressing it once and using the lessons in future triggers builds a durable codebase.

Further reading from Salesforce

• Apex Developer Guide: Best Practices for Triggers
• SOQL Reference: Working with Very Large SOQL Queries
• Salesforce Help: Custom Indexes and Selective Queries
• Architect: Large Data Volumes
• Trailhead: Large Data Volumes Module