How Salesforce Executes a Query

🌐 Also in 19 languages

Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Français (Canada) · Deutsch (Schweiz)

Image generated with AI

Module 4 of 5 in the chapter Query and Retrieval Architecture, chapter 8 of the Data Architect course. Each module stands on its own. Previous: When Salesforce Uses an Index · Next: Search Is Not Query

The question this module answers: Who decides how Salesforce runs your query, and how does it choose?

In the last module, three things had to be true before the nitro fires: an index, a selective way of asking, and not too many matches. So who checks all three, every time a query runs? We’ll meet them in a minute. First, four words, because that’s how they think.

Four Words to Know

I put four words on the board at this point in class, because we’ll use them for the rest of the chapter:

• Query: finding the records you asked for. The search.
• Fetch: pulling the fields for those records. Once you’ve found the books, how much do you carry back: one field or a hundred?
• Table scan: touching every record to find the ones you want. In the library, it’s walking every aisle and opening every book, like the Harbor Press question in the last module, where there was no drawer by publisher. Fast at a hundred records, slow at 50 million.
• Join: crossing from one object to another. Remember the join you never wrote, in module 1.

Every request does the first two: find the records, then carry them back. They’re different costs, and it helps to keep them apart. The query is about which records: is it 12 or 12 million? The fetch is about how much you carry back: the number of records times the number of fields. A thousand records with five fields is a light load. A million records with a hundred fields is a truck. An index helps the query, the finding. It doesn’t make a big fetch small. Table scans and joins are the expensive ones. A fast query finds a small number of records quickly and fetches only the fields it needs.

The Librarian Is the Query Optimizer

If you walked into a library and asked for a book, who would you talk to? The librarian. Salesforce has a librarian too. What’s the librarian’s name? The query optimizer.

When your query arrives, the librarian doesn’t run straight to the shelves. Salesforce describes the steps: “First, the platform executes ‘pre-queries’ that consider the multitenant-aware statistics. Then, based on the results returned by the pre-queries, the service builds an optimal underlying database query for execution” (Platform Multitenant Architecture (opens in a new tab)). In library terms, the librarian checks first: how many books are likely to match? Is there a drawer for this? And who’s asking? A manager who can see every record and a salesperson who sees only their own get different plans, because the platform keeps statistics for “users with high visibility (a manager that can see all rows) versus users with low visibility (sales people that can only see rows related to themselves)” (same source).

Then the librarian decides. “Every book whose title starts with ‘Plumbing’”: there’s a title drawer, so use it. “Every book published after 1950”: nine million of the 10 million books qualify. Should the librarian look up 9 million cards and then fetch 9 million books? At some point the shortcut stops being a shortcut, and the librarian decides to just walk the shelves. That an index exists doesn’t guarantee Salesforce will use it. That decision belongs to the query optimizer (Query Optimizer (opens in a new tab)).

Here’s the full job description, in Salesforce’s words. The query optimizer works on the queries behind “reports, list views, and SOQL queries”, and it:

• “Determines the best index from which to drive the query, if possible, based on filters in the query”
• “Determines the best table from which to drive the query, if no good index is available”
• “Determines how to order the remaining tables to minimize cost”
• “Injects custom foreign key value tables that are required to create efficient join paths”
• “Influences the execution plan for the remaining joins, including sharing joins, to minimize database input and output (I/O)”
• “Updates statistics”

(Lightning Platform Query Optimizer (opens in a new tab), Best Practices for Deployments with Large Data Volumes, Winter ‘27.) Look for the four words in that list. Driving from an index, or from the table when no good index is available, decides whether the query is a quick lookup or a table scan. Ordering the tables and building the join paths is about the joins, including the hidden join for sharing. And only once the records are found does the fetch carry their fields back.

So follow one query on its way through. It arrives. The librarian checks what it knows: how many records, how they’re spread, who’s asking. Then it takes the filters one at a time and runs each through the three checks from the last module, the nitro:

1. Is the field indexed? A standard index, or a custom one.
2. Is it asked in a selective way? Equals, starts with, a short list, a range of dates or numbers. Not contains, ends with, not equal or empty.
3. Are there few enough matches? Under the selectivity threshold for that kind of index: 30% of the first million records for a standard index, 10% for a custom one, with their caps.

Each filter is judged on its own. A filter that passes all three can become the way in, and the query starts from that index. Then come the joins to any other objects, and finally the fetch of the fields you asked for. If no filter passes, the librarian walks the shelves: a table scan.

Click to enlarge

The librarian checks before walking.

You write the query. The librarian decides how it runs.

Test knowledge. The Lightning Platform query optimizer decides how every query, report and list view runs. It runs pre-queries against its statistics, judges each filter (is the field indexed, is it asked in a selective way, are the matches under the threshold), drives the query from the best index, or from the table when no good index is available, and orders the joins. The same query can get a different plan for a user who sees all records and one who sees only their own.

When Is the Librarian Working? All the Time

So when are you using the query optimizer? I ask this in class, and most people say: when a developer writes SOQL. The answer is every time. Every list view. Every report with a filter. Every dashboard built on those reports. Salesforce says so: the optimizer “works on automatically generated queries that handle reports, list views, and SOQL queries. The optimizer also handles other queries that rely on these generated queries” (Lightning Platform Query Optimizer (opens in a new tab), Winter ‘27).

That’s why the nitro checks from the last module matter far beyond code. An inefficient query doesn’t have to be written by a developer. It can be clicked together by anyone who builds a list view or a report:

• A list view on 9 million work orders with the filter Status not equal to Completed. A negative operator: the index on Status can’t help, whatever the volume.
• A report filter Account Name contains “Plumbing”. A leading wildcard, hidden behind a friendly word.
• A report type that joins work orders to line items to accounts. Every object it crosses is another join for the librarian to plan.

Nobody wrote a line of code, and every one of those runs through the same librarian with the same three checks. They’re buried in innocuous places: the list view a team opens fifty times a day, the report behind the manager’s dashboard. Often the fix is just a better question. Status equals Scheduled or In Progress, a short list of values, asks the same thing in a way the index can use.

And you can ask the librarian about these too, not only about SOQL. The REST API’s explain option works “to get feedback on how Salesforce executes your query, report, or list view”, and returns the plans “sorted from most optimal to least optimal” (Get Feedback on Query Performance (opens in a new tab), REST API Developer Guide, beta). The next section shows how to read a plan.

Test knowledge. The query optimizer runs for SOQL, reports and list views. An inefficient filter, like not equal to or contains, slows a list view or report filter just as much as it slows code.

Reports at Volume

Reports are where the librarian works hardest, because a report often asks for many records, across several objects, with a lot of fields. Salesforce’s best practices for reporting read like this chapter in four words (Reporting (opens in a new tab), Best Practices for Deployments with Large Data Volumes, Winter ‘27):

• Fewer records to look at (the query). “Use a value in the data to segment the query. For example, query for only a single state instead of for all states.” And “Use index fields in report filters, whenever possible.”
• Fewer joins. Minimize the objects queried in the report and the relationships used to build it.
• A lighter fetch. “Reduce the number of fields queried—only add fields to a report, list view, or SOQL query that is required.”
• Summaries stored, not recalculated. “Use summarized data stored on the parent record for the report. This practice is more efficient than having the report summarize the child records.”

Two of Salesforce’s own case studies show it at work. One customer’s monthly and yearly reports summed across custom objects of 4 million and 9 million records; the fix was “an aggregation custom object that summarized the monthly and yearly values into the required format for the required reports”, filled by batch Apex (Data Aggregation (opens in a new tab)). Another had a report across four objects, from 314,000 accounts to 2.3 million sales details, with very little filtering. The fixes: more filters, “as many filters as possible” indexable, less data in each object, a Recycle Bin kept empty (“Data in the Recycle Bin affects query performance”), and no complex sharing rules on those objects (Multi-Join Report Performance (opens in a new tab)).

Measure the Distribution, Then Ask the Optimizer

So how do you know what the librarian will choose? Two ways: count the data, then ask the librarian. Start with the count. Twenty million cases, filter on status. Someone says, “Status has six values, so each is about a sixth.” Why? Real data doesn’t spread itself evenly because a picklist has six values. Maybe it’s 70% closed, 15% resolved, 8% waiting on the customer, 4% escalated, 2% new, 1% other. The only useful answer is the actual distribution, or a defensible projection. Count the records grouped by the field’s values, as Salesforce’s own guide shows (Query Optimizer (opens in a new tab)), and stop arguing about whether status “seems selective.” Measure before you optimize. One detail: the record counts can include records in the Recycle Bin, so decide deliberately whether to count IsDeleted records when you measure (same source). “We deleted 5 million records, so why isn’t it selective now?” Were they really removed, or are they soft-deleted?

Counting tells you about your data. It doesn’t tell you what the librarian will do with it. For that, go one step further with the Query Plan tool. In Winter ‘27 you’ll find it in the new Web Console, where you build a query in the SOQL Builder and see its query plan; the Developer Console, where the tool used to live, no longer appears in the Setup menu by default (The Salesforce Developer’s Guide to the Winter ‘27 Release (opens in a new tab); Web Console, generally available (opens in a new tab)). Use it so you’re not saying “I think Salesforce should use this index” but asking the optimizer what it plans to do. It shows the cardinality (how many records the librarian expects the query to find), the fields, the leading operation type (an index, sharing, a table scan or another operation: in other words, how the query will start) and a cost. The plan with the lowest cost wins, and a cost above 1 means the filter isn’t selective (Query Plan Tool FAQ (opens in a new tab)). In class I use an open airports dataset to show it: a small change to a filter moves the estimated cost from something like 0.69 to 0.09. Much better than arguing about whether a query “looks efficient.” And cost isn’t runtime: 0.09 doesn’t mean 0.09 seconds. It’s a relative measure for comparing plans, not a stopwatch.

Click to enlarge

Don’t guess what the optimizer will do. Ask it.

More Than One Index: Which One Drives?

Now the librarian has more than one choice. Real queries rarely have one filter. Say you ask the library for every book on home repair and by A. Ortiz. There’s a subject drawer and an author drawer. Which one does the librarian open? The one that narrows the most. Home repair might be thousands of books; A. Ortiz might be twelve. Pull the twelve author cards, then check each of those books for the subject. In our four words: the author index drives the query, the subject is checked on the twelve books found, and only the books that pass both are fetched. That’s what Salesforce means by the index that drives the query: the optimizer “determines the best index from which to drive the query” (same source), and the other filters are checked along the way.

With AND, filters help each other. Salesforce’s own example: on an object where the threshold is 150,000, StageName = 'Closed Won' matches 49,899 and CloseDate = THIS_WEEK about 3,000, so both are selective. But even if Closed Won matched 250,000, over the line on its own, the combination can still be selective: the optimizer considers an AND selective when each filter targets less than twice its threshold, or when the records that match both are under the threshold (Lightning Platform Query Optimizer (opens in a new tab)). Steve Trucking: Retail_Partner__c = 'Redrock' AND Scheduled_Date__c = TODAY. The partner alone may be millions; together with today’s date, it’s a small set.

With OR, it’s the other way around. “With the OR operator, each filter must meet the threshold individually” (same source), and “All fields in the OR clause must be indexed for any index to be used” (Indexes (opens in a new tab), Winter ‘27). One unindexed field in an OR, or one filter over its threshold, and the librarian walks the shelves. So I don’t tune SOQL clause by clause; I look at the whole retrieval shape. And when an OR is the problem, Salesforce’s own fix is to split it: “If you use two indexed fields joined by an OR in the WHERE clause, your search results can exceed the index threshold. Break the query into two queries and join the results” (SOQL and SOSL (opens in a new tab), best practices, Winter ‘27). Two questions the librarian can each answer from a drawer, instead of one it has to answer by walking the shelves.

Click to enlarge

AND: drive from the smaller drawer, check the rest on those books. Fast.

Click to enlarge

OR, with a drawer for each part: both drawers, then the books on both lists.

Click to enlarge

OR, with one part that has no drawer: walk every shelf. Slow.

Two-Column Indexes

Now a variation, and it’s where the fetch comes back in. What if two fields are almost always asked together? Salesforce can build one index over both: a two-column custom index. They’re “useful for list views and situations where you want to use one field to select records to display and another field to sort them. For example, an Account list view that selects by State and sorts by City can use a two-column index with State in the first column and City in the second” (Indexes (opens in a new tab), Winter ‘27). And when two fields are a common filter together, “a two-column index on f1__c,f2__c is more efficient than single indexes on f1__c and f2__c” (same source).

Think of a card catalog sorted by subject, and within each subject by author. Ask for home repair by A. Ortiz and you go straight to the spot. In class I always add: the order of the fields matters. The first column is the one you select by: it serves the query, the finding. The second is kept in order within the first, like authors within each subject: it serves what happens after the finding, handing the records back in the order you want. That’s why Salesforce’s example is a list view: select this state’s accounts, show them sorted by city. Put the fields the other way round and the index no longer matches the question. A drawer sorted by subject then author doesn’t help much if you only know the author. For Steve Trucking, that might be Retail Partner first, then scheduled date. The value isn’t “two columns beat one.” It’s that the index matches an important, repeatable pattern.

One more detail from the same page: two-column indexes “can have nulls in the second column, whereas single-column indexes can’t unless Salesforce Customer Support explicitly enabled the option to include nulls.”

Test knowledge. A two-column custom index: first column to select, second to sort or narrow further; the order matters; nulls are allowed in the second column.

Before You Ask for an Index

You now have every index tool: standard indexes, custom indexes, External IDs, two-column indexes. So when someone says “this dashboard is slow, let’s index something,” the question isn’t “can we convince somebody to create one?” It’s should this field be indexed? Remember from module 2: every custom index request is a conversation with Salesforce Customer Support, and every index is another copy written on every save. Here’s the argument I want to see before asking:

• Used repeatedly. An important, everyday way of finding records, not a report someone runs once a year.
• Values that genuinely narrow the population. The filter has to come in under the threshold: 10% of the first million records for a custom index, with its cap.
• A known distribution. Counted, not guessed: how many records per value, today.
• An operational need. People or processes waiting on it: a list view, a report, an integration, an agent.
• A design that survives growth. Still under the threshold in year three and year five, with the 30% a year from the last module.
• Asked the right way. Equals or starts with, not contains or not equal, or the index won’t be used however well it’s justified.

One shortcut to avoid: marking a field as an External ID just to get the index. If it isn’t really an identifier from another system, don’t label it one for a performance side effect. Metadata should tell the truth. That’s your case for the request, and it’s the same case the librarian makes every time the query runs. I’m not trying to maximize the number of indexes. I’m trying to create the right retrieval paths.

Skinny Tables

Back to the four words one last time. Even with a good index, the join and the fetch still cost something on a huge object: remember from module 1 that standard and custom fields live in separate tables, joined behind the scenes.

Here’s the moment I describe in class. A giant object, millions of records, and your important reports, list views and queries keep reaching for the same small set of fields, again and again. You look at the pattern and you know it in your heart: we wish there were a smaller, flatter version of this object, built around just these fields.

That’s a skinny table. And first, let’s get one thing straight, because people mix them up: a skinny table is not an index. It’s a different tool for a different job. An index is a small copy of one field, kept in order, so the librarian can find records: it helps the query. A skinny table is a copy of the record itself, cut down to just the columns you keep asking for, with the standard and custom fields side by side in one table: it helps carry them back, the fetch, without the join. In Salesforce’s words, it’s “a custom table that contains a subset of fields from a standard or custom base Salesforce object” (Optimize Large Data Set Queries & Searches (opens in a new tab), Trailhead).

It’s another deliberate copy made for retrieval, kept in sync with the object, and the same trade-off as every copy in this chapter: copies make reads faster, and copies have to be maintained.

What does it buy you? Salesforce lists the wins:

• No join. “Read-only operations that reference only fields in a skinny table don’t require an extra join, and can consequently perform better” (Skinny Tables (opens in a new tab), Best Practices for Deployments with Large Data Volumes, Winter ‘27).
• A lighter fetch. “By having narrower rows and less data to scan than the base Salesforce object, skinny tables allows more rows per database to be fetched, increasing throughput when reading from a large object” (Trailhead, same source).
• Fewer rows. A skinny table “omits soft-deleted records”, the ones still in the Recycle Bin (LDV guide, same source).
• Indexes come along. “Custom indexes on the base table are also replicated, and they usually perform better because of the reduced table joins” (Trailhead). So the index still drives the query; the skinny table makes the rest of the trip shorter.

In library terms: picture a binder at the front desk with one line per book, just the handful of facts people keep asking for. Most questions get answered from the binder, without walking to the shelves to open each book.

And who decides when to use it? The librarian. “The platform determines at query runtime when it would make sense to use skinny tables, so you don’t have to modify your reports or develop any Apex code or API calls” (Trailhead). But only when it fits: the query, report or list view has to ask for fields that are all in the skinny table. Add one column that isn’t there, and the librarian goes back to the full object and the join.

The facts matter here:

• You can’t build one yourself. “If you want to use skinny tables, contact Salesforce Customer Support”, and “You can’t create, access, or modify skinny tables yourself.” If the report or query changes, for example to add a field, Support has to update the definition (LDV guide).
• They work on custom objects and on Account, Contact, Opportunity, Lead and Case, and “can enhance performance for reports, list views, and SOQL” (same source).
• At most 200 columns, and no fields from other objects (same source). Older material says 100: that was the limit in earlier editions of the same guide (Winter ‘16).
• The field types allowed are listed: checkbox, currency, date, date and time, email, number, percent, phone, multi-select picklist, text, text area, long text area and URL (same source).
• They’re copied to Full sandboxes; other sandboxes need Support to activate them (same source).

And Salesforce says it plainly: “Skinny tables aren’t a magic wand to wave at performance problems. There’s overhead in maintaining separate tables that hold copies of live data. Using them in an inappropriate context can lead to performance degradation instead of improvement” (same source). It’s the same trade-off as every copy in this chapter: copies make reads faster, and copies have to be maintained.

So think of them when a stable set of fields repeatedly serves important high-volume reads on a huge object, not “let’s put the whole object in there.” And a skinny table doesn’t make a bad filter selective. Ask for 95% of 50 million records and you still have a large retrieval problem. Indexes and selective filters get you the boost for the query; the skinny table helps with the join and the fetch. They work together, and they’re not the same thing.

Test knowledge. A skinny table is not an index. Salesforce Customer Support creates it; it holds up to 200 columns from one object (standard and custom fields together, no fields from other objects), leaves out soft-deleted records, avoids the join between standard and custom field storage, and is used automatically when a query, report or list view uses only its fields. Custom objects plus Account, Contact, Opportunity, Lead and Case. Copied to Full sandboxes only.

Divisions: Splitting a Huge Object

One more tool from the same family, and it’s on the exam: divisions. “Divisions are a means of partitioning the data of large deployments to reduce the number of records returned by queries and reports. For example, a deployment with many customer records might create divisions called US, EMEA, and APAC to separate the customers into smaller groups that are likely to have few interrelationships” (Divisions (opens in a new tab), Best Practices for Deployments with Large Data Volumes, Winter ‘27).

In library terms, it’s giving each region its own wing, so most questions only walk one wing. And it’s not an index either: it splits the records into groups, and Division is one of the standard indexed fields from module 2, so the librarian can go straight to one division’s records. Like skinny tables, you don’t turn it on yourself. “To use divisions, your organization must have over one million records in a single object and more than 35 licenses. Salesforce provides special support for partitioning data by divisions, which you can enable by contacting Salesforce Customer Support” (same source). Divisions suit data that really does fall into groups people work in separately. They’re not a way around a filter that isn’t selective.

Test knowledge. Divisions partition a large org’s data (for example US, EMEA, APAC) to reduce the records returned by queries and reports. They need more than one million records in one object and more than 35 licenses, and Salesforce Customer Support enables them.

Database Statistics: The Librarian’s Notebook

Remember the pre-queries at the start of this module: before the librarian picks a path, it checks how many records are likely to match. Does it count every book each time someone asks? No. It keeps a notebook: how many books there are, how many on each subject, how they’re spread. That’s what database statistics are. In Salesforce’s words: “Modern databases gather statistics on the amount and types of data stored inside of them, and they use this information to execute queries efficiently. Because of Salesforce’s multitenant approach to software architecture, the platform must keep its own set of statistical information to help the database understand the best way to access the data” (Database Statistics (opens in a new tab), Best Practices for Deployments with Large Data Volumes, Winter ‘27).

And here’s the catch: the notebook isn’t updated as you go. “When large amounts of data are created, updated, or deleted using the API, the database must gather statistics before the application can efficiently access data. Currently, this statistics-gathering process runs on a nightly basis” (same source).

So picture this. Last night, the notebook says 1 million contacts, 50,000 of them in Arizona: under the custom-index line of 100,000, so State = 'AZ' looks selective. This afternoon a migration loads another million contacts, all in Arizona. The table now has 2 million contacts and 1,050,000 in Arizona, far over the new line of 150,000. But the notebook still says 50,000. Until tonight’s statistics run, the librarian is planning from yesterday’s counts.

Click to enlarge

Until tonight’s statistics run, the librarian plans with last night’s counts.

That’s why I tell people in class: load a million records and you may see performance change, and it can take until the next day to settle. The reason isn’t that the index breaks. It’s that the notebook is a day behind. So:

• Plan big loads and big deletes so the nightly statistics run before people depend on the performance: before a quiet night, not an hour before go-live.
• Test performance the day after a big load, not the hour after. Otherwise you’re measuring yesterday’s notebook.
• Expect deletes to count too. Salesforce names creates, updates and deletes. Archive 5 million records today, and the counts catch up tonight.

Test knowledge. Salesforce keeps its own database statistics, the counts the query optimizer uses to choose a plan. After large data loads, updates or deletes through the API, statistics must be gathered before queries run efficiently; the gathering runs nightly.

When It’s Slow: Index, Rewrite, Redesign or Archive

You now know how the librarian thinks. So eventually someone comes to the architect: “This query is slow. Can we put an index on it?” Maybe. But that’s not my first answer. My first question is why is it slow? An index is one tool. Sometimes the query should be rewritten, sometimes the model is fighting us, sometimes we’re keeping 50 million records active that shouldn’t be, and sometimes we’re about to fix a one-off by adding permanent complexity. I don’t prescribe the medicine until I understand the disease.

And measure before you change anything. How many records, growing how fast? How are the filter values distributed? What’s already indexed? What does Query Plan say? How many rows does the query return, and is the problem even retrieval, or sharing, locking or automation? What happens at year three and year five? “The query returns 400,000 records, so we need an index” doesn’t tell me enough. 400,000 out of what? Out of 500,000 that’s terrible selectivity; out of 50 million it’s a different conversation. Do the math first.

Click to enlarge

An index is one answer, not the answer.

This is the decision I want architects practicing:

Index when an important, repeatable pattern has a suitable field with a useful distribution, and the index is a clean, general solution: the checklist in Before You Ask for an Index. Steve Trucking’s example: each Retail Partner’s delivery reference, a unique External ID on the work order. Care agents find one delivery out of 9 million by it, hundreds of times a day.

Rewrite when the model is reasonable but the question stops the librarian from using a good path. Before changing the schema: can we use a more selective positive filter, narrow by time, avoid a leading wildcard, replace a broad negative condition with a small positive one, split the retrieval, or recognize that this is really a text-search problem that belongs in SOSL rather than a pile of SOQL ORs (the next module)? Don’t change the architecture to compensate for a bad question when you can ask a better one. And for “everything changed since the last run”, filter on SystemModstamp, which is indexed, rather than LastModifiedDate, which isn’t (module 2’s tables); the integration chapter covers incremental sends. Two of Salesforce’s case studies are rewrites too. In one, a query for the records created in the last three days matched too many records for the standard threshold and ran slowly. Rewritten with ORDER BY CreatedDate LIMIT 99999, “the threshold checks were not done, and the CreatedDate index was used to find the records” (Sort Optimization on a Query (opens in a new tab)). That’s a documented case, not a general trick: check it with the Query Plan for your own query. In the other, an integration used a user inside the sharing hierarchy to limit what it read, across several million records, and its queries took minutes. Sharing “works very well for UI interactions”, but “when used as a high-volume data filter in a SOQL query, performance can suffer”; the fix was “to give the query access to all the data, and then to use selective filters”, or to extract only what changed (API Performance (opens in a new tab)). An integration user that can read everything is a security decision as well as a performance one, so make it deliberately.

Redesign when the retrieval matters and the model itself makes it expensive. Work order line items, about 14.5 million in five years, and an important query needs recent line items for one Retail Partner. But the partner lives on the work order, not the line item. Copy Retail_Partner__c onto the line item, set it when the line item is created, maybe index it, and the query can filter the child directly. That’s deliberate denormalization: duplicate data and logic to keep it right, in exchange for a direct path across millions of records (Design to Third Normal Form, Then Back Off makes the full case). Redesign can be small, too. If “unassigned” is a real business state, store it: a query can ask for Routing_Status__c = 'Unassigned' and use an index, where Route__c = null can’t by default. And if the empty values are already there, Salesforce’s own case study shows a way out: a formula field that turns an empty value into a word, IF(ISBLANK(Status__c), "blank", Status__c). It’s deterministic, so it can be indexed (module 2), and the query becomes Status_Value__c = 'blank' (Indexing with Nulls (opens in a new tab)).

Archive when history that no longer belongs in the active workload dominates the population you’re querying. Fifty million records, and the operational process only needs the last 18 months. You can spend a long time making queries against all 50 million faster, or you can ask why are all 50 million still active? Sometimes the best query optimization is a smaller active population (If You’re Not Archiving Today…).

Click to enlarge

Four answers to a slow query. Do the math first, then choose.

And one warning. Someone learns that Salesforce has standard indexes, custom indexes, External IDs, two-column indexes and skinny tables, and starts looking for places to use them all. That’s backwards. Start with what are we trying to retrieve, at what volume, how often, with what distribution, and how that will change, then choose the smallest mechanism that solves it cleanly.

Correct Isn’t the Same as Scalable

A SOQL query can be syntactically perfect, return exactly the right records, run beautifully in a developer org, and still be architecturally wrong. Salesforce’s Well-Architected guidance lists it as an anti-pattern: “Small-scale testing with minimal data volumes (query optimizer behavior changes dramatically at 10M+ records making small-scale results misleading)” (Availability & Performance Patterns (opens in a new tab)). In a sandbox with 5,000 deliveries, a table scan is tiny and the query feels instant. In production with 50 million, the same SOQL, the same Apex, the same logic is a very different problem. Nothing in the code changed. The Number of Zeros changed. That’s why retrieval architecture can’t wait for performance testing at the end: the data model already decided which paths would be available.

So prove the design against realistic volume, not 500 records, not “it seemed fast in my developer sandbox.” And just as important, realistic distribution. Ten million records of perfectly random test data may tell you little about a production system where 80% share one status, 40% belong to one Retail Partner, most history is completed, and a small active window gets almost all the updates. Selectivity depends on distribution; performance testing has to model the shape of the data, not just the count. Salesforce’s Well-Architected guidance says the same thing: “Developer Sandboxes copy only configuration and contain no data. Testing against realistic volumes in a Full Copy Sandbox surfaces scalability problems before customers do” (Resource and Cost Optimization (opens in a new tab)). For serious large-volume work, a Full sandbox matters: it can carry production-scale volume and distribution, and production’s skinny tables are copied there. Test basic behavior early, but before go-live, I want evidence the design survives the Number of Zeros you expect.

A developer eventually writes SELECT … FROM … WHERE …, but that query’s success was decided months earlier: by the data model, the field types, which values could be null, which relationships existed, which fields were indexed, how values were distributed, how much history stayed active, and how much data we expected in five years. The query doesn’t fix those decisions. It inherits them.

Headless, Agents and AI: The Librarian Is Still Deciding

Now a question I get more and more. What happens when nobody opens a list view at all? In April 2026 Salesforce put it plainly: “Everything on Salesforce is now an API, MCP tool, or CLI command, and agents can use all of it” (Introducing Salesforce Headless 360 (opens in a new tab), April 15, 2026). No browser, no screen, an AI agent asking for records directly. Does the librarian retire?

No. If the data lives in Salesforce core, every one of those requests still becomes a query, and the same librarian runs it with the same three checks. Look at the tool Salesforce gives AI agents to read records through its hosted MCP server. It’s called soqlQuery, it “Executes a SOQL query to retrieve Salesforce records”, and it comes with this instruction for the agent: “Always include a WHERE clause to filter results and a LIMIT clause to control result size. For large objects, filter on indexed fields (Id, Name, foreign keys, External IDs) to avoid timeout errors” (SObject All (opens in a new tab), Hosted MCP Servers, checked 2026-10-22). Salesforce is teaching the agents the nitro checks.

So headless doesn’t make this module less important. It makes it more important. A person opens a slow list view a few times a day and complains. An agent can ask the same question hundreds of times an hour, and it doesn’t know that Status has 9 million Completed records, that the field it filters on has no index, or that its “contains” can’t use one. It learns your data only through the paths you designed. The more of your business runs through APIs and agents, the more the architect’s choices decide whether those queries get the boost: which fields are indexed, which identifiers are External IDs, which filters stay selective as the data grows. And you can check an agent’s queries the same way as anyone else’s: run them through the Query Plan, or the REST API’s explain.

One boundary, to be fair about it. This is the librarian for data in Salesforce core. Text search (FIND) has its own engine, as module 5 covers, and data that lives in Data 360 is stored and processed outside core, with its own way of answering (module 6). The principle travels, though: whatever the engine, someone has to choose a path to your data, and a good path is designed, not hoped for.

What an Architect Decides Here

• Which important queries, reports and list views have a selective filter the optimizer can drive from.
• Whether the Query Plan confirms the path you expect, at realistic volume, before a query or index is accepted.
• How AND and OR filters combine, and which index should drive.
• Whether a pair of fields asked together justifies a two-column index, and in which order.
• Whether a stable set of fields on a huge object justifies a skinny table, and what it costs to keep.
• When a query is slow: whether to index, rewrite, redesign or archive, after doing the math.
• How performance testing models the real volume and distribution, not just the record count.

Explore

• Salesforce Developers, Best Practices for Deployments with Large Data Volumes (Winter ‘27): Indexes (opens in a new tab) and Skinny Tables (opens in a new tab), checked 2026-11-01.
• Salesforce Architects, Well-Architected, Resource and Cost Optimization (opens in a new tab), checked 2026-11-01: testing at realistic volume in a Full Copy sandbox; Query Selectivity.
• Salesforce Developers, Metadata API Developer Guide, CustomIndex (opens in a new tab), checked 2026-11-01.
• Salesforce Developers, Lightning Platform Query Optimizer (opens in a new tab) (Best Practices for Deployments with Large Data Volumes, Winter ‘27), checked 2026-11-01: what the optimizer does, and how AND and OR filters are judged.
• Salesforce Developers Blog, Announcing General Availability: Web Console (opens in a new tab) (September 2026) and The Salesforce Developer’s Guide to the Winter ‘27 Release (opens in a new tab) (October 2026), checked 2026-11-01: the query plan in Web Console; Developer Console hidden from Setup by default.
• Salesforce Architects, Platform Multitenant Architecture (opens in a new tab), checked 2026-11-01: pre-queries and statistics by who’s asking (Queries).
• Salesforce Help, Developer Console Query Plan Tool FAQ (opens in a new tab), version 5, checked 2026-11-01: reading a query plan, and cost above 1 means not selective.
• Salesforce Developers, Best Practices for Deployments with Large Data Volumes (Winter ‘27), Database Statistics (opens in a new tab), checked 2026-11-01: statistics gathered nightly.
• Salesforce Architects, Well-Architected, Availability & Performance Patterns (opens in a new tab), checked 2026-11-01: test at production-scale volume.
• Salesforce Developers, REST API Developer Guide, Get Feedback on Query Performance (opens in a new tab) (beta), checked 2026-11-01: explain for a query, report or list view.
• Salesforce News, Introducing Salesforce Headless 360. No Browser Required. (opens in a new tab), April 15, 2026, checked 2026-11-01.
• Salesforce Developers, Hosted MCP Servers, SObject All (opens in a new tab), checked 2026-11-01: the soqlQuery tool and its instructions to agents.
• Trailhead, Optimize Large Data Set Queries & Searches (opens in a new tab), checked 2026-11-01: skinny tables, fetching more rows per read, and replicated custom indexes.
• Salesforce Developers, Best Practices for Deployments with Large Data Volumes (Winter ‘27), checked 2026-11-01: Divisions (opens in a new tab); best practices for Reporting (opens in a new tab) and SOQL and SOSL (opens in a new tab); case studies Data Aggregation (opens in a new tab), Multi-Join Report Performance (opens in a new tab), Indexing with Nulls (opens in a new tab), Sort Optimization on a Query (opens in a new tab) and API Performance (opens in a new tab).