Query Plan in Salesforce

Author - Webner
28.12.2020
|
0 Comments
||

The Query Plan tool within 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 within the Developer Console, you’ll access the Query Plan Tool within the ‘Query Editor’ tab of the console.
To use the Query Plan Tool, simply enter a question within the Query Editor and press Query plan to display all query operations and therefore the cost of every 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, providing 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.

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, subject 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 field value is Index. Otherwise, the field 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 the 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.

The 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.
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 use 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 is found to be selective, the query optimizer will choose the one with a 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 a 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 a 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.

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *