Search optimization is a Snowflake service for selective filter queries. It builds and maintains a search access path that records which column values occur in each micro-partition, improving micro-partition pruning without replacing an existing clustering key. section 8.5 section 1.2.3
Use search optimization when the query and table characteristics justify its cost:
- Filter the selected column with equality predicates, including an equal sign or an IN predicate. section 8.5.2
- Target a large table with many micro-partitions and a selective filter that returns relatively few rows. section 8.5.2
- Prefer it when candidate queries do not use an existing clustering key; otherwise, clustering may be the better optimization. section 8.5.2
- Limit the service to frequently filtered columns because building and maintaining search access paths consumes cost. section 8.5.1
Optimize a product filter
For a query filtering RETAILER_SALES by product_id while the table is already clustered by store_id, add search optimization to the filter column: ALTER TABLE RETAILER_SALES ADD SEARCH OPTIMIZATION ON EQUALITY(product_id); Then run SHOW TABLES LIKE 'RETAILER_SALES' and wait for search optimization progress to reach 100 before reviewing the query profile. section 8.5 section 8.5.1
Listing 8.4 Total sold quantity of a chosen product in each store
select store_id, sum(sales_quantity) as tot_quantity from RETAILER_SALES where product_id = 4120371332641752996 group by store_id;
Use the representative product-level aggregation to compare query behavior before and after optimization. section 8.5
Table 8.8 Output of the SHOW TABLES command where we can monitor the search access path progress
|
Table name
|
Search optimization
|
Search optimization progress
|
Search optimization bytes
|
|---|---|---|---|
RETAILER_SALES
|
ON
|
100
|
17,498,624
|
Use the progress output to confirm that the search access path is fully built and inspect its storage footprint. section 8.5.1
