Resolving Selective/Non-Selective Issue with Query Plan

OverView :
Query Plan tool is analytical tool which  helps in increasing query performance in the system.As a developer i ran into issue where i have faced "Selective /Non-Selective Exception"."Selective /Non-Selective Exception" which states how much data we are going to hit and  result(#No of records going to reterive).Salesforce Architecture is something which enforce optimum use of resources does'nt matter whether character in class/custom field or rollup field limitation.

Enable : Query Plan
              Open Developer Console --->Help--->Preferences--->Enable query Plan

@Bottom of developer console --> Option for query Plan.

Understanding Query Plan :

Cardinality

The estimated number of records that the leading operation type would return. For example, the number of records returned by an index.

Fields

 The indexed field(s) used by the query. 

 If query has Index field : 

    Fields : Indexed Field else null

LeadingOperationType

The primary operation type that Salesforce will use to optimize the query.

• Index - The query will use an index on the query object.
• Other - The query will use optimizations internal to Salesforce.
• Sharing - The query will use an index based on the sharing rules associated with the user who is executing the query. If there are sharing rules that limit which records that user can access, Salesforce can use those rules to optimize the query.
• TableScan - The query will scan all records for the query object.

Notes

An array of one or more feedback notes. Each note contains:

• description - A detailed description of an aspect of the optimization. This could include information on optimizations that could not be used, with details on why the optimization was not used.
• fields - An array of one or more fields used for the optimization.
• tableEnumOrId - The table name for the fields used for the optimization.

This response field is available in API version 33.0 and later.

RelativeCost : It is cost (Selectivity Threshold) of query as compared to or relative to force.com native query Optimizer

   If Cost  Value  >1 mean that the query won’t be selective.(Red Signal): May Hit exception 

  It must be <1 and preffered value is : 0.0n....(0.024 etc) n (Green Signal ) No Exception

SobjectCardinality:

The approximate record count for the query object.

Note: Using Index field in query may not always resolve "Selectivity /Non-Selectivity Issue".In some cases have to use 2nd index field check the cost .

Key Point :

Try And Clause with Custom Index avoid ORING In Query

Avoid !=(-ve Operator)

In Case :If You are using multiple OR Clause  in Query:Try to make  OR Condition  selective individually.

Ex:

OR (A) OR (B) OR (C)

A: Selective with use of index or by other mean

B: Selective with use of index or by other mean

C: Selective with use of index or by other mean

Note : Technical Resource

https://developer.salesforce.com/page/Developing_Selective_Force.com_Queries_through_the_Query_Resource_Feedback_Parameter_Pilot

https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_query_explain.htm