11

I have a custom object with a Master-Detail relationship to Opportunity. The relationship field to the parent Opportunity is automatically indexed.

There are 200,000 plus of these custom objects in production, so the selectivity of the any SOQL query from the custom object is important.

A trigger on Opportunity needs to find all the related child records from the custom object. Because the query is in a trigger context in needs to be bulkified to handle up to 200 records at a time. E.g.

List<OppChild__c> children = [Select Id, Name, SomeField__c from OppChild__c where Opportunity__c in :triggerOpportunityIds];

If there is only a single Opportunity in the trigger the query is fine and the Query Plan from the developer console is using the Index for a cost of 0.00024.

enter image description here

However, if I have two Opportunities the cost raises to 0.00508.

By the time you reach a full 200 Opportunity ids the relative cost of the index scan exceeds that of a TableScan!

I ran a whole lot of query plans for various numbers of Ids for the IN clause and came up with the following.

le

Up until 20 records the relative cost of using the index is lowest. But from 20 records onward the TableScan has the lowest cost and the query fails.

Is it expected that the selectivity with an IN clause over an index would degrade so quickly?


I don't believe data skew is an issue here. Each Opportunity is expected to have exactly 3 child custom objects. So the SOQL query in the trigger is only expected to return 200 * 3 = 600 records. This should be well below the selectivity threshold of 10% for the first million records.


The cardinality for each query plan seems very high. Due to the data structure each Opportunity can only have up to 3 child custom objects. Yet both the Index and Tablescan are returning cardinalities that are 100 to 1000 times more records (rather than 3 times).

enter image description here

  • 1
    I think IN is just shorthand for a bunch of OR clauses... so I can see 200 OR clauses causing some probs – Caspar Harmer Jul 29 at 23:10
  • I'm currently wondering if I should be a bad person and burn up some SOQL queries to stay within the selectivity sweet zone. E.g. Make 10 queries for 20 records each... – Daniel Ballinger Jul 29 at 23:16
  • 2
    There is a lot of data skew involved here? For example, do you have opportunities with 1k+ children? This is the sort of scenario I'd expect to see in that case. We run massive queries with child records in batches of 200 and never see any selectivity errors against tables of millions. Or, you might need to talk to support and see if they can figure out what's going on. – sfdcfox Jul 29 at 23:52
  • @sfdcfox All opportunities can have a maximum of 3 child records via the custom object. So I think I'm well clear of any potential data skew issues. I'll raise a support case. Maybe something is messed up with how the query plans are stored? E.g. the estimated cardinality seems very high given that one 3 records exist per opp. – Daniel Ballinger Jul 30 at 0:04
  • @DanielBallinger Yeah, I think R&D/T3 is going to need to look at this. I've never seen this situation before, and it feels like a bug of some sort. Cardinality should be very low given 200 x 3 approximate records regardless of size. – sfdcfox Jul 30 at 0:22
3

Further analysis done. This is a child object with total cardinality of 3.7 million records which is being filtered based on parent field.

enter image description here

Below are the observations:

  1. The query cost is directly proportional to the number of records retrieved from database for that particular type of field (parent lookup field in my case).
  2. IN operator is virtually OR conditions in terms of cost. ParentId IN ('id1','id2') is same as ParentId='id1' OR ParentId='id2' and so the cost simply adds up.
  3. As observed above each record retrieving cost is 0.00000427332165921 which is constant for that parent field filter. It is always the multiple of this number to the total records being retrieved from database either by = or IN operator.

Now coming to your use case:

From your screenshot, enter image description here

Cost of each child record is 0.0000483956831 for given Parent Opportunity field AB2__Opportunity__c.

However, if I have two Opportunities the cost raises to 0.00508.

This makes sense as the number of child records for those 2 opportunities is 105 whereas the first opportunity has only 5 child records.

Considering this, I think it is better to check how many child records is being returned when you are using 200 opportunity ids. (It should be much more than 3 child records per opportunity)

  • Interesting. So your finding is that I'm getting a base line cost of 0.00000427332165921 per Opportunity ID present in the IN Clause and that is multiplied by the total number of child objects that will be returned for those opportunities. E.g. the first opp had 5 child records and the second had 100... – Daniel Ballinger Jul 30 at 9:46
  • What is really odd is that neither of those Opportunities actually have that many child records. They should only have 3 each. That goes for all the opportunities I'm querying. I'll do some more tests to verify that assumption tomorrow. Maybe there is something way off in the index that is messing up the cardinality. – Daniel Ballinger Jul 30 at 9:48
  • I've done a dumb thing. I'll post the clarification in a separate answer, but this answer guided me in the right direction and was very helpful, thanks. – Daniel Ballinger Jul 30 at 20:04
3

Firstly, the answer provided by salesforcesas is correct. It's the accumulation of cardinality records for each opportunity Id in the IN clause that is causing the issue.

I'd assumed that each Opportunity has 3 related child records that match the SOQL filter. And this was correct, but...

I hadn't provided the full story within the question. The actual SOQL query looks more like this:

select Id, Name, SomeField__c 
from OppChild__c 
where 
  Opportunity__c in :triggerOpportunityIds and ObjectType__c = 'Order'

It's that and ObjectType__c = 'Order' that is the killer here. When I run the actual SOQL query there are indeed 3 records per Opportunity. What I wasn't taking into account are the records where ObjectType__c != 'Order' and the lack of indexing for that field.

Missing Field Index

There can be hundreds or even thousands of records with an object type other than Order for each opportunity. Doh - Data Skew! Of course the cardinality count will include those records in the index on Opportunity ID alone. It even explains the odd increases in relative cost and cardinality I was seeing in the graphs I produced. There was a widely varying number of related records that depended on the Opportunity ID's being used.

To make matters worse, the ObjectType__c field is a formula to a related object field, so it isn't available for custom indexing.

I'll need to revisit this custom object so that the ObjectType__c can be included in the index.

  • but you are querying OppChild__c, not Opportunity. Doesn't ObjectType__c simply filter down the 3 OppChilds per Opportunity into at most 3 OppChilds? Or are you saying that the sum of the cardinalities forced it to use tablescan? – cropredy Jul 30 at 22:42
  • 1
    @cropredy Having OppChild__c.ObjectType__c in the SOQL where clause does filter down the actual results to just the 3 records per Opportunity. But, (and it's bold because it is a big BUT), that field is a formula to another related object and can't be indexed. Because it isn't indexed it isn't considered for the query plan optimization. So the query needs to look at all the related records for each Opp and then discard most of them. I can't filter out the data skew as the field isn't indexed. – Daniel Ballinger Jul 30 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.