Using the Search Optimization Service¶

How Does the Search Optimization Service Work?¶

To improve performance for point lookups, the search optimization service relies on a persistent data structure that serves as an optimized search access path.

A maintenance service that runs in the background is responsible for creating and maintaining the search access path:

• When you configure search optimization for a table, the maintenance service creates and populates the search access path with the data needed to perform the lookups.

  The process of populating data can take time, depending on the size of the table. The service does this work in the background and does not block any concurrent operations on the table.

• When data in the table is updated (for example, by loading new data sets or through DML operations), the maintenance service automatically updates the search access path to reflect the changes to the data.

  If queries are run when the search access path hasn’t been updated yet, the queries might run slower but will always return up-to-date results.

This search access path and the maintenance service are transparent to the user. You don’t need to create a warehouse for the service that maintains the search access path.

However, note that there is a cost for the storage and compute resources for this service. For more details, see Managing the Costs of the Search Optimization Service (in this topic).

Considering Other Solutions for Optimizing Query Performance¶

The search optimization service is one of several ways to optimize query performance. Related techniques include:

• Clustering a table.

• Creating one or more materialized views (clustered or unclustered).

Each of these has different advantages, as shown in the following table:

The following table shows which of these three optimizations have storage or compute costs:

1

The process of reclustering can increase the size of Fail-safe storage due to the rewriting of existing partitions into new partitions. (Note that this process does not introduce any new rows. This just reorganizes existing rows.) For details, see Credit and Storage Impact of Reclustering.

What Access Control Privileges Are Needed For the Search Optimization Service?¶

To add, configure, or remove search optimization for a table, you must have the following privileges:

• You must have OWNERSHIP privilege on the table.

• You must have ADD SEARCH OPTIMIZATION privilege on the schema that contains the table.

  GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>
  

  Copy

To use the search optimization service for a query, you just need the SELECT privilege on the table.

You do not need any additional privileges. Because SEARCH OPTIMIZATION is a table property, it is automatically detected and used (if appropriate) when querying a table.

Equality or IN Predicates¶

The search optimization service can improve the performance of queries that use:

• Equality predicates (for example, <column_name> = <constant>).

• Predicates that use IN (see example).

Substrings and Regular Expressions¶

The search optimization service can improve the performance of queries with predicates that search for substrings or use regular expressions. This includes predicates that use:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART (in equality predicates)

• RLIKE

• REGEXP

• REGEXP_LIKE

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);

The search optimization service can improve performance when searching for substrings that are 5 or more characters long. (More selective substrings can result in better performance.)

In contrast, the search optimization service does not use search access paths for the following predicate because the substring is shorter than 5 characters:

LIKE '%TEST%'

Copy

For the following predicate, the search optimization service can optimize this query, using search access paths to search for the substrings for SEARCH and OPTIMIZED. However, search access paths are not used for IS because the substring is shorter than 5 characters.

LIKE '%SEARCH%IS%OPTIMIZED%'

Copy

For queries that use RLIKE, REGEXP, and REGEXP_LIKE:

• The subject argument must be a TEXT column in a table that has search optimization enabled.

• The pattern argument must be a string constant.

For regular expressions, the search optimization service works best when:

• The pattern contains at least one substring literal that is 5 or more characters long.

• The pattern specifies that the substring should appear at least once.

For example, the following pattern specifies that string should appear one or more times in the subject:

RLIKE '(string)+'

Copy

The search optimization service can improve the performance of queries with the following patterns because each predicate specifies that a substring of 5 or more characters must appear at least once. (Note that the first example uses a dollar-quoted string constant to avoid escaping the backslash characters.)

RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$

Copy

RLIKE '.*country=(Germany|France|Spain).*'

Copy

RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'

Copy

In contrast, the search optimization service does not use search access paths for queries with the following patterns:

• Patterns without any substrings:

  RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
  

  Copy

• Patterns that only contain substrings shorter than 5 characters:

  RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
  

  Copy

• Patterns that use the alternation operator where one option is a substring shorter than 5 characters:

  RLIKE '.*(option1|option2|opt3).*'
  

  Copy

• Patterns in which the substring is optional:

  RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
  

  Copy

Even when the substring literals are shorter than 5 characters, the search optimization service can still improve query performance if expanding the regular expression produces a substring literal that is 5 characters or longer.

For example, consider the pattern:

.*st=(CA|AZ|NV).*(-->){2,4}.*

Copy

In this example:

• Although the substring literals (e.g. st=, CA, etc) are shorter than 5 characters, the search optimization service recognizes that the substring st=CA, st=AZ, or st=NV (each of which is 5 characters long) must appear in the text.

• Similarly, even though the substring literal --> is shorter than 5 characters, the search optimization service determines that the substring -->--> (which is longer than 5 characters) must appear in the text.

The search optimization service can use search access paths to match these substrings, which can improve the performance of the query.

Fields in VARIANT Columns¶

The search optimization service can improve the performance of point lookup queries on semi-structured data in Snowflake tables (data in VARIANT, OBJECT, and ARRAY columns).

When VARIANT support for the search optimization service is configured for columns in a table, the search optimization service automatically includes VARIANT, OBJECT, and ARRAY columns in a search access path. This even applies to columns where the structure is deeply nested and the structure changes frequently.

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user:uuid);

The next sections provide more details about this support:

• Supported Data Types for Constants and Casts in Predicates for VARIANT Types

• Support for VARIANT Values Cast as TEXT

• Supported Predicates for VARIANT Types

• Current Limitations in Support for VARIANT Types

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME

select * from test_table where src::TEXT = 'true';
select * from test_table where src::TEXT = '2020-01-09';
select * from test_table where src::TEXT = '01:02:03.899213';

Geospatial Functions¶

The search optimization service can improve the performance of queries with predicates that use geospatial functions with GEOGRAPHY objects.

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);

The following sections provide more details:

• Supported Predicates With Geospatial Functions

• Other Performance Considerations

• Examples That Use Geospatial Functions

CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
  (1, 'POINT(-122.35 37.55)'),
  (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
  (3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);

SELECT id FROM geospatial_table WHERE
  ST_INTERSECTS(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  ST_CONTAINS(
    TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)

...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))

...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)

...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)

...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))

...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)

...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))

...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)

...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  ST_CONTAINS(
    ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)

...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)

...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))

...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))

...
  ST_INTERSECTS(
    ST_POLYGON(
      TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
    g1)

...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))

...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))

Conjunctions of Supported Predicates (AND)¶

For queries that use conjunctions of predicates (i.e., AND), query performance can be improved by search optimization if any of the predicates adhere to the conditions above.

For example, suppose that a query has:

where condition_x and condition_y

Search optimization can improve performance if either condition separately returns a few rows (i.e., condition_x returns a few rows or condition_y returns a few rows).

If condition_x returns a few rows but condition_y returns many rows, the query performance can benefit from search optimization.

See these additional examples.

Disjunctions of Supported Predicates (OR)¶

For queries that use disjunctions of predicates (i.e., OR), query performance can be improved by search optimization if all predicates adhere to the conditions above.

For example, suppose that a query has:

where condition_x or condition_y

Search optimization can improve performance if each condition separately returns a few rows (i.e., condition_x returns a few rows and condition_y returns a few rows).

If condition_x returns a few rows but condition_y returns many rows, the query performance does not benefit from search optimization.

In the case of disjunctions, each predicate in isolation is not decisive in the query. The other predicates need to be evaluated before it can be determined if search optimization can improve performance.

Joins¶

The search optimization service does not directly improve the performance of joins. However, it can improve the performance of filtering rows from either table prior to the join, if the table has search optimization enabled and the predicate is selective.

Both tables do not need to have search optimization enabled. The decision to use search optimization is made for each table independently.

Views¶

The search optimization service can indirectly improve the performance of views (including secure views). If a base table for a view has search optimization enabled and if the query uses a selective predicate for that table, the search optimization service can improve performance when filtering rows.

All tables in the view do not need to have search optimization enabled. Search optimization is performed on each table independently.

Tables with Masking Policies and Row Access Policies¶

The search optimization service can improve query performance for tables that use masking policies and row access policies.

Queries Not Supported By the Search Optimization Service¶

The search optimization service does not support the following:

• External tables.

• Materialized views.

• Columns defined with a COLLATE clause.

• Column concatenation.

• Analytical expressions.

• Casts on table columns (except for fixed-point numbers cast to strings).

  Although search optimization supports predicates with implicit and explicit casts on constant values, it does not support predicates that cast values in the actual table column (except for casts from INTEGER and NUMBER to VARCHAR).

  For example, the following predicates are supported because they use implicit and explicit casts on constant values (not values in the table column):

  -- Supported predicate
  -- (where the string '2020-01-01' is implicitly cast to a date)
  WHERE timestamp1 = '2020-01-01';
  
  
  -- Supported predicate
  -- (where the string '2020-01-01' is explicitly cast to a date)
  WHERE timestamp1 = '2020-01-01'::date;
  

  Copy

  The following predicate is not supported because it uses a cast on values in the table column:

  -- Unsupported predicate
  -- (where values in a VARCHAR column are cast to DATE)
  WHERE to_date(varchar_column) = '2020-01-01';
  

  Copy

  The search optimization service considers the original column values, not the values after the cast. As a result, the search optimization service is not used for queries with these predicates.

As mentioned earlier, the exception to this rule is casting NUMBER or INTEGER values to VARCHAR values in the table column. The search optimization service does support this type of predicate:

-- Supported predicate
-- (where values in a numeric column are cast to a string)
WHERE cast(numeric_column as varchar) = '2'

Copy

Search optimization does not improve performance of queries that use Time Travel because search optimization works only on active data.

The query acceleration service does not accelerate queries on tables that have search optimization service enabled.

Configuring Search Optimization for Specific Columns¶

To configure search optimization for a specific column, use the ALTER TABLE … ADD SEARCH OPTIMIZATION command with the ON clause.

The ON clause specifies that you want to configure search optimization for specific columns. For details on the syntax, see the section on ALTER TABLE … ADD SEARCH OPTIMIZATION.

After running this command, you can verify that the columns have been configured for search optimization.

The next sections contain examples that demonstrate how to specify the configuration for search optimization:

• Example: Supporting Equality and IN Predicates for Specific Columns

• Example: Supporting Equality and IN Predicates for All Applicable Columns

• Example: Supporting Different Types of Predicates

• Example: Supporting Equality and IN Predicates for a Field in a VARIANT

• Example: Supporting Geospatial Functions

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);

-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2), SUBSTRING(c3);

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user:uuid);

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON GEO(c1);

Adding and Maintaining Search Optimization for the Entire Table¶

If you just want to specify EQUALITY for all columns of the supported data types (except for VARIANT and GEOGRAPHY), use the ALTER TABLE … ADD SEARCH OPTIMIZATION command without the ON clause.

For example:

alter table test_table add search optimization;

For more information on the syntax, see the section on search optimization in ALTER TABLE.

After running this command, you can verify that the columns have been configured for search optimization.

Verifying That the Table Is Configured For Search Optimization¶

To verify that the table and its columns have been configured for search optimization:

1. Display the search optimization configuration for the table and its columns.

2. Run the SHOW TABLES command to verify that search optimization has been added and to determine how much of the table has been optimized.

   For example:

   SHOW TABLES LIKE '%test_table%';
   

   Copy

   In the output from this command:

   • Verify that SEARCH_OPTIMIZATION is ON, which indicates that search optimization has been added.

   • Check the value of SEARCH_OPTIMIZATION_PROGRESS. This specifies the percentage of the table that has been optimized so far.

     When search optimization is first added to a table, the performance benefits do not appear immediately. The search optimization service starts populating data in the background. The benefits appear increasingly as the maintenance catches up to the current state of the table.

     Before you run a query to verify that search optimization is working, wait until this shows that the table has been fully optimized.

3. Run a query to verify that search optimization is working.

   Note that the Snowflake optimizer automatically chooses when to use the search optimization service for a particular query. Users cannot control which queries search optimization is used for.

   Choose a query that the search optimization service is designed to optimize. See Identifying the Tables and Columns That Benefit From Search Optimization.

4. In the web UI, view the query plan for this query, and verify that the query node “Search Optimization Access” is part of the query plan.

Modifying the Table¶

A search access path becomes invalid if the default value of a column is changed.

To use search optimization again after a search access path has become invalid, you must drop the SEARCH OPTIMIZATION property and add the SEARCH OPTIMIZATION property back to the table.

A search access path remains valid if you add, drop, or rename a column:

• If you enabled search optimization for an entire table without specifying specific columns, then when you add a column to a table, the new column is added to the search access path automatically. However, if you used the ON clause when enabling search optimization for a column, new columns are not added automically.

• When you drop a column from a table, the dropped column is removed from the search access path automatically.

• Renaming a column doesn’t require any changes to the search access path.

If you drop a table, the SEARCH OPTIMIZATION property and search access paths are also dropped. Note that:

• Undropping the table immediately reestablishes search optimization as a property of the table.

• When you drop a table, the search access path has the same data retention period as the table.

If you drop the SEARCH OPTIMIZATION property from the table, the search access path is removed. When you add the SEARCH OPTIMIZATION property back to the table, the maintenance service needs to recreate the search access path. (There is no way to undrop the property.)

Cloning the Table, Schema, or Database¶

If you clone a table, schema, or database, the SEARCH OPTIMIZATION property and search access paths of each table are also cloned. (Cloning a table, schema, or database creates a zero-copy clone of each table and its corresponding search access paths.)

Note that if you use CREATE TABLE … LIKE to create a new empty table with the same columns as the original table, the SEARCH OPTIMIZATION property is not copied to the new table.

Working With Tables in a Secondary Database (Database Replication Support)¶

If a table in the primary database has the SEARCH OPTIMIZATION property enabled, the property is replicated to the corresponding table in the secondary database.

Search access paths in the secondary database are not replicated but are instead rebuilt automatically. Note that this process incurs the same kinds of costs described in Managing the Costs of the Search Optimization Service.

Sharing the Table¶

Data providers can use Secure Data Sharing to share tables that have search optimization enabled.

When querying shared tables, data consumers can benefit from any performance improvements made by the search optimization service.

Estimating the Costs¶

To estimate the cost of adding search optimization to a table and configuring specific columns for search optimization, use the SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS function.

In general, the costs are proportional to:

• The number of tables on which the feature is enabled, and the number of distinct values in those tables.

• The amount of data that changes in these tables.

Viewing the Costs¶

You can view the billing costs for the search optimization service by using either the web interface or SQL. See Exploring Compute Cost.

Reducing the Costs¶

You can control the cost of the search optimization service by carefully choosing the tables and columns for which to enable search optimization.

In addition, to reduce the cost of the search optimization service:

• Snowflake recommends batching DML operations on the table:

  • DELETE: If tables store data for the most recent time period (e.g. the most recent day or week or month), then when you trim your table by deleting old data, the search optimization service must take into account the updates. In some cases, you might be able to reduce costs by deleting less frequently (e.g. daily rather than hourly).

  • INSERT, UPDATE, and MERGE: Batching these types of DML statements on the table can reduce the cost of maintenance by the search optimization service.

• If you recluster the entire table, consider dropping the SEARCH OPTIMIZATION property for that table before reclustering, and then add the SEARCH OPTIMIZATION property back to the table after reclustering.

• Before enabling search optimization for substring searches (ON SUBSTRING(col)) or VARIANTs (ON EQUALITY(variant_col)), call SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS to estimate the costs. The initial build and maintenance for these search methods can be computationally intensive, so you should assess the trade-off between performance and cost.

Dropping Search Optimization for Specific Columns¶

To drop the search optimization configuration for specific columns, use the following command: ALTER TABLE … DROP SEARCH OPTIMIZATION command with the ON clause.

For example, suppose that executing the DESCRIBE SEARCH OPTIMIZATION command prints the following expressions:

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+-----------+-----------+-------------------+--------+
| expression_id |  method   | target    | target_data_type  | active |
+---------------+-----------+-----------+-------------------+--------+
|             1 | EQUALITY  | C1        | NUMBER(38,0)      | true   |
|             2 | EQUALITY  | C2        | VARCHAR(16777216) | true   |
|             3 | EQUALITY  | C4        | NUMBER(38,0)      | true   |
|             4 | EQUALITY  | C5        | VARCHAR(16777216) | true   |
|             5 | EQUALITY  | V1        | VARIANT           | true   |
|             6 | SUBSTRING | C2        | VARCHAR(16777216) | true   |
|             7 | SUBSTRING | C5        | VARCHAR(16777216) | true   |
|             8 | GEO       | G1        | GEOGRAPHY         | true   |
|             9 | EQUALITY  | V1:"key1" | VARIANT           | true   |
|            10 | EQUALITY  | V1:"key2" | VARIANT           | true   |
+---------------+-----------+-----------+-------------------+--------+

To drop search optimization for substrings on the column c2, execute the following statement:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);

To drop search optimization for all methods on the column c5, execute the following statement:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;

Because the column c5 is configured to optimize equality and substring searches, the statement above drops the configuration for equality and substring searches for c5.

To drop search optimization for equality on the column c1 and to drop the configuration specified by the expression IDs 6 and 8, execute the following statement:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;

For more information on the syntax, see the section on ALTER TABLE … DROP SEARCH OPTIMIZATION.

Removing Search Optimization From the Table¶

To remove the SEARCH OPTIMIZATION property from a table:

1. Switch to a role that has the privileges to remove search optimization from the table.

2. Run the ALTER TABLE … DROP SEARCH OPTIMIZATION command without the ON clause:

   ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
   

   Copy

   For example:

   alter table test_table drop search optimization;
   

   Copy

   For more information, see the section on ALTER TABLE … DROP SEARCH OPTIMIZATION.