Non-Selective Query Error 'System.QueryException' in Salesforce Apex Trigger But Succeeds as Standalone Query

By design, non-selective queries on large object types are not allowed on trigger context (when evaluating Apex triggers). This design restriction prevents the impact a non-deterministic query execution time (due to a full/partial table scan required to perform the SOQL query) could have on the end-user's experience. As a result, attempts to run a non-selective query on a large object type within a trigger context will be prevented, and the‌ error message will be displayed.

NOTE: If this restriction wasn't in place, and the affected query was run within a given trigger that gets invoked upon saving a record, in a worst case scenario, the end user would need to wait for the query to time out to get some UI feedback.

The same restriction does not apply when the query is run within the Developer Console or via the SOAP/REST API. In this case, a standard query timeout will apply. In other words, the query will be attempted, and will be timed out only if it takes longer than a certain time.

It is suggested that the affected SOQL query be optimized so that it can leverage an index. See Salesforce query search optimization developer cheatsheet for more details on how to optimize a SOQL query.