28Mar, 2023
Query Plan Tool

Enable and Use the Query Plan Tool

  1. In the Developer Console, click Help | Preferences
  2. Set ‘Enable Query Plan’ to TRUE.


Once enabled 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, simply enter a query in the Query Editor and press Query Plan to displays all query operations and the cost of each for the SOQL provided.

Reasons to use the Query Plan 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.
 

Determine if a filter is selective:

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

Indexed field does not appear in the list of plans

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.

Does the Query Plan Tool display 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.