Given the number of
Tasks you have, and that your query was working, your issue might be one of index skew.
I still think that I don't quite grasp the definition of index skew, and my org isn't large enough to regularly run into such issues, but my understanding is this:
- Index skew is when the value of an indexed field (like relationship fields such as
Opportunity) contains the same value on many more records than average. For example, in my org, a typical
Account may have around 10
Asset records related to it. There are a few
Accounts which have between 10,000 and 90,000
Asset records. Those exceptional
Accounts 'skew' the index
- Index skew can make it so that there isn't much benefit in Salesforce trying to optimize your query using the skewed index. If using that index results in the least costly query, Salesforce will still use it. However, if the computed 'cost' of that query is still over 1.0, your query won't be selective.
- Index skew might only be a problem when your query includes a skewed value. For example, if I query
[SELECT Id FROM Asset WHERE AccountId IN :acctsList], and
acctsList contains the Id of one (or more) of my exceptional
Accounts, I'm liable to get a selectivity exception
- Index skew may cause query selectivity issues even below the 200,000 record threshold mentioned by the error message
The fields with indices (that I know about) in your query are
With 28 million records,
RecordTypeId probably isn't too useful as an index, even if it isn't skewed, unless you have a ton of them (the absolute maximum number of rows a query could potentially return and still be selective is 1 million. If the distribution of record types were perfectly uniform, you'd need 28 or 29 separate record types to hit that target).
My guess is that
npsCaseMap contains an Id for a record that has an abnormal number of
Tasks. To figure out if that's the case, you might be able to run something similar to this query (via the query editor in the developer console, which appears to be more tolerant of large and non-selective queries)
SELECT WhatId, COUNT(Id)
GROUP BY WhatId
HAVING COUNT(Id) > 1000
ORDER BY COUNT(Id) DESC
1000 in the
HAVING clause as appropriate to narrow down your culprit(s).
As for working around the issue, if it's a 'once in a blue moon' type event, you may just want to handle things manually. Use the dev console to run the query, update the count yourself, then include some logic to only query
Cases that don't have their count set.
I don't really have an idea about how you might try to handle things automatically. If it is an index skew issue, removing one record from your query may fix things. Then again, it could be a combination of skewed index values causing you trouble. In both cases, the issue is that we don't really have the tools to determine which record(s) is/are causing us problems.
About the best advice I can give here is to echo something that Adrian said. Be sure to remove nulls from variables you're binding into your query (or prevent nulls from getting into them in the first place). In my org, there are 6600+
Tasks with no
WhatId. The next most referenced
WhatId value is referenced by 322
Tasks. It could very well be that a null value in
npsCaseMap.keySet() is causing your issues.