Using query insights to improve performance¶
If conditions exist that affect query performance, Snowflake provides insights about these conditions. Each insight includes a message that explains how query performance might be affected and provides a general recommendation for improving performance.
You can access these insights by querying the QUERY_INSIGHTS view.
The next sections provide details about query insights:
List of insight types¶
The QUERY_INSIGHTS view provides the insights, which includes:
A message about the condition detected and how it can affect query performance.
Details about the part of the query that produced the condition.
A suggested next step to address the condition, if the condition negatively affects performance.
The following table lists the types of insights by type ID.
Type ID |
Insight |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
No filter on table scan¶
A query or subquery has no WHERE clause, which means that the query scans an entire table and might return more rows than intended.
To improve performance, add a WHERE clause to reduce the amount of data scanned.
Filter not applicable¶
A WHERE clause doesn’t filter out any rows, which means that the query might scan more data than intended.
To improve performance, add a more selective condition to the WHERE clause, or make the existing condition more selective.
Filter not selective¶
A WHERE clause doesn’t significantly reduce the number of rows, which means that the query might scan more data than intended.
Unlike the Filter not applicable insight, this insight indicates that the WHERE clause is filtering out some rows but it could have been more selective.
To improve performance, add a more selective condition to the WHERE clause, or make the existing condition more selective.
Filter uses clustering key¶
The query benefited from filtering on a clustering key for the table.
Query benefited from search optimization¶
The query benefited from filtering on a column that is configured for search optimization.
Join with no join condition¶
The join is missing the join condition. The result is a cross join, which returns every possible combination of rows.
To reduce the row count produced by this join, specify one or more join conditions.
Exploding join (nested join)¶
A join that includes the output of at least one other join is returning many more rows than are in the tables being joined. This might indicate a problem with the join conditions for the child joins.
To prevent the join from producing more rows than the joined tables contain, add or change the join conditions for the child joins. In addition, adding a WHERE clause to a subquery used in a child join might reduce the number of rows returned.
Exploding join (not nested)¶
A join of two data sets (for example, tables, views, or output from table function calls) is returning many more rows than the joined tables contain. This might indicate a problem with the join condition.
To prevent the join from producing more rows than are in the tables being joined, add or change the join condition. In addition, adding a WHERE clause to a subquery used by this join might reduce the number of rows returned.
Remote spillage¶
This query scanned more data than the warehouse had capacity to store. As a result, the warehouse spilled data to storage, which slowed down the processing of the query.
To prevent this problem, use a larger warehouse that has more capacity. If using a larger warehouse is not an option, change the query to process data in smaller batches.
Limitations¶
Insights are produced for SQL queries that are made against databases and are processed by warehouses.
Insights are not produced for:
Queries involving secure objects.
Queries executed against hybrid tables (Unistore).
Queries generated by Native Apps.
EXPLAIN queries.
Queries that reuse results.