Loading

Developer Console Query Plan Tool FAQ

Publiceringsdatum: Aug 30, 2024
Beskrivning

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

 

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.

 
Lösning

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.
 

What does it all mean?

The Query Plan Tool will show a list of available plans that our Query Optimizer can use 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.

 

Cardinality
Fields
Leading Operation Type
Cost
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 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.
 

 

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.

 

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.

Note: OR filters that use indexes will appear as "Other" in the explain plan output.

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.

Please consult How to make my SOQL query selective on determining which fields can be indexed.

 

Examples and how to interpret Query Plan results 

The following examples uses 2 indexed fields. A checkbox (InActiveAcc__c) and a Picklist (Account_Hierarchy__c) on the Account sObject.

Note: You can find more example queries at the original post on the Query Resource Feedback Parameter Pilot that this new Developer Console tool evolved from.

 

Example Query 1

SELECT count() FROM Account WHERE Account_Hierarchy__c = 'Parent'


Scenario: An indexed field with a selective binding variable

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.

 

Example 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? 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.

 

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

 

Example 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)

Here, the indexed field Account_Hierarchy__c shows 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.
Knowledge-artikelnummer

000386864

 
Laddar
Salesforce Help | Article