Making SOQL selective: the non-selective-query error, and the indexes that fix it

A nightly batch job has run clean for a year. One Tuesday it starts failing with System.QueryException: Non-selective query against large object type. Nobody changed the code. What changed is that the object finally crossed a row count, and a query that was always inefficient stopped being allowed to run.

Selectivity is the idea that saves you here, and most teams meet it for the first time through that error. A query is selective when its filter uses a database index and targets few enough rows to stay under a threshold. Everything else is a full table scan, and on a large object the platform would rather throw than let you scan.

My rule is plain: filter on a field that is already indexed, or get an index on the field you filter, and check the query plan before the data grows into a problem rather than after.

This matters more now than it used to. The same non-selective query that merely feels slow in a list view can hard-fail an Agentforce action or a Data 360 read, because those run under governor ceilings with no patient human watching the spinner.

What selective actually means

The optimizer picks an index only when your filter targets fewer rows than a threshold tied to the index type. The numbers are specific, and worth knowing because they explain every “why is this slow” conversation.

A standard index is usable when the filter targets up to 30% of the first million records, plus 15% of anything beyond the first million, capped at one million rows. A custom index is stricter: 10% of the first million, plus 5% beyond that, capped at 333,333 rows. These figures come from Salesforce’s engineering write-up on query performance.

Work an example. On an Account object with 2.5 million rows, a standard index stays selective up to 300,000 + 225,000 = 525,000 matching rows. A custom index on the same object holds up to 175,000. If your filter would return more than that, the optimizer gives up on the index and scans the table.

The practical reading: selectivity is relative to how many rows your WHERE clause matches, not how many the object holds. A filter on a status that 90% of records share is non-selective even with an index on it. Narrow filters win.

The error only triggers throw at you

The exact message is worth reading closely: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. The Apex guide on very large queries documents it.

There is a version wrinkle: the current guide says 200,000 rows, while Trailhead and older docs cite 100,000. Treat the threshold as a couple hundred thousand, not a single magic number, and design well under it.

The fail-fast only happens in one place: a SOQL query inside an Apex trigger, against an object over that row count. Salesforce terminates it rather than run a long scan during a save. Outside a trigger, in a batch, a controller, or anonymous Apex, the same non-selective query does not throw. It just runs slowly, or eventually times out.

So the error is a trigger-specific symptom of a query that was already a problem everywhere else, only without the exception. One more trap: an indexed field can still be non-selective. If the filter value is null, or data skew makes the matching row count blow past the threshold, the optimizer skips the index even though it exists.

The fields that are already indexed

Before you ask Salesforce for a custom index, check whether you can filter on a field that is already indexed. Standard indexes exist by default on the primary key Id, on Name, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, and Division where enabled. Foreign keys are indexed, which covers lookup and master-detail fields, and the audit fields are indexed too.

Salesforce’s field-index guide has the fuller picture, and the exact set varies by object, so don’t assume a field is indexed because a similar one is.

Two custom fields index themselves. Mark a field as an External Id and Salesforce creates an index on it. Mark a field Unique and you get a unique index. A field can be both. This is the cheapest way to get an index on a field you control, and it’s why integration keys should almost always be External Ids rather than plain text.

For anything else, a custom index is a Salesforce Support request. You cannot create a general one yourself, and some field types can’t be indexed at all: multi-select picklists, long text areas, rich text areas, non-deterministic formula fields, and encrypted text. If you’re filtering on one of those at volume, the fix is a data-model change, not an index.

The filters that throw your index away

A query can name an indexed field and still scan, because the operator defeats the index. These are the patterns to catch in review:

• A leading wildcard in LIKE, as in Name LIKE '%acme%'. An index is an ordered structure, so it can seek a prefix but not an interior match. Use SOSL for real full-text search instead.
• Negative operators: not-equals (<>), NOT, NOT IN, and EXCLUDES. An index finds what matches, not what doesn’t. Rewrite to the positive set with = or IN when you can.
• Comparison operators (>, <, >=, <=) on text fields. Ranges work on true numeric and date indexes; on text they go non-selective.
• Filtering for null. Null rows are excluded from a custom index unless it was built with allowNullValues set true, which defaults to false.
• Non-deterministic formula fields. Only deterministic formulas can be indexed; any formula that spans to another object or uses TODAY, NOW, owner, or multi-currency values cannot, as Salesforce’s formula-indexing note spells out.

The habit that prevents most of these: filter on a narrow, positive, indexed field first, then do the rest of the narrowing in Apex on the smaller result set.

Read the query plan before you ship

You don’t have to guess whether a query is selective. The Developer Console has a Query Plan tool, off by default. Turn it on under the console’s preferences, and a Query Plan button appears in the Query Editor. Trailhead’s efficient-queries module walks through it.

Read three things in the plan. relativeCost is the headline: above 1.0 means the optimizer judged the query non-selective, and lower is better. The Leading Operation Type tells you what the database would do: Index is the win, TableScan is the scan you’re avoiding, and Sharing means sharing rules are driving the access path. Cardinality is the estimated row count the leading operation returns.

Run the plan against production-like volume, not an empty sandbox. A query that shows Index on 5,000 rows can flip to TableScan at five million, and the whole point is to see that before your users do.

When the index isn’t enough

Sometimes the data is genuinely large and no filter gets you under the threshold. The platform has large-data-volume patterns for that.

Skinny tables are a physical copy of your most-used fields that Salesforce maintains alongside the object, built to avoid joins and speed reports, list views, and queries. They hold up to 100 fields, can’t include fields from other objects, and are created and kept in sync by Salesforce Support. Because they can’t span objects, they don’t help a query that joins across relationships.

For processing volume rather than filtering it, Database.getQueryLocator in Batch Apex returns a server-side cursor that can scope up to 50 million rows, far past the 50,000-row ceiling of a plain SOQL list. For extracting data, Bulk API PK chunking (the Sforce-Enable-PKChunking header, default 100,000 rows per chunk, maximum 250,000) splits a huge table into Id ranges and is the standard move above roughly ten million records.

Apex Cursors and Big Objects cover the on-platform and archival ends of the same problem. Divisions can partition an org so queries and list views see a segment rather than the whole object.

Sharing and skew: the cost you didn’t write

Two performance costs come from the data model, not the query text. The first is sharing. When an object’s org-wide default is private, the optimizer adds a sharing join against the share tables and role hierarchy, so the query has to work out what the running user may see. The same query can be selective for an admin with View All Data, who skips the check, and non-selective for a restricted user, who doesn’t.

The second is data skew, which Salesforce defines as more than about 10,000 child records tied to a single parent. Account data skew causes record-lock contention, because each child update briefly locks the shared parent and concurrent updates collide.

Ownership skew, one user owning a vast number of records, makes sharing recalculation crawl when that user moves in the hierarchy. Lookup skew, many records pointing at one lookup value, causes the same locking on inserts. Keeping parents under roughly 10,000 children, and placing high-volume owners outside the role hierarchy, prevents a class of timeouts no index would have fixed.

Why this is now an agent problem

A slow query used to be a user-experience issue: someone waits, sighs, and the list view eventually loads. An Agentforce action built on Apex has no such patience. It runs inside a synchronous transaction with up to 100 SOQL queries, and the Atlas reasoning engine can call an action several times as it reasons.

So a query that is merely slow for a human compounds into exhausted limits for an agent. A non-selective query inside an action can throw mid-loop, and what the business sees is not “the query was slow.” It’s “the AI failed.”

Data 360 reads run under the same discipline. Querying unified data from Apex counts against the transaction’s budget, so the selectivity you’d want on any high-volume CRM query matters just as much when the thing issuing it is autonomous.

Where to start

Pick the one object that shows up in your slow queries and your limit errors, and look at the three or four queries that hit it most. For each, name the field you filter on and ask whether it’s indexed, whether the operator keeps the index, and whether the filter is narrow enough.

Turn on the Query Plan tool and confirm, against real volume, that the leading operation is Index and the cost is under 1.0. Fix the filters first, add External Id or Unique indexes where you own the field, and only then ask Support for a custom index or a skinny table.

Most “Salesforce is slow at scale” problems are a handful of non-selective queries wearing a trench coat, and they’re cheaper to fix the week before the object crosses the threshold than the week after. If limit errors and slow queries are a pattern rather than an incident, it’s usually technical debt worth scoring and paying down.

Understanding the basics

What makes a SOQL query selective in Salesforce?

A query is selective when its filter uses a database index and targets fewer rows than the index’s threshold. A standard index stays selective up to 30% of the first million rows plus 15% beyond, capped at one million; a custom index up to 10% then 5%, capped at 333,333. If the filter matches more rows than that, or uses no index, the optimizer runs a full table scan instead.

How do I fix “Non-selective query against large object type”?

Add an indexed, selective filter to the query. Filter on a field that is already indexed (Id, Name, OwnerId, CreatedDate, a lookup, an External Id or Unique field), and avoid operators that defeat the index: leading wildcards, not-equals, NOT IN, and null checks. If no existing field works, mark your integration key as an External Id to auto-create an index, or ask Salesforce Support for a custom index. The error fires for queries inside triggers against objects over roughly 200,000 rows.

How do I check whether my query is selective?

Enable the Query Plan tool in the Developer Console preferences, then click Query Plan in the Query Editor. Read relativeCost (above 1.0 is non-selective), the Leading Operation Type (Index good, TableScan bad), and Cardinality (the estimated matching rows). Run it against production-like data volume, because a query selective on a small sandbox can scan at scale.

Can I create a custom index myself?

Not directly for a general field. Marking a field as External Id or Unique auto-creates an index, which you can do yourself. Any other custom index is a Salesforce Support request, and some field types (multi-select picklists, long and rich text areas, non-deterministic formulas, encrypted text) can’t be indexed at all.

---

Fighting slow queries, timeouts, or non-selective-query errors as your data grows, and wondering whether the fix is an index, a skinny table, or a data-model change? Talk to us. Getting the data foundation to hold up under real volume is the work we do.