Query Plan Tool (How To & FAQ)
|Knowledge Article Number||000199003|
The Query Plan tool is a new addition to the Developer Console since Summer '14. From the documentation:
"The new Query Plan tool in the Developer Console can help speed up SOQL queries done over large volumes.
Use the Query Plan tool to optimize and speed up queries done over large volumes. To enable the tool, click Help > Preferences and set Enable Query Plan to true. To use the tool, enter your query and click the Query Plan button in the Query Editor. The Query Plan window displays all query operations and the cost of each."
Leading Operation Type
The estimated number of records that the leading operation type would return.
For example, the number of records returned if using an index table.
|The indexed field(s) used by the Query Optimizer. If the leading operation type is Index, the fields value is Index. Otherwise, the fields value is null.||
The primary operation type that Salesforce will use to optimize the query.
|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.||
The approximate record count for the queried object.
The name of the queried.
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.
How is the Cost determined?
My indexed field is not showing up in the list of plans - why not?
- Custom index will never be used when comparisons are being done with an operator like "NOT EQUAL TO"
- Custom 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 and under the 10% threshold. . If you have a non-indexed field or one is above 10%, the plan will not be displayed.
Will this tool show me index candidates or fields that can be indexed?The Query Plan tool will only show Indexed field statistics in the Plan’s section, not fields that could be indexed. This tool will not provide information on fields that can be indexed. Please consult How to make my SOQL query selective on determining which fields can be indexed.
The following examples should help you understand how to interpret the results of the Query Plan:
Examples:In the following examples I will be using 2 indexed fields. A checkbox (InActiveAcc__c) and a Picklist (Account_Hierarchy__c) on the Account sObject.
Query 1: SELECT count() FROM Account WHERE Account_Hierarchy__c = 'Parent'
Scenario: An indexed field with a selective binding variable
As you can see, the “Account_Hierarchy__c” field is indexed so it was considered for a Plan with Leading Operation Type as “Index”. The indexed field has a lower cost than the TableScan, so the index will be used by the Query Optimizer for this query on the Account Object of 50,088 rows.
Query 2: SELECT count() FROM Account WHERE InActiveAcc__c = true AND Account_Hierarchy__c = 'Parent'
Scenario: 2 Indexed fields, 1 selective
What happens if more than one simple filter is selective?
Query 3: SELECT count() FROM Account WHERE InActiveAcc__c = true AND Account_Hierarchy__c != 'Parent' **NOTE: Using unsupported operation on index**
Scenario: 2 indexed fields, 1 selective BUT using an unsupported operation
The index on Account_Hierarchy__c is not used or considered due to the unsupported operation of “!=”. Also note that the InActiveAcc__c will be used but is higher cost than 1. As mentioned earlier, the filter is not selective but will be used.
Query 4: SELECT count() FROM Account WHERE Account_Hierarchy__c = 'Child'
Scenario: 1 indexed field using an non-selective binding variable (>10% of sObject's row count)
As you can see, the indexed field Account_Hierarchy__c is showing a much higher cost than a full table scan. The reason for this is that the Index is not selective with the binding variable "Child" resulting in more than 10% of the full table. The DB will execute a full table scan for this query, which, depending on the full table size, could result in poor performance.
You can find more example queries here at the original post on the Query Resource Feedback Parameter Pilot that this new Developer Console tool evolved from.