Print this page

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."

How to enable the Query Plan Tool in the Developer Console?

To enable the tool go to:
  • Developer Console>>Help > >Preferences
and set "Enable Query Plan" to true.


Why use this tool?

Use this tool to check the Query Plan for any SOQL queries that execute slowly. It will provide you with insight on the different plans and should you have some of the filters indexed, provide the cost of using the index compared to a full table scan. If the cost for the table scan is lower than the index, and the query is timing out, you will need to perform further analysis on using other filters to improve selectivity, or, if you have another selective filter in that query that is not indexed but is a candidate for one.
Note: To determine if a filter is selective, please follow these steps:

Determine if it has an index.
If the filter is on a standard field, it'll have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate,   SystemModstamp).
Custom fields will have an index if they have been marked as Unique or External Id
If the filter doesn't have an index, it won't be considered for optimization.
If the filter has an index, determine how many records it would return:
For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1   million total targeted records, which you could reach only if you had more than 5.6 million total records.
For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million.  In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.
If the filter exceeds the threshold,it won't be considered for optimization.
If the filter doesn't exceed the threshold, this filter IS selective, and the query optimizer will consider it for optimization.

Where can I access the Query Plan Tool in the Dev Console?

In the Developer Console, you can access the Query Plan tool in the Query Editor tab of the console. To use the Query Plan tool you simply enter a query in the Query Editor and press the "Query Plan" button which returns a Query Plan for the SOQL provided.
Query Plan Tool button


What does it all mean?

The Query Plan tool will show a list of available plans that our Query Optimizer can utilize for the query provided and will be arranged by cost ascending. Each Plan will contain information on Cardinality, Operation Type, Cost, sObject Type, and more. Each plan has a “Leading Operation Type”, for example, Field Index or Full Table Scan. The plan with the lowest cost is the plan that is used for driving the query execution.
Leading Operation Type
sObject Cardinality
sObject 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.
  • Index - The query will use an index on the queried object.
  • 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 queried object.
  • Other - The query will use optimizations internal to Salesforce.
The cost of the query compared to the 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.


How is the Cost determined?

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.


My indexed field is not showing up in the list of plans - why not?

If the query you provided contains an Indexed field in the filters, the plan will be shown for that field only if you are using a supported operation against that field. Here is a list of unsupported operations:
  • 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:


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

Filter is indexed and is used

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

Two or more filter indexed

What happens if more than one simple filter is selective?
If more than one filter are found to be selective, the query optimizer will choose the one with lower cost to drive the execution plan of the query.

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

Unsupported Operations for indexing

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)

Field is indexed but not selective

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.

References and Recommended Reading:

This feature was originally a Pilot feature (Query Resource Feedback Parameter pilot) which you can find more information about, covered in detail here.

promote demote