Non-selective query against large object type (more than 200000 rows)

Question

I have a Set called set_pvCorporativo with about 600 values. I want to bring exactly 600 accounts that have the pv_Corporativo value in set_pvCorporativo AND the Status 'Ativo' in a query like:

List<Account> list_Accounts = [SELECT Id, PV_Corporativo__c 
                            FROM Account 
                            WHERE Status__c = 'Ativo' 
                            AND PV_Corporativo__c IN :list_pvCorporativo];

But when I execute the query, I take the following error:

System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

I can't make this field unique, because there is more than one Account with the same PV_Corporativo, but I used the Status Ativo to filter the result. So with the status on the query, I have only one account for each value on the set. Can some one help me with this error?

Answer 1

I was going to write a long answer and then remembered this excellent article.

To simplify your work, I'll copy a a relevant paragraph from there, but you should read the entire thing

For best performance, SOQL queries must be selective, particularly for queries inside triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. Developers receive an error message when a non-selective query in a trigger executes against an object that contains more than 200,000 records. To avoid this error, ensure that the query is selective.

Selective SOQL Query Criteria

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

The selectivity threshold is 10% of the first million records and less than 5% of the records after the first million records, up to a maximum of 333,333 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold can be higher. Also, the selectivity threshold is subject to change.

You can ask SFDC to make the field PV_Corporativo__c a custom index. That will definitely help with the selectivity (assuming you're returning # of records under the threshold). Alternatively, try adding WHERE clauses that bring the number of records down under the selectivity threshold (though that depends on whether you can change your requirements to suit the query issues).

Other resource: here.

Answer 2

Sebastian kessel answer explains most of part, one thing that I want to add is using limit clause in query. E.g:- you want only limited number of accounts depending upon count of Pv_Corporativo. So you can add limit clause in your query to improve performance of soql query.

Q: We include an "ORDER BY Name" statement in an important SOQL statement that has performance issues at times. We could probably get away with not doing this. Is this practice good or bad or does it not impact much? A: ORDER BY without LIMIT clause is not going to be optimizable. However, ORDER BY + LIMIT on an indexed field (standard or custom) can actually be a very useful optimization (i.e. a page displaying the first 10 rows of a potentially large, 1000+, list of records).

Link:- https://help.salesforce.com/articleView?id=000181277&type=1&language=en_US