A search access path is a background-maintained structure that records which column values may occur in each table micro-partition. Search optimization uses it to improve micro-partition pruning for filtered queries, including when the filtered column is not the table's clustering key. Snowflake maintains the path as rows are inserted, updated, or deleted. section 8.5 section 8.7
Apply search optimization with these operational choices:
- Target frequently filtered columns rather than every eligible column when cost matters; omitting columns builds paths for all eligible columns. section 8.5.1
- Enable the feature with equality search optimization on the filtered column. section 8.5.1
- Allow time for path construction, then monitor search optimization progress and bytes through SHOW TABLES. section 8.5.1
- Use Snowflake Enterprise edition or higher, because search optimization requires that edition level. section 8.5.1
Filtering RETAILER_SALES by product
RETAILER_SALES is clustered by store_id, while a sales query filters by product_id. Re-clustering for product_id is not used; instead, search optimization adds a search access path for product_id so filtering can improve pruning before grouping results by store. section 8.5
alter table RETAILER_SALES add search optimization on equality(product_id);
Enable an equality search access path for the frequently filtered product_id column with ALTER TABLE. section 8.5.1
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
|
Verify that search optimization is enabled and that path construction has reached 100 percent; the listing also reports maintained bytes. section 8.5.1
