Performance Optimization · Topic 5 of 9
Search Optimization Service, Pruning and Optimization Techniques
Use search optimization for selective lookups, understand micro-partition pruning, and choose between clustering, materialized views and rewrites.
- 4 min
- Pruning uses per partition min and max metadata; compare partitions scanned with total.
- Search optimization: Enterprise or higher, serverless cost plus storage, for equality, substring, VARIANT and geospatial lookups.
ADD SEARCH OPTIMIZATION [ON EQUALITY(...), SUBSTRING(...), GEO(...)]; estimate withSYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS.
What it is
The search optimization service builds a persistent data structure that lets Snowflake find a few matching rows in a huge table without scanning most of it. It is aimed at selective lookups: "find the order with this ID" or "find rows containing this text".
Pruning is the underlying mechanism of fast scans. Snowflake keeps min and max metadata for every column of every micro-partition and skips those that cannot match the filter. Most optimization techniques are ways to make pruning succeed.
How it works
Micro-partition pruning
Each micro-partition holds 50 to 500 MB of uncompressed data. Pruning works well when matching rows are concentrated in few partitions.
| Pruning killer | Better form |
|---|---|
Function on the filtered column, WHERE TO_CHAR(order_date) = '2026-11-14' | Compare the raw column, WHERE order_date = '2026-11-14' |
| Type mismatch that forces a cast on the column | Match the literal to the column type |
| Filter on a column unrelated to load order or clustering | Cluster on it, or use search optimization |
Leading wildcard LIKE '%abc' | Search optimization with SUBSTRING, or redesign |
| Filter hidden inside a VARIANT value | Promote the field to a typed column |
The Query Profile shows partitions scanned versus partitions total. The closer these are, the weaker the pruning.
Search optimization service
Enable it per table, optionally for chosen columns and methods.
ALTER TABLE orders ADD SEARCH OPTIMIZATION;
ALTER TABLE orders ADD SEARCH OPTIMIZATION ON EQUALITY(order_id), SUBSTRING(notes);
ALTER TABLE orders ADD SEARCH OPTIMIZATION ON GEO(location);
| Supports | Example |
|---|---|
| Equality and IN lookups | WHERE order_id = 42 |
| Substring and regular expression search | WHERE notes LIKE '%refund%' |
| VARIANT, ARRAY and OBJECT elements | WHERE v:id = 'abc' |
| Geospatial functions | ST_WITHIN, ST_INTERSECTS |
- It needs Enterprise Edition or higher.
- It is maintained by a serverless background service. You pay for that compute and for the extra storage of the search access paths, and the cost rises with data change.
- Use
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSbefore enabling, andSEARCH_OPTIMIZATION_HISTORYto watch cost afterward. - It is best on large tables where queries return a small number of rows, and where the filter column has many distinct values.
SHOW TABLESshows whether a table has it enabled and its progress.
Search optimization helps "needle in a haystack" queries. If a query returns a large share of the table, it will not help.
Search optimization compared with clustering and materialized views
| Aspect | Clustering key | Search optimization | Materialized view |
|---|---|---|---|
| Helps | Range filters, sorting by a column | Point lookups, text and VARIANT search | Repeated aggregates on one table |
| Columns | One key in a chosen order | Many columns at once | One query shape |
| Maintenance | Serverless reclustering | Serverless access path upkeep | Serverless refresh |
| Edition | See clustering page | Enterprise or higher | Enterprise or higher |
| Typical table | Very large, filtered by range | Very large, selective lookups | Slow changing base table |
These can be combined: cluster by date for range work and add search optimization for ID lookups on the same table.
Optimization techniques
Work through techniques in order of cheapness.
- Rewrite the query. Select needed columns, filter early, avoid functions on filtered columns, use
UNION ALLinstead ofUNIONwhen duplicates do not matter, and fix join conditions. - Use native data types so metadata and pruning work.
- Rely on caches. Identical queries hit the result cache for free.
- Precompute. Use a materialized view, dynamic table or summary table for repeated heavy aggregation.
- Improve data layout. Cluster or load data in a sorted order.
- Add targeted features. Search optimization for lookups, query acceleration for rare big scans.
- Size compute last. Scale up for spilling and complex queries, scale out for concurrency.
Other useful habits include avoiding SELECT DISTINCT and ORDER BY on large results unless needed, joining on the same data types, and using approximate functions such as APPROX_COUNT_DISTINCT where an estimate is acceptable.
Common mistakes
Wrapping the filtered column in a function. The metadata cannot be used, so everything scans.
Adding search optimization for range scans. It targets selective lookups; use clustering for ranges.
Enabling it without estimating cost. Serverless maintenance and storage can be large on busy tables.
Resizing the warehouse first. A better predicate or layout often fixes the problem for free.
What to remember for the exam
- Pruning uses per partition min and max metadata; compare partitions scanned with total.
- Search optimization: Enterprise or higher, serverless cost plus storage, for equality, substring, VARIANT and geospatial lookups.
ADD SEARCH OPTIMIZATION [ON EQUALITY(...), SUBSTRING(...), GEO(...)]; estimate withSYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS.- Clustering for ranges, search optimization for point lookups, materialized view for repeated aggregates.
- Optimize in order: rewrite, layout, precompute, features, then size.
Check yourself
A table with billions of rows is queried by
customer_email = ?and returns one row, yet scans many partitions. What fits?The search optimization service with equality search on that column.
Why does
WHERE TO_CHAR(order_date) = '2026-11-14'scan everything?The function on the column prevents partition metadata from being used, so pruning fails.
What do you check before enabling search optimization on a busy table?
Run
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS, because maintenance and storage costs rise with data change.
Topic 5 of 9 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.