Second part of the Snowflake search optimization series is here! 🔥

This two-part series will guide you through everything you need about Snowflake search optimization. In the previous Part (Part 1), we discussed what Snowflake search optimization service is, how to implement it, and a hands-on example of a performance comparison between Optimized vs. Non-Optimized Tables. In this part (part 2), we have much to cover; we will dive into the benefits of a Snowflake search optimization, the types of queries that can benefit from it, how they are charged—and a lot more!

Let's get started!!

Types of Queries that can benefit from Snowflake search optimization

Snowflake search optimization can improve the performance of these kinds of queries:

1) Equality(“=”) or “IN” Predicates

Queries that use equality or IN predicates on columns with high cardinality can benefit from Snowflake Snowflake search optimization.

You can enable Snowflake search Snowflake search optimization service for all columns of a table that support lookup queries using the "=" or "IN" clauses.

To enable a Snowflake search Snowflake search optimization service for a table, you can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION;

This above ALTER TABLE command with the ADD SEARCH OPTIMIZATION clause will enable Snowflake search optimization service for a table.

Now, to check the list of columns for which Snowflake search optimization service is enabled, we can use the following command to do so:

DESCRIBE SEARCH OPTIMIZATION ON table_name;

2) Substrings and Regular Expressions

To enable Substring search optimization service for a particular column, we can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION ON SUBSTRING(column_name);

To enable Substring search optimization service for all columns in a table that support substring and regular expression predicates, we can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION ON SUBSTRING(*);

Make sure to check out the list of supported Substrings and Regular Expressions predicates.

To check the list of columns for which Substring search optimization service is enabled, we can use the same command as for Equality or IN Predicates:

DESCRIBE SEARCH OPTIMIZATION ON table_name;

3) Fields in VARIANT Columns

To improve the performance of point lookup queries on semi-structured data stored in Snowflake tables (i.e., data in VARIANT, OBJECT, and ARRAY columns), we can enable search optimization service for a matching data type column in a table.

To enable search optimization service for a variant, array, object data type column in a table, we can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION ON EQUALITY(column_name);

To check the list of columns for which VARIANT search optimization service is enabled, we can use the same command as for Equality or IN Predicates:

DESCRIBE SEARCH OPTIMIZATION ON table_name;

Check out the following link for additional information on the :

• Supported Predicates for VARIANT Types

4) Geospatial Functions

To enable GEO search optimization service for a particular column, we can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION ON GEO(column_name);

To enable GEO search optimization service for all columns in a table, we can use the following command:

ALTER TABLE table_name ADD SEARCH OPTIMIZATION ON GEO(*);

Check out the list of supported predicates with geospatial functions.

5) Conjunctions of Supported Predicates (AND)

To optimize search performance for queries that use conjunctions of predicates (i.e., AND), any predicate that returns only a few rows can benefit from search optimization.

For example, consider a query that contains:

where condition_x or condition_y

If either condition returns only a few rows (i.e., condition_x or condition_y), search optimization can enhance the query performance.

Furthermore, if condition_x returns a small number of rows but condition_y returns many rows, search optimization can still benefit the query performance.

To enable search optimization for conjunctions of supported predicates (AND), the query must contain multiple conditions joined by the AND operator. For example,

SELECT id, name, address FROM customers WHERE name='Pramit AND address='123'

6) Disjunctions of Supported Predicates (OR)

Consider a query that contains:

where condition_x or condition_y

If each condition returns only a few rows (i.e., condition_x and condition_y), search optimization can enhance the query performance.

However, if condition_x returns only a few rows but condition_y returns many rows, search optimization does not improve the query performance.

In the case of disjunctions, each predicate alone is not sufficient to determine the query's outcome. The other predicates must be evaluated to determine if search optimization can enhance performance.

To enable search optimization for disjunctions of supported predicates (OR), the query must contain multiple conditions joined by the OR operator. For example:

SELECT id, name, address FROM customers WHERE name='Pramit OR address='123'

Check out this official Snowflake documentation to learn everything you need to know about search optimization service.

How are Snowflake search optimization services charged?

Snowflake search optimization service is charged based on the amount of compute and storage resources used.

The overall cost of Search optimization is included in the overall cost of running Snowflake workloads.

Compute and Storage Costs

Enabling Snowflake search optimization requires additional compute and storage resources. These resources are used to maintain the search indexes and optimize the search access path.

The compute and storage costs associated with Snowflake search optimization service are based on the number and size of columns enabled for the Snowflake search optimization service.

What are the factors Affecting the Cost of Snowflake search optimization service?

Several factors can affect the cost of the Snowflake search optimization service.

These include:

• Length and width of the table: number of rows and columns
• Number of distinct values for each column: increases the storage used. Can be as much as the table's size if all values are unique.
• Churn: number of inserts, updates, and deletes on the table. Increases compute the cost for maintaining the search access paths

Ways to keep the Snowflake costs under control:

• Only enable the search optimization service on tables that will benefit from it. Not all tables need Snowflake search optimization service enabled.
• Use Snowflake search optimization service on tables with large amounts of data and high numbers of micro-partitions.
• Either group/combine INSERT, UPDATE, DELETE and MERGE operations on a table into fewer, larger transactions instead of multiple smaller transactions. By doing so, the background maintenance process needed to maintain the search optimization service will be run less frequently, which can significantly help reduce the overall Snowflake cost.
• If the table is not clustered, consider dropping the Snowflake search optimization service from the table first, then re-adding the table/columns once you recluster the table.
• Monitor your usage of Snowflake search optimization service carefully and adjust as needed. Use the Snowflake Account Usage to carefully monitor your usage and make adjustments as necessary to keep the costs under control.

Conclusion

In the first installment of this two-part article on Snowflake search optimization, we delved into the service's practical applications and step-by-step implementation. In this follow-up (Part 2), we examine the finer details of Snowflake search optimization. Here's a recap of the key topics we covered:

• Types of queries that can benefit from Snowflake search optimization
• Cost structure of Snowflake search optimization services
• Factors that impact the cost of Snowflake search optimization
• Best practices for optimizing Snowflake search and managing associated costs

Remember: With great power comes great responsibility, and it's up to you to use Snowflake search optimization in a cost-effective manner that maximizes its potential without breaking the bank!

FAQs

How do I enable Snowflake search optimization for a table?

Use the command: ALTER TABLE table_name ADD SEARCH OPTIMIZATION.

How can I check which columns have search optimization enabled?

Use the command: DESCRIBE SEARCH OPTIMIZATION ON table_name.

What are the disadvantages of Snowflake Search Optimization?

Snowflake Search Optimization may not provide significant benefits for queries that return a large number of rows or have less selective filters.

Will enabling both Auto Clustering and Search Optimization consume more credits?

Yes, tables with both Auto Clustering and Search Optimization enabled will consume more credits.