Query Acceleration and Search Optimization in Snowflake

1.Four levers for four different symptoms

The search optimization documentation lists the other ways to optimize query performance next to it: query acceleration, materialized views (clustered or unclustered), and clustering a table. These are not interchangeable settings. Each one works on a different part of the problem. Query acceleration adds compute: it sends part of a heavy scan to shared resources. Search optimization adds a data structure that lets a query skip micro-partitions when it is looking for a few specific rows. A clustering key changes how rows are laid out across micro-partitions, so filters on that key prune better. A materialized view stores the result of a query that keeps being run, so later runs read the stored result instead of recomputing it.

On the exam, a question about one of these usually describes a symptom and asks which lever fits it. This page covers the two services you turn on for a warehouse or a table: query acceleration and search optimization. Clustering keys and materialized views change how the data is stored or what gets stored, and they have their own lesson.

Sources1

2.Query acceleration service: shared compute for outlier queries

The query acceleration service (QAS) is a warehouse-level feature. It does not speed up every query. Its job is to stop a few unusually heavy queries from slowing the warehouse down. It does this by sending portions of those queries to shared compute resources provided by the service, so the scanning and filtering run with more parallelism. It speeds up two kinds of query pattern: large scans with an aggregation or selective filter, and statements that insert, copy, update or delete large amounts of data. The service depends on server availability, so the speed-up can vary over time.

To turn it on, set ENABLE_QUERY_ACCELERATION = TRUE in CREATE WAREHOUSE or ALTER WAREHOUSE. You may not need to: QAS is enabled by default on Gen2 standard warehouses and on multi-cluster warehouses.

CREATE WAREHOUSE my_wh WITH ENABLE_QUERY_ACCELERATION = true;

CREATE WAREHOUSE my_other_wh WITH ENABLE_QUERY_ACCELERATION = false;
ALTER WAREHOUSE my_other_wh SET ENABLE_QUERY_ACCELERATION = true;

Eligibility has no fixed size threshold. It depends on the query plan and the warehouse size, and Snowflake marks a query as eligible only when it is highly confident that QAS would speed it up. Common reasons a query is ineligible: the scan does not cover enough partitions; the filters are not selective enough; a GROUP BY has too many distinct values; a LIMIT clause prevents acceleration; or the query uses nondeterministic functions such as SEQ or RANDOM.

There are two ways to find out before you pay for QAS. SYSTEM$ESTIMATE_QUERY_ACCELERATION takes the ID of a query that has already run. For an eligible query it returns estimated run times at different scale factors. For an ineligible query it returns the reason, as in the output below. To look across the whole workload, query the SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE view, which shows how much of each query's execution time could have been accelerated.

{
  "estimatedQueryTimes": {},
  "ineligibleReason": "NO_LARGE_ENOUGH_SCAN",
  "originalQueryTime": 20.291,
  "queryUUID": "cf23522b-3b91-cf14-9fe0-988a292a4bfa",
  "status": "ineligible",
  "upperLimitScaleFactor": 0
}

SELECT query_id, eligible_query_acceleration_time
  FROM SNOWFLAKE.ACCOUNT_USAGE. ? 
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  ORDER BY eligible_query_acceleration_time DESC;

Sources2

3.The scale factor: a cap on QAS spending

QAS can raise the rate at which a warehouse uses credits. The control for this is QUERY_ACCELERATION_MAX_SCALE_FACTOR, which you set with CREATE WAREHOUSE or ALTER WAREHOUSE. It is a multiplier on the warehouse's size and cost, and it sets the most compute the warehouse can lease for acceleration. A medium warehouse costs 4 credits per hour. With a scale factor of 5, it can lease up to 5 times its own size, which adds at most 20 credits per hour.

The default depends on how QAS was enabled. If you set ENABLE_QUERY_ACCELERATION = TRUE explicitly, the default is 8. When Snowflake enables QAS automatically for a Gen2 or multi-cluster warehouse at creation, the default is 2. A value of 0 does not turn QAS off. It removes the upper limit, so queries can lease as much as they need and as much as is available. The scale factor covers the whole warehouse, so on a multi-cluster warehouse it is worth raising it so all clusters can benefit.

The scale factor is a ceiling, not a fixed charge. QAS uses only the resources a query needs and that are available at that moment. QAS is billed per second, only while the service is in use, and those credits are billed separately from the warehouse's own usage.

Sources2

4.Search optimization service: skipping micro-partitions for selective lookups

QAS adds compute. Search optimization reduces how much data a query has to read. It is set on a table, and it targets selective point lookups, text and IP searches with SEARCH and SEARCH_IP, substring and regular-expression predicates (LIKE, ILIKE, RLIKE), predicates on elements of semi-structured and structured columns, and some geospatial functions.

The service creates and maintains a search access path. This structure records which column values might appear in each micro-partition, so a query can skip the partitions that cannot match. A background maintenance service builds the path when you enable the feature and keeps it up to date as DML changes the table. It needs no warehouse of yours, but you pay for its storage and compute. Building the path can take a long time on a large table, and queries are not accelerated until it is complete. Check the search_optimization_progress column in SHOW TABLES before you measure any improvement.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION;

Queries do not change. Equality predicates, IN lists, IS NULL, and AND-combinations of supported predicates can all use the access path, and so can DELETE, UPDATE and MERGE. How you write the predicate still matters. An implicit cast on the constant is fine, but casting the column itself blocks the access path. The two services also work together: search optimization first prunes the micro-partitions a query does not need, and QAS can then send part of the remaining work to shared compute.

Sources12

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Setting QUERY_ACCELERATION_MAX_SCALE_FACTOR to 0 turns off query acceleration.Why is that wrong?

   A scale factor of 0 removes the upper limit on leased resources. QAS is turned on and off with ENABLE_QUERY_ACCELERATION.

   Covered in The scale factor: a cap on QAS spending

2. 2.A nonzero bytes_spilled_to_remote_storage on a QAS-enabled warehouse always means the warehouse is too small.Why is that wrong?

   With QAS enabled, Snowflake writes a small amount of data to remote storage for each eligible query, even when QAS is not used for that query.

   Covered in Query acceleration service: shared compute for outlier queries

3. 3.Search optimization speeds up queries as soon as ADD SEARCH OPTIMIZATION finishes.Why is that wrong?

   The maintenance service builds the search access path in the background, which can take a long time on a large table. Queries are not accelerated until the path is complete.

   Covered in Search optimization service: skipping micro-partitions for selective lookups