使用查询见解提高性能

如果存在影响查询性能的条件，Snowflake 会提供有关这些条件的见解。每项见解都包含一条消息，其中解释了查询性能可能受到的影响，并为提高性能提供了一般性建议。

You can access these insights in Snowsight and by querying the QUERY_INSIGHTS view.

以下各节提供有关查询见解的详细信息：

• 见解类型列表
• Viewing the query insights in Snowsight
• 限制

见解类型列表

The Query Insights pane and The QUERY_INSIGHTS view provide the insights, which include:

• 关于检测到的条件及其可能对查询性能产生的影响的信息。
• 关于产生该条件的查询部分的详细信息。
• 如果该条件对性能产生负面影响，建议采取的下一步措施。

下表按类型 ID 列出了见解的类型。

表扫描时未使用筛选器

查询或子查询没有 WHERE 子句，这意味着该查询会扫描整个表，并且可能返回比预期更多的行。

为了提高性能，应添加 WHERE 子句以减少扫描的数据量。

筛选器不适用

WHERE 子句未筛选出任何行，这意味着查询可能扫描的数据量多于预期。

为了提高性能，应在 WHERE 子句中添加更具选择性的条件，或使现有条件更具选择性。

筛选器选择性低

WHERE 子句未显著减少行数，这意味着查询可能扫描的数据量多于预期。

Unlike the 筛选器不适用 insight, this insight indicates that the WHERE clause is filtering out some rows but it could have been more selective.

为了提高性能，应在 WHERE 子句中添加更具选择性的条件，或使现有条件更具选择性。

LIKE filter with leading wildcard¶

The query uses a LIKE filter that starts with a wildcard character. Specifying a pattern that starts with a wildcard can result in scanning a large amount of data.

To reduce the amount of data scanned, specify a pattern that does not start with a wildcard, if possible. If you need to specify a pattern that starts with a wildcard, consider enabling search optimization for more efficient pattern matching.

筛选器使用了群集密钥

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

查询受益于搜索优化

The query benefited from filtering on a column that is configured for search optimization.

Query benefited from Snowflake Optima¶

The query benefited from Snowflake Optima.

Query benefited from search optimization and Snowflake Optima¶

The query benefited from search optimization and Snowflake Optima.

无联接条件的联接

The join is missing the join condition. The result is a cross join, which returns every possible combination of rows.

为了减少该联接生成的行数，请指定一个或多个联接条件。

Join with inefficient join condition¶

The join contains a complex join condition that is evaluated after the data sets are joined. This is less efficient than if the condition were evaluated before the data sets were joined, which reduces the amount of data that the join must process.

To speed up this query, simplify the join condition.

爆炸性联接（嵌套联接）

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.

为了防止联接生成的行数超过参与联接的表的行数，请为子联接添加或更改联接条件。此外，在子联接中使用的子查询中添加 WHERE 子句可能会减少返回的行数。

爆炸性联接（非嵌套）

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.

为了防止联接生成的行数超过参与联接的表的行数，请添加或更改联接条件。此外，在此联接使用的子查询中添加 WHERE 子句可能会减少返回的行数。

Unnecessary aggregation¶

The DISTINCT or GROUP BY clause produces the same number of rows as the same statement without the DISTINCT or GROUP BY clause. Specifying the clause introduces an additional processing step that has no effect on the result.

To improve performance, remove the unnecessary DISTINCT or GROUP BY clause.

Unnecessary UNION [ DISTINCT ] clause¶

The UNION [ DISTINCT ] clause isn’t necessary because the input sets are disjoint.

To improve performance, use UNION ALL, rather than UNION [ DISTINCT ].

远程溢出

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.

为防止此问题，请使用容量更大的仓库。如果无法使用更大的仓库，请修改查询以分批处理数据。

Query was in the queue for the warehouse for too long¶

This query was waiting in the queue for the warehouse for too long.

To avoid this problem, use a larger warehouse that has more capacity, or use a warehouse that has fewer concurrent queries.

Viewing the query insights in Snowsight¶

In Query Profile tab under Query History, you can view the insights for a query. The nodes that have corresponding insights are highlighted.

The Query Insights pane on the right displays each type of insight that was detected for this query and lists each instance of that insight type that was detected for the query. To learn more about the condition that was detected, select View next to an entry in the Query Insights pane.

The details include the recommended next steps to take to improve the performance of the query. You can select Learn more to view more information about this insight.

限制

• 见解针对针对数据库执行并由仓库处理的 SQL 查询生成。
• Snowflake does not produce the “filter not selective” insight for queries that are accelerated by the query acceleration service.
• 以下情况不会生成见解：
  • Queries for which the query plan takes multiple steps to finish.
  • 涉及安全对象的查询。
  • 针对混合表 (Unistore) 执行的查询。
  • 由本地应用程序生成的查询。
  • EXPLAIN 查询。
  • Queries that reuse results.
  • Queries executing on interactive tables.