We have a batch performing a query on a custom object that returns a timeout. The table contains 2.4 million records and the query should return 125 000 records. That's 5% of total records.
Our query looks like this (simplified SELECT clause):
SELECT Id FROM Custom_Object__c WHERE Status__c = 'Pending'
We only have one filter in the where clause called 'Status__c'. The Status__c field is a picklist with multiple picklist values. 'Pending' is an inactive picklist value. When the timeout occurred the Status__c field was not indexed. Salesforce proactively added an index on the Status__c field but that did not solve our problem. We can not make the query more selective.
We took this to Salesforce Support and they gave us two options:
- Add LIMIT 200 to make the query more selective which beats the purpose of having a batch to process thousands of records...
- Make the picklist value active because inactive picklist values are not indexed. Is this really how this works? An index is on the field and not on the values? Correct?
If you look at this link (https://help.salesforce.com/articleView?id=How-to-make-my-SOQL-query-selective&language=en_US&type=1) and check example #4 the treshold for custom indexes is 10% which is higher than what we have so in my opinion this should just work.
We did a test and in some cases the second try did give a result which tells us they use some kind of cache. Restarting the batch every time is of course not the behavior we want to advise our customers.
So how do we avoid these timeouts?