July 28, 2026

Large Data Volumes: When a Query Stops Being Selective

Selectivity is a threshold, not a property. A query written when an object held 50,000 records fails at two million, with the same filter and no code change.

TL;DR

A query is selective when its filter matches few enough rows that the optimiser will use an index instead of scanning the object.
The thresholds are proportions, not fixed counts: a standard index is used when the filter matches under 30 percent of the first million records and 15 percent beyond that, capped at 1,000,000 rows. A custom index allows 10 percent and 5 percent, capped at 333,333.
Because they are proportions of a growing object, a query stops being selective without anyone changing it. This is the same class of failure as row locking: the data moved, the code did not.
Salesforce may terminate non-selective queries against objects with more than 200,000 records, which is why this presents as a sudden failure rather than a gradual slowdown.
The Query Plan tool in the Developer Console tells you what the optimiser will do, before production tells you.
Fixes in order: make the filter selective, add a custom index, then consider skinny tables and archiving. Indexing a bad filter does not help.

What You'll Learn

What selectivity means mechanically, and the numbers behind it
Why working code fails at scale with no deployment
How to read a query plan and what cost actually means
Which filter patterns defeat indexes no matter what you index
The order to apply fixes, and which ones need Salesforce involved

The Problem

The report ran fine for three years. The integration never timed out. Then one Tuesday the query starts failing, and the error says the query is non-selective against a large object.

Nobody deployed anything. The filter is the same filter. What changed is the object grew past a threshold, and the optimiser stopped being willing to use an index for a filter that now matches too large a share of the table.

This is the most confusing property of large data volumes work: selectivity is not a property of your query, it is a relationship between your query and the current size of the object. The same SOQL is selective at 50,000 records and non-selective at two million. There is no line of code to blame and no commit to revert.

It is the same failure shape as UNABLE_TO_LOCK_ROW, which also appears in code nobody changed because the data underneath crossed a line.

Common questions this article answers:

Why did a query that worked for years suddenly fail?
What actually makes a query selective?
Which fixes work, and in what order?

Quick Answer

The Salesforce query optimiser will use an index only when your filter is estimated to match a small enough share of the object. For a standard index that is under 30 percent of the first million records and 15 percent of anything beyond, capped at one million rows. For a custom index it is 10 percent and 5 percent, capped at 333,333 rows. If your filter exceeds the threshold the optimiser falls back to scanning, and Salesforce may terminate non-selective queries against objects holding more than 200,000 records rather than let them run long. Because the thresholds are proportional, a query written when the object was small stops qualifying as it grows, without any code change. Diagnose with the Query Plan tool in the Developer Console, which shows which index the optimiser would use and the estimated cost, where anything above 1.0 means no usable index. Fix in order: restructure the filter so it is selective, add a custom index on the field you filter on, then consider skinny tables and archiving for objects that are simply too large. Indexing a filter that cannot use an index, such as a leading wildcard or a negative operator, changes nothing.

What selectivity actually means

The optimiser makes one decision: use an index, or scan.

Scanning a small object is fine. Scanning an object with millions of rows is not, so Salesforce imposes thresholds above which it will not use an index because the index would return so many rows that scanning is no better.

Index type	Threshold
Standard	30% of first 1,000,000 records, then 15% of the remainder, capped at 1,000,000 rows
Custom	10% of first 1,000,000 records, then 5% of the remainder, capped at 333,333 rows

The custom index thresholds are stricter, which surprises people who assume adding an index makes a query selective by definition. It does not. A custom index on a field where 40 percent of records share the same value will not be used, because 40 percent is far beyond the 10 percent allowance.

That is the practical rule worth carrying: an index helps a filter that selects a small share of the object, and does nothing for a filter that selects a large one. Indexing a boolean field where half the records are true is wasted effort.

Why it fails without a deployment

Work through the arithmetic and the surprise disappears.

Suppose a custom object holds 400,000 records, and your query filters on a status field where the value you want covers 8 percent of them. Custom index threshold at that size is 10 percent, so 8 percent qualifies. The query is selective and fast.

Two years later the object holds 3 million records, and that status still covers 8 percent, which is now 240,000 rows. The threshold is 10 percent of the first million plus 5 percent of the remaining two million, so 100,000 plus 100,000, which is 200,000. Your 240,000 rows exceed it. The optimiser stops using the index.

Nothing about the query changed. The proportion did not even change. The object grew, and the allowance grows more slowly than the object does, which is the whole trap.

This is why LDV problems arrive as cliffs rather than slopes. You are fine, and fine, and then you are not, because a threshold is a step function.

Reading the query plan

The Developer Console has a Query Plan tool, enabled from preferences, and it answers the question directly rather than by inference.

Run your query through it and you get one row per plan the optimiser considered, with:

Cardinality, the estimated number of records matched
Leading operation type, which is Index when it will use one, TableScan when it will not
Cost, where anything above 1.0 means the optimiser will not use that plan, and the lowest-cost plan wins
sObject cardinality, the estimated total rows in the object

The number to look at is cost. Below 1.0 and you have a selective query. Above 1.0 on every row and you are scanning, regardless of what indexes exist.

The value of this is that it is a pre-production check. You can run the query plan against a full-volume sandbox and know the answer before a deployment finds out for you. Making it part of code review for any query against a large object costs a minute and prevents the Tuesday morning failure.

Filters that defeat indexes

Some filters cannot use an index no matter what you index, so the first question is always whether the filter is capable of selectivity before asking whether it is under threshold.

The well-established ones:

Leading wildcards, such as LIKE '%acme'. An index is ordered, so a search that does not know the beginning cannot use it.
Negative operators, including !=, NOT IN and NOT LIKE. Asking for everything except a value usually matches most of the object, which is the definition of non-selective.
Comparisons against null on fields that are not indexed for nulls.
Formula fields, unless they are deterministic and have been indexed. A formula that references other objects or dynamic values cannot be indexed at all.

There is also a specific limit worth knowing: with the CONTAINS operator, a query becomes non-selective once more than 333,333 rows would need scanning.

The pattern across all of these is the same. Restructure the filter before reaching for an index. Replacing != with an explicit IN list of the values you do want often converts a non-selective query into a selective one with no infrastructure change at all, because you have changed the question from "everything except" to "these few".

The fixes, in order

1. Make the filter selective. Cheapest, fastest, and frequently sufficient. Add a filter that genuinely narrows: a date range, a record type, an owner. Two filters that are each moderately selective combine well, since the optimiser considers them together. Replace negative operators with positive ones.

2. Add a custom index. Where a field is genuinely selective but not indexed, a custom index is the right answer. Some are automatic, including Id, Name, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, master-detail and lookup fields, and anything marked External Id or Unique. Others need to be requested through Salesforce support, so this fix has a lead time and should not be discovered during an incident.

3. Skinny tables. A Salesforce-created table containing frequently used fields, avoiding joins between the base and custom field tables. They help reporting and list views on very large objects, are created by support rather than by you, and are worth asking about only once the first two options are exhausted.

4. Reduce the data. Archiving, or moving cold records out of the object, is the only fix that changes the arithmetic rather than working within it. It is also the slowest and the one with the most stakeholders, which is why it tends to be considered last despite being the durable answer for an object that simply keeps growing.

Two notes on sequencing. Do not index first, because an index on a non-selective filter changes nothing and you will have spent a support cycle to learn that. And do not start with archiving, because it is the largest project and often unnecessary once the filter is fixed.

Frequently Asked Questions

Q: Why did a query that ran for years suddenly fail?

A: The thresholds are proportions of the object, and the allowance grows more slowly than the object. A filter matching a constant share of a growing table eventually exceeds it. Nothing in your code has to change for this to happen, which is why it presents as a sudden failure with no deployment behind it.

Q: Will adding an index always fix it?

A: No, and this is the most common misunderstanding. Custom index thresholds are stricter than standard ones, so a field where a large share of records hold the same value will not use its index. Indexes help filters that select a small proportion. They do nothing for filters that select a large one, or for filters that cannot use an index at all, such as leading wildcards.

Q: How do I know before it breaks?

A: The Query Plan tool in the Developer Console, run against a full-volume sandbox. Cost above 1.0 on every plan means a scan. Making that a review step for queries against large objects is the cheapest prevention available.

Q: What counts as a large object?

A: Salesforce may terminate non-selective queries against objects with more than 200,000 records, so treat that as the point where selectivity stops being theoretical. In practice, start paying attention well before it, because the object will cross it while you are not looking.

Q: Do these thresholds apply to reports and list views too?

A: The same optimiser and the same selectivity logic sit behind reports, list views and SOQL, which is why an object that has grown past a threshold produces slow reports and timing-out list views at about the same time as it produces failing queries. They are symptoms of one cause.

Key Takeaways

Selectivity is a relationship between your filter and the object's current size, not a property of the query.
Standard indexes allow 30 percent then 15 percent, capped at a million rows. Custom indexes allow 10 percent then 5 percent, capped at 333,333.
Working queries fail without a deployment, because the allowance grows more slowly than the object.
The Query Plan tool answers the question before production does. Cost above 1.0 means a scan.
Some filters cannot use an index at all, so restructure the filter before requesting one.
Fix in order: filter, index, skinny table, archive. Indexing first wastes a support cycle.

What's Next?

Recommended Reading:

Action Items:

Enable the Query Plan tool in Developer Console preferences and run your heaviest queries through it against a full-volume sandbox.
List the objects in your org above 200,000 records, since that is where selectivity stops being theoretical, and check what queries and reports hit them.
Before requesting any index, check whether the filter is capable of selectivity at all. Negative operators and leading wildcards cannot be rescued by indexing.

Resources & References

Written by the team at CloudCounsel — builders of Paywren and Mehfil.

Responses

Checking your session.

Loading responses.