Query Plan Optimizer

SOQL query is designed to increase or decrease the operation times. The Force.com query optimizer helps the database system’s optimizer produce effective execution plans for Salesforce queries, and it is a major factor in providing efficient data access in Salesforce.

Query Plan can be used on any SOQL Query which is running slowly on Salesforce Platform or giving a time-out error. It will provide the cost of using the index compared to a full table scan. If the cost of SOQL is more than 1, then it’s more likely to provide a slow result.

Enable Query Plan Optimizer

1. Click on Setup and then Developer Console
2. Click Help from the top and select Preferences
3. Check Enable Query Plan checkbox

How to use Query Plan?

Once you enabled Query Plan, you will see Query Plan button in Developer consoler in Query Editor. When you write a soql query and click on Query Plan button, you will see the below screen:

• Cardinality: The estimated number of records returned by SOQL.
• Fields: The name of indexed field used by the query.
• Leading Operation Type: The primary operation type that Salesforce will use to optimize the query.
• Index: The query will use an index on the query object.
• Table Scan: The query will scan all records for the query object.
• Cost: The cost of the query compared to the Force.com query optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective.
• SObject Cardinality: Total number for records in the query object.
• Sobject Type: The name of the query object.

How Query plan calculates cost?

Each plan has its own cost value. The cost value is derived from the latest gathered database (DB) statistics on the table and values. The plan with the lowest cost will be the plan used. If the Cost is above 1, it means that the query won’t be selective. There are be,ow reasons for not showing index fields in Query plan

• index will never be used when comparisons are being done with an operator like “NOT EQUAL TO”
• index will never be used when comparisons are being done with a null value like “Name = ””
• Leading ‘%’ wildcards are inefficient operators that also make filter conditions non-selective
• When using an OR comparison, all filters must be indexed for optimized results.