Back to portfolio

Salesforce LDV: Query Selectivity and Data Skew Explorer

A query that is instant in a sandbox can become the slowest thing in production, with no code change at all. The table grew, the filter stopped being selective, and a trigger that used to work now fails. Separately, a single parent record with too many children can serialize a parallel data load. Grow a fictional object and see both effects.

Fictional object and illustrative model. Selectivity uses a simplified form of the thresholds Salesforce documents for standard and custom indexes. The real optimizer weighs more factors, so confirm any real query with the query plan tooling in your own org. No timing or performance measurements are used or implied. The skew panel reports properties of the data, such as the share of batches that include the largest parent. It does not predict lock errors, which depend on timing and on what each transaction updates.

1. Query selectivity

Pick a filter, grow the table, and see when the optimizer stops using an index.

Query

From a sandbox-sized copy to a production-sized table.
How selective the filter is for your data.
The optimizer can use only the most selective indexed filter as its entry point.

Query plan, simplified

Selective row limit versus rows matched, as the table growsLog-log chart. Two lines show how many matching rows an index can handle selectively. A third line shows rows matched by filter A as the table grows.
Standard index limitCustom index limitRows matched by filter A

    2. Parent-record skew

    Load child records in parallel batches and see how many batches include the one oversized parent, and what grouping by parent would cost.

    Load design

    The documented skew warning starts above 10,000 children under one parent.
    All parents together.
    Each batch holds 200 child records.
    Spreading children across placeholder parents divides the skew.

    What the data looks like

    Ownership skew behaves similarly: when more than 10,000 records are owned by a single user, role or sharing changes can trigger heavy recalculation. The same remedies apply: spread ownership, or avoid a catch-all integration user as the owner of bulk data.

    Run it in your own org

    The model above teaches the idea. In a real org, the source of truth is the platform itself, and the audit is read-only. These are templates: adapt the object and field names, and try them in a sandbox first. They have not been run against any org for this page.

    1. Ask the platform whether a query is selective

    The REST Query resource accepts an explain parameter and returns the plans the optimizer considered. Read the leading operation type (an index or a table scan), the relative cost (above 1 means the query is not selective), the estimated cardinality and the object's total cardinality.

    GET /services/data/v{apiVersion}/query/?explain=
  SELECT Id FROM Case
  WHERE Status = 'Open' AND CreatedDate = LAST_N_DAYS:30

    2. Find parent-record skew

    Count children per parent and keep the parents above the documented warning level of 10,000.

    SELECT AccountId, COUNT(Id) children
FROM Contact
GROUP BY AccountId
HAVING COUNT(Id) > 10000
ORDER BY COUNT(Id) DESC
LIMIT 20

    3. Find ownership skew

    SELECT OwnerId, COUNT(Id) owned
FROM Case
GROUP BY OwnerId
HAVING COUNT(Id) > 10000
ORDER BY COUNT(Id) DESC
LIMIT 20

    Notes before you rely on the output

    Aggregate queries over very large objects can time out. In that case run the same grouping through a Bulk API query, or against a replica of the object in a warehouse using plain SQL. Confirm the current thresholds and limits in Salesforce documentation, because they can change between releases. Treat the result as an input to a design decision: spread the children, group the load by parent, or archive history.

    Architecture applied

    1. Design filters around selective indexes

    Choose entry-point filters that stay selective as the table grows, avoid negative operators and leading wildcards on large objects, and request custom indexes deliberately where the data distribution justifies them.

    2. Re-test as volume changes

    Selectivity is a property of data, not code. Review the query plan for critical triggers, flows and reports at production-sized volume, and revisit it on a schedule, not only at release.

    3. Keep the operational table small and parents balanced

    Archive history to a warehouse or archival store, spread children across parents, group bulk loads by parent, and avoid one catch-all owner. Smaller, balanced data keeps both queries and loads predictable.

    Impact

    Large-data-volume failures tend to arrive as surprises: a nightly job that began timing out, an integration that started failing on lock errors. Treating selectivity and skew as design properties moves those surprises into design review, where the fix is a filter, an index or a load plan instead of an incident.

    For revenue systems, the stakes are practical. Quote, order and billing automation often runs in synchronous contexts, so a query that quietly lost its index can slow or block the same quarter-end work the business depends on.