Snowflake Data Cloud optimization tip regarding the Search Optimization Service
Background
The Search Optimization Service (SOS) can be used to improve query performance for specific lookups. Use cases may be the optimization of analytical queries, or any query that uses filtering. This includes not only equality filtering (=), but also regular expression searches e.g. LIKE, CONTAINS. Likewise the SOS can potentially improve searches on VARIANT data types.
Tip
- Even though you may have configured the Search Optimization Service on a table or particular column, it can take time for the specific optimization to be prepared and available for use
- So you could configure the SOS, then execute the query you are optimising, and notice that e.g. query pruning is pretty much the same as before the optimization configuration
- Assuming the configuration will add benefit to the query, the reason for the lack of expected performance gain is because the SOS prepares the optimization configuration in the background. For larger tables it can take e.g. a number of hours depending on optimization configuration and data volume.
- To determine if the optimization is ready to use, check the search_optimization_progress field in the SHOW TABLES output. This indicates processing progress in percent.
Summary
There are plenty of considerations in relation to the Search Optimization Service before ensuring an optimal return, such as estimated costs and credits required to setup and maintain a configuration.
However, checking the SHOW TABLES command outputs will indicate whether the optimization configuration is complete and ready to use. I hope this helps!
© Dan Galavan 2023