Salesforce同条件查询同一对象返回记录数不同原因咨询

Great question—this is one of those tricky Salesforce quirks that trips up even seasoned admins and developers. Let’s break down the most likely causes for this head-scratching discrepancy:

1. Covered Index vs. Full Table Scan Differences #

When you only query the Id field, Salesforce can leverage a covered index—an index that includes exactly the fields needed for your query (Id and LastModifiedDate). Covered indexes are ultra-efficient, but they might exclude records in transitional states:

• Recently deleted records that haven’t been fully removed from the index
• Records where LastModifiedDate updates haven’t propagated to the index yet (rare for historical queries, but possible)

When you add additional fields to your query, Salesforce can’t use the covered index anymore and switches to a full table scan (or a scan of a broader index that includes your extra fields). This scan will pick up records that were omitted by the streamlined covered index, leading to a higher record count.

2. Soft-Deleted Records (Hidden Edge Case) #

You might be thinking, "But I didn’t use ALL ROWS!"—and that’s true, but there’s a critical edge case here: when a record is soft-deleted, its LastModifiedDate gets updated to the deletion timestamp. If that timestamp falls within your date range, some query execution plans might include these deleted records when fetching multiple fields (even without ALL ROWS), while the covered index for Id only includes active records.

To test this, append ALL ROWS to both queries and compare the counts. If the gap disappears, this is almost certainly the culprit.

3. Millisecond Precision in LastModifiedDate #

Salesforce stores LastModifiedDate with millisecond precision, but SOQL queries using timestamps without milliseconds (like 2020-04-19T01:03:21Z) are interpreted as 2020-04-19T01:03:21.000Z.

Covered indexes sometimes truncate timestamps to seconds, so records with LastModifiedDate = 2020-04-19T01:03:20.999Z would be included, but if the index rounds values up, some records might be excluded. When running a full table scan, the database checks the full millisecond value, which could lead to more records being included if your date range boundary is tight.

To verify, adjust your upper bound to include milliseconds (e.g., <2020-04-19T01:03:21.999Z) and see if the counts align.

4. Query Execution Plan & Caching #

Salesforce’s query optimizer might choose different execution plans for the two queries. For example, one query could be using a cached result from a previous run (with slightly different filters) while the other runs a fresh scan. To rule this out, add a random comment to both queries (like /* test-run-1 */) to bypass caching, then re-run them.

Troubleshooting Next Steps #

• Use the EXPLAIN keyword (e.g., EXPLAIN SELECT Id FROM Account WHERE ...) to view the execution plan for each query. This will show you exactly which indexes are being used.
• Pull a sample of the extra records from the second query and check their LastModifiedDate and status (active/deleted).
• Verify your field-level security (FLS) and sharing settings to ensure consistent access to all fields you’re querying (though this usually reduces record counts, not increases them).

内容的提问来源于stack exchange，提问作者notAnExpert