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
In a nutshell
  • 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 with SYSTEM$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.

Queryselective filterchecksPartition metadatamin and max, search pathsskipsFewer partitionsless data scannedfasterResult

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 killerBetter 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 columnMatch the literal to the column type
Filter on a column unrelated to load order or clusteringCluster on it, or use search optimization
Leading wildcard LIKE '%abc'Search optimization with SUBSTRING, or redesign
Filter hidden inside a VARIANT valuePromote 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);
SupportsExample
Equality and IN lookupsWHERE order_id = 42
Substring and regular expression searchWHERE notes LIKE '%refund%'
VARIANT, ARRAY and OBJECT elementsWHERE v:id = 'abc'
Geospatial functionsST_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_COSTS before enabling, and SEARCH_OPTIMIZATION_HISTORY to 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 TABLES shows whether a table has it enabled and its progress.
Exam tip

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

AspectClustering keySearch optimizationMaterialized view
HelpsRange filters, sorting by a columnPoint lookups, text and VARIANT searchRepeated aggregates on one table
ColumnsOne key in a chosen orderMany columns at onceOne query shape
MaintenanceServerless reclusteringServerless access path upkeepServerless refresh
EditionSee clustering pageEnterprise or higherEnterprise or higher
Typical tableVery large, filtered by rangeVery large, selective lookupsSlow changing base table
Slow querybig tablerange filtersClustering keyprune by rangeSlow querybig tablepoint lookupsSearch optimizationfind rows fastSlow querybig tablerepeated aggregateMaterialized viewprecomputed

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.

  1. Rewrite the query. Select needed columns, filter early, avoid functions on filtered columns, use UNION ALL instead of UNION when duplicates do not matter, and fix join conditions.
  2. Use native data types so metadata and pruning work.
  3. Rely on caches. Identical queries hit the result cache for free.
  4. Precompute. Use a materialized view, dynamic table or summary table for repeated heavy aggregation.
  5. Improve data layout. Cluster or load data in a sorted order.
  6. Add targeted features. Search optimization for lookups, query acceleration for rare big scans.
  7. 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 with SYSTEM$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.