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 sections describe the types of insights by type ID.

Filter not applicable: QUERY_INSIGHT_INAPPLICABLE_FILTER_ON_TABLE_SCAN¶

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: QUERY_INSIGHT_UNSELECTIVE_FILTER¶

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: QUERY_INSIGHT_INAPPLICABLE_FILTER_ON_TABLE_SCAN 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: QUERY_INSIGHT_FILTER_WITH_CLUSTERING_KEY¶

The query benefited from filtering on a clustering key for the table.

Join with no join condition: QUERY_INSIGHT_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.

Remote spillage: QUERY_INSIGHT_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.

Language: English