Snowflake Optima

Snowflake Optima 通过对工作负载优化采用智能化方法，扩展了 Snowflake 核心的性能和简化原则。与需要手动调优不同，Snowflake Optima 会持续分析工作负载模式，并自动实施最有效的策略。Snowflake Optima 确保查询运行更快、成本更低，而无需额外配置或维护。通过预测并适应 SQL 工作负载的不断变化，Snowflake Optima 会自动提升性能。

Note

The following sections describe Snowflake Optima in more detail:

Optima Indexing

Optima Indexing is a Snowflake Optima feature that automatically analyzes workloads to create and maintain indexes in the background. Optima Indexing is built on top of the search optimization service.

By continuously monitoring SQL workloads, Optima Indexing identifies opportunities to improve performance — such as repetitive point-lookup queries on a table — and automatically generates hidden indexes to accelerate those workloads. These indexes are built and maintained on a best-effort basis, without requiring user intervention.

Optima Indexing 不产生额外费用，并且由于它与 Snowflake 完全集成，无需额外配置即可享受性能提升。

For specialized workloads that demand guaranteed performance — for example, threat detection in the cybersecurity industry — you can still directly apply search optimization. This option provides consistent index freshness and ultimately consistent performance for scenarios where near real-time results are critical.

Optima Metadata

Optima Metadata is a Snowflake Optima feature that automatically optimizes your workloads without any user input. Snowflake Optima analyzes your query patterns, identifies inefficient usage of columns in pruning, and creates additional metadata to optimize these queries. Even if you don’t know all the nuances of Snowflake’s query engine, Optima still ensures that you prune unused micro-partitions as effectively as possible.

For example, one of the scenarios that Snowflake Optima has optimized is usage of the UPPER and LOWER functions in the WHERE clause. These functions are inefficient in pruning. So, if Snowflake Optima observes frequent use of these functions in your query filter predicates, it automatically creates metadata to aid in pruning.

In general, the best practice is to avoid scenarios that lead to inefficient pruning. However, Snowflake Optima can improve performance when these scenarios occur. That is, you should continue to follow all existing query performance best practices and think of Optima Metadata as a feature that works in the background to catch optimizations you might have missed.

Monitor Snowflake Optima use

You can monitor Snowflake Optima use on the following panes in the Query Profile tab under Query History in Snowsight:

You can also monitor Snowflake Optima use by querying the QUERY_INSIGHTS view. For more information about query insights, see Using query insights to improve performance.

Query insights 窗格

The Query insights pane displays each type of insight detected for a query and lists each instance of that insight type.

  • To learn more about the condition that was detected, select View next to an entry in the Query insights pane.

If Snowflake Optima was used to optimize the given query, Snowflake Optima used appears and the details are displayed.

The following image shows an example of the Query insights pane that indicates that Snowflake Optima was used:

Shows the Query Insights pane on the Query Profile tab.

Statistics 窗格

To view pruning statistics for Snowflake Optima, open the Statistics pane on the Query Profile tab. Look for the row labeled Partitions pruned by Snowflake Optima. This row shows the number of partitions skipped during query execution, indicating how Snowflake Optima improved performance by reducing the amount of data scanned.

The following image shows an example of the Statistics pane that indicates that Snowflake Optima was used:

Shows the Statistics pane on the Query Profile tab.