4

The following SOSL is in an Account trigger in a managed package:

/* Trigger is on API 27.0 */

String acctsNamesOrd = 'PARKER/VANGUARD MGT';
Map<Integer, String> acctsNames = new Map<Integer, String>{0 => 'PARKER/VANGUARD MGT'};
Map<Integer, String> acctsAddress = new Map<Integer, String>{0 => '15 COMMAND DR. #9'};
Map<Id, Account> acctsChanged = new Map<Id, Account>{'001J000001YAno5' => new Account(Id='001J000001YAno5')};

List<List<Sobject>> contactMatchSosl = [find :acctsNamesOrd in ALL FIELDS 
    returning Contact(Id, PKG__DBA__c, FirstName, LastName, MailingStreet, AccountId 
    where (PKG__DBA__c in :acctsNames.values() 
        or ((LastName in :acctsNames.values() or Name in :acctsNames.values()) and mailingStreet in :acctsAddress.values() and (AccountId in :acctsChanged.keySet() or Account.Name = 'Public'))) 
        and PKG__Confidential__c = false)];

When the package is installed in a client sandbox with 100K+ Contacts we get a QueryException BUT the exact same Search runs fine as part of a custom class: I created an Apex class (API 27.0) in client sandbox and copy/pasted the SOSL in there. Then called it up from Execute Anonymous. I have verified that all variables referenced in the packaged code are non-empty/non-null.

Not just that, the Search also runs fine in Query Editor (in client sandbox):

find {"PARKER/VANGUARD MGT"} in ALL FIELDS returning Contact(Id, PKG__DBA__c, FirstName, LastName, MailingStreet, AccountId where (PKG__DBA__c in ('PARKER/VANGUARD MGT') or ((LastName in ('PARKER/VANGUARD MGT') or Name in ('PARKER/VANGUARD MGT')) and mailingStreet in ('15 COMMAND DR. #9') and (AccountId in ('') or Account.Name = 'Public'))) and PKG__Confidential__c = false)

The Query Plan tool is showing these:

Cardinality | Fields | Leading Op Cost | sObject Cardinality | sObject Type
------------|--------|-----------------|---------------------|-------------
55206       |        | TableScan       | 118208              | Contact

Not considering filter for optimization because unindexed. Table: Contact Fields: ["IsDeleted"]
Not considering filter for optimization because query table is not optimizable. Table: Account Fields: ["IsDeleted"]
Not considering filter for optimization because unindexed. Table: Contact Fields: ["PKG__Confidential__c"]
Not considering ORed filter for optimization because unindexed. Table: Contact Fields: ["PKG__DBA__c"]

I am not understanding this discrepancy. What's going on?

UPDATE: I was able to repro the error using a custom trigger within the sandbox. As sfdcfox pointed out below running the code in Exec Anon is not the same thing.

  • Calling a query from a class from execute anonymous using an administrator is not the same as calling it from a trigger. You need to refactor your query so it runs with a leading Index cost instead of a table scan, or you'll run into that error only during trigger context. You may need to use either a SOQL instead, or remove some of the conditions from your initial SOSL and then perform a SOQL based on the results from the SOSL call. – sfdcfox Oct 29 '15 at 22:29
  • This SOSL actually used to be a SOQL -until 12 months ago when we ran into the very same issue. That was when I switched to SOSL and it worked -until now... I just tried running the query inside a custom trigger in the client sandbox and, you were right, I got the error. I don't understand how this is not working anymore. – Mossi Oct 29 '15 at 23:03
  • @sfdcfox Can you elaborate on why the execute anonymous admin vs trigger context makes a difference? – Keith C Oct 29 '15 at 23:18
  • See Working with Very Large SOQL Queries, but basically they give a hint: For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. -- You need to make sure that queries are optimized; the rule is in place to make sure that row lock times are minimized. – sfdcfox Oct 29 '15 at 23:33
  • I was able to identify the exact cause of the failure (with your help) BUT I have no idea how to do my Search. I can't split it into 2 Searches/Queries either; as long as PKG__DBA__c is in the filter I get the error. Remember that this is an OR Search, which means I can't loop through the results to narrow them down. Any ideas? – Mossi Oct 30 '15 at 18:45
1

The error was caused by using a non-indexed field in an OR condition. In this case removing PKG__DBA__c in :acctsNames.values() fixed the issue.

It's also worth noting that having the above condition as the only criterion in the where-clause causes the same error. It seems having a non-indexed field alone or ORed is not advised when querying large data sets.

Here are some condition that I have tried (IX means indexed, NIX means non-indexed):

/* NIX ORed with IX ANDed with NIX - Errors */
WHERE (NIX in :values OR IX in :values) AND NIX = value

/* NIX ORed with IX - Errors */
WHERE (NIX in :values OR IX = 'value')

/* NIX alone - Errors */
WHERE (NIX in :values)

/* NIX ANDed with IX (not equals) - Errors */
WHERE (NIX in :values AND IX != 'value')

/* NIX ANDed with IX (equals) - Works */
WHERE (NIX in :values AND IX = 'value')

Also beware of the following discrepancy between the Query/Search Optimization Cheatsheet and this documentation: LastModifiedDate is not indexed but CreatedDate is.

I still don't have a solution on how to make my Search work though.

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.