Developer Console Query Plan
Tool FAQ
Publish Date: Aug 30, 2024
Description
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.
Resolution
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 Cost sObject sObject
Type Cardinalit Type
y
The The indexed The primary The cost of The The
estimated field(s) used operation type that the query approxi name
number of by the Query Salesforce will use to compared to mate of the
records Optimizer. If optimize the query. the record queried
that the the leading [Link] count for .
● Index -
leading operation Query the
The
operation type is Index, Optimizer’s queried
query
type the fields selectivity object.
will use
would value is threshold.
an index
return. Index. Values
on the
Otherwise, above 1
For queried
the fields mean that
example, object.
value is null. the query
the ● Sharing
won’t be
number of - The
selective.
records query
returned if will use
using an an index
index based
table. on the
sharing
rules
associat
ed with
the user
who is
executin
g the
query. If
there are
sharing
rules
that limit
which
records
that user
can
access,
Salesfor
ce can
use
those
rules to
optimize
the
query.
● TableSc
an - The
query
will scan
all
records
for the
queried
object.
● Other -
The
query
will use
optimizat
ions
internal
to
Salesfor
ce.
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.
References and Recommended Reading
● How to make my SOQL query selective
● [Link] Query Optimizer FAQ
● Collecting Selectivity Statistics for [Link] Queries
Knowledge Article Number
Developer Console Query Plan
Tool FAQ
Publish Date: Aug 30, 2024
Description
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.
Resolution
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 Cost sObject sObject
Type Cardinalit Type
y
The The indexed The primary The cost of The The
estimated field(s) used operation type that the query approxi name
number of by the Query Salesforce will use to compared to mate of the
records Optimizer. If optimize the query. the record queried
that the the leading ● Index - [Link] count for .
leading operation The Query the
operation type is Index, query Optimizer’s queried
type the fields will use selectivity object.
would value is an index threshold.
return. Index. on the Values
Otherwise, queried above 1
For
the fields object. mean that
example,
value is null. ● Sharing the query
the
- The won’t be
number of
query selective.
records
will use
returned if
an index
using an
based
index
on the
table.
sharing
rules
associat
ed with
the user
who is
executin
g the
query. If
there are
sharing
rules
that limit
which
records
that user
can
access,
Salesfor
ce can
use
those
rules to
optimize
the
query.
● TableSc
an - The
query
will scan
all
records
for the
queried
object.
● Other -
The
query
will use
optimizat
ions
internal
to
Salesfor
ce.
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.
References and Recommended Reading
● How to make my SOQL query selective
● [Link] Query Optimizer FAQ
● Collecting Selectivity Statistics for [Link] Queries
Knowledge Article Number