Why are the ROQL queries involving Contacts table taking too long to execute or running into the error: “Poor performing query – too many rows examined”

Environment:

ROQL, Oracle B2C Service 19D and above.

Issue: 

After upgrading from an Oracle B2C Service Release 19C or below to an Oracle B2C Service Release 19D or above, the ROQL query I used no longer worked.  The query takes much longer or returns an “Poor performing query – too many rows examined”.  

Resolution:

Optimize the query with the following: 

• Include filters to reduce the data set returned.
• Examine if the queries use the right indices; if not, make sure to optimize the query by including the right indices.
• If the queries involve “OR” conditions in the WHERE clause, break out the OR clauses into multiple separate ROQL queries (each using a key), instead of one large combined query and then process the results in the code.

Cause: 

Beginning 19D release, the underlying contacts table is split into two tables: one holding the standard contact fields and the other holding the custom fields/attributes of contacts. While these underlying changes result in better scanned row estimates, it may result in queries being prohibited from executing that previously were incorrectly allowed to execute. Such queries might take longer to execute or run into max join size error and will need to be corrected.