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

  1. Point lookups on a large table are slow
  2. Check Query Profile: many partitions scanned for few rows
  3. Is the filter an equality, IN, substring or VARIANT lookup? Yes: Add SEARCH OPTIMIZATION on those columns. No: Consider clustering or rewriting the query.
  4. Wait for the access path to build (serverless)
  5. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic