Not verified hands-on. This article was written from primary documentation rather than reproduced in an org, so it carries no tested-release badge. If you run it and something differs, say so in the comments and it gets tested and corrected.

The orienting fact: Salesforce does not run your SOQL against your data directly. A cost-based optimiser rewrites it against a shared multi-tenant schema, and it picks one of four strategies — an index on your filter, an index driven by sharing, a scan of the object, or an “other” path such as a full-table plan. Which one it picks explains nearly every performance surprise you will hit, and the Query Plan tool shows you the choice before you ship.

Why an optimiser is involved at all

Your custom object is not a table. Records from every org on the instance live in shared structures, with custom field values held in flexible columns and your org keyed alongside them. Every query is therefore rewritten to include your org and the object type before anything else is evaluated.

That rewrite is why “just add an index” does not behave the way it would on a database you own, and why the optimiser’s cost estimates matter more than the shape of the SQL you would have written.

The four plans

Plan What it means Typical cause
Index Drives from an index on a filter field Selective filter on an indexed field
Sharing Drives from sharing rows for the running user Private OWD, user sees few records
TableScan Walks every row for the object in your org No usable index on any filter
Other A path not in the above categories Often ORDER BY or an aggregate driving the plan

Each candidate gets a Cost. Below 1.0 the optimiser expects the plan to beat a scan and will use it. At 1.0 or above it will not, and in a governed context that becomes the non-selective query exception.

The Sharing plan is the one nobody predicts

When an object’s org-wide default is Private, the optimiser can drive from the sharing tables rather than from your filter. For a user who can see 200 of five million records, that is dramatically faster than any filter-based plan.

The trap is that it inverts with the user. The same query is instant for a sales rep with narrow visibility and slow for a manager high in the role hierarchy who can see most of the table. Performance complaints that only come from executives usually have this shape, and testing as an admin hides it completely — an admin with View All Data does not get a sharing plan at all.

If a query matters, run the Query Plan as a representative user, not as yourself.

What makes a filter usable

The optimiser will consider an index only when the filter can drive one. These defeat it:

  • Negation — !=, NOT IN, NOT LIKE.
  • Leading wildcards — LIKE '%urgent'. A trailing wildcard is fine.
  • Nulls on standard fields — WHERE Field != null typically cannot use the index.
  • Non-deterministic formulas — anything with TODAY(), NOW(), cross-object references, or owner-dependent logic cannot be indexed at all.
  • Type mismatch — comparing a text field to a number forces a conversion that discards the index.

And a filter can be indexed and still be rejected as unselective, because selectivity is about how many rows come back: under 10% of the first million, under 5% after that, capped at 333,000.

Reading a Query Plan properly

Developer Console → Help → Preferences → Enable Query Plan, then run the query in the Query Editor and open the Query Plan panel. You get one row per candidate plan, ordered by cost.

  • Cost — below 1.0 means the plan is expected to beat a scan.
  • Cardinality — rows the optimiser expects the filter to match.
  • sObject Cardinality — rows it thinks the object has.
  • Fields — which field drove the plan. Blank on a TableScan.

Cardinality against sObject Cardinality is the selectivity calculation made visible. If the first is a large fraction of the second, no index is going to rescue the query, and the filter itself has to change. Read the fraction before you open a case asking for an index.

One caveat worth keeping: these are estimates from statistics that are refreshed periodically, not a measurement of the current table. A plan taken immediately after a large load can be based on stale numbers.

Turning a bad plan into a good one

Add a bounded, indexed filter. A date range on CreatedDate or LastModifiedDate is available on every object and is usually the cheapest fix that works.

Invert negations. Status__c NOT IN ('Closed','Cancelled') becomes Status__c IN ('Open','Pending','Escalated'). Same rows, an index that can now be used.

Trim the SELECT list. Fewer fields means less to fetch per row, which does not change the plan but does change how long executing it takes.

Ask for a custom index only when the field is genuinely selective in your data and no narrower filter is available. Requesting one is a support case, not a setting.

Frequently asked

Why is the query fast in the Developer Console and slow in production?

Usually the running user. You are probably an admin with broad visibility and a warm cache. The user complaining has a different sharing plan and a cold one. Reproduce with System.runAs or by logging in as an affected user.

Does the optimiser use more than one index at a time?

It picks one driving path and applies the remaining filters to those rows. That is why one good filter beats three mediocre ones, and why adding filters does not reliably make a query faster.

Do standard and custom indexes behave the same?

Broadly yes from your side — the thresholds and the way the optimiser decides are the same. What differs is that a custom index is created by Salesforce Support for a specific field, and it can only be created on field types that are indexable in the first place.

Does adding LIMIT change the plan?

LIMIT does not make an unselective query selective, because the rows still have to be identified before they can be limited. It can reduce what is returned, which helps with the row limit, but it is not a performance fix.

Does the plan change as data grows?

Yes, and that is the thing to plan for. A filter that is selective at 100,000 rows may not be at five million, so a query that has been fine for two years can fail without anybody changing it.

Reference: Query & Search Optimization Cheat Sheet for Large-Scale Orgs, Best Practices for Deployments with Large Data Volumes and Working with Very Large SOQL Queries, Salesforce Architect and Apex Developer Guide resources.