🗄️ DataIntermediate2026-03-04

SOQL Optimization: Indexed Fields, Selective Queries, and the Query Plan Tool

Slow SOQL queries are the #1 performance killer in Salesforce orgs with large data volumes. The difference between a query that takes 200ms and one that takes 30 seconds is almost always about indexed fields and selectivity. Salesforce maintains indexes on certain fields (Id, Name, CreatedDate, SystemModstamp, RecordTypeId, and all lookup/master-detail fields), and custom indexes can be requested through Salesforce Support.

A query is "selective" if its WHERE clause filters down to less than 10% of the total records (or 333,333 records, whichever is less) for standard indexes, or less than 5% (or 100,000) for custom indexes. Non-selective queries on objects with more than 200,000 records will throw a "non-selective query" exception. The fix is to always filter on indexed fields first, then add non-indexed conditions.

Use the Query Plan tool in the Developer Console (Settings > Enable Query Plan) to see exactly how Salesforce executes your query. It shows you which index is used, the estimated cost, and whether a table scan is happening. Before writing any SOQL in a trigger or batch class, run it through the Query Plan tool. This single habit prevents more performance issues than any other practice.

Code Example

// BAD: Non-selective query (table scan)
SELECT Id, Name FROM Account
WHERE Custom_Text_Field__c = 'Active'
// If Account has 500K records and Custom_Text_Field__c
// is not indexed, this scans every row.

// GOOD: Use indexed fields in WHERE clause first
SELECT Id, Name FROM Account
WHERE CreatedDate = LAST_N_DAYS:30
AND Custom_Text_Field__c = 'Active'
// CreatedDate is indexed. Salesforce filters by index first,
// then applies the non-indexed filter on the smaller result set.

// BETTER: Use Id or lookup fields
SELECT Id, Name FROM Account
WHERE OwnerId = :currentUserId
AND Custom_Text_Field__c = 'Active'
// OwnerId is a lookup (indexed). Very selective.

// Query Plan Tool output example:
// ┌──────────────┬──────────┬────────────────────────┐
// │ Leading Op   │ Cost     │ Note                   │
// ├──────────────┼──────────┼────────────────────────┤
// │ TableScan    │ 1.03     │ BAD - full table scan  │
// │ Index: Owner │ 0.00123  │ GOOD - uses index      │
// └──────────────┴──────────┴────────────────────────┘
// Cost < 1 = uses index. Cost > 1 = table scan.

// Pro tip: Compound indexes
// Salesforce Support can create a "skinny table" or
// custom index on frequently queried field combinations.
// Request via a support case for fields you filter on together.

Share this tip on X

Need this implemented in your org?

I've shipped these patterns in production for 10+ years.

View Consulting →

Testing LWC with Jest: The Patterns That Actually Work in CI/CD Data Loader Best Practices: Batch Sizes, Bulk API, and Surviving Large Imports

Enjoyed this? Get more like it.

Glen's Musings — AI, investing, and building things. Occasional. Free.

More Data Tips

Beginner

Data Loader Best Practices: Batch Sizes, Bulk API, and Surviving Large Imports

Data Loader is deceptively simple. The UI makes it look like you just pick a CSV and click Import. But the settings you ...