Search Optimization Service
Snowflake · Performance Deep Dive
Theory
The Search Optimization Service builds and maintains a persistent search access path for chosen columns of a table. When a query uses a selective filter such as WHERE customer_id = 12345, the access path tells Snowflake exactly which micro-partitions can contain matching rows, so it skips far more partitions than min/max pruning alone. You enable it with ALTER TABLE customers ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email), and you can also target substring searches, fields in VARIANT columns and geospatial functions.
It is an Enterprise edition feature. Building and maintaining the access path runs as a serverless service that uses credits, and the access path itself adds storage.
Why we use it
Min/max pruning works well for ranges on clustered columns, but point lookups on high-cardinality columns like IDs or emails can still scan most partitions. Search optimization makes those needle-in-a-haystack queries fast.
When to use it
Use it on large tables with frequent, highly selective lookups or substring searches, where returning a few rows still scans many partitions. For range filters on dates, prefer clustering. For small tables, it is not worth the cost.
When Pruning Alone Isn't Enough
Micro-partition pruning works well for range filters on a clustered column, but a lookup for one specific value scattered across many partitions (like a customer ID or an order number) can still require scanning far more data than needed.
Enabling It on a Table
ALTER TABLE ... ADD SEARCH OPTIMIZATION builds and maintains the access path automatically in the background, at an ongoing storage and compute cost that only pays off if those exact-match lookups run often.
What It's Best Suited For
Point lookups, IN-lists, and equality/substring filters on VARCHAR or VARIANT columns in very large tables — not a general performance switch to flip on everything.
How it works
- Point lookups on a large table are slow
- Check Query Profile: many partitions scanned for few rows
- Is the filter an equality, IN, substring or VARIANT lookup? Yes: Add SEARCH OPTIMIZATION on those columns. No: Consider clustering or rewriting the query.
- Wait for the access path to build (serverless)
- Compare partitions scanned and runtime before and after
Example
ALTER TABLE customers ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email);
ALTER TABLE customers ADD SEARCH OPTIMIZATION ON SUBSTRING(email);
SELECT * FROM customers WHERE customer_id = 12345;
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
ALTER TABLE customers DROP SEARCH OPTIMIZATION;Key points
- Speeds up selective equality, IN and substring lookups, not broad scans
- Can target specific columns and VARIANT fields
- Adds ongoing storage and serverless maintenance cost
- Enterprise edition feature
- Best on very large tables with frequent point lookups