page163
249
Search Optimization Service Explained
Enabling SOS for a table does not imply full coverage of all the possible query predicate
operations. By default, an SOS-enabled table creates
EQUALITY
search access paths
only. A wider range of search access paths should be created by deliberate intent on
an attribute-by-attribute basis according to known query predicates. I discuss optimal
usage scenarios later in this chapter; note that there are many exclusions too.
Search optimization should be applied retrospectively and cannot pro-actively
predict future use but must be part of a well-considered holistic approach to application
performance tuning. We suggest SOS is enabled in production systems where
predictable workloads exist and baseline system performance has been established.
While QAS has no storage component and is focused on compute, QAS can
accelerate queries within specific boundaries and may be used with SOS where both
services complement each other. You can fine more information on QAS and SOS
interaction at
https://docs.snowflake.com/en/user-guide/search-optimization-
service#compatibility-with-query-acceleration
.
SOS implements an alternative to clustering by creating search access paths for
each enabled table. Search access paths may take time to create, and any changes to the
underlying table content will need to be reflected into the search access paths; note that
serverless compute is an asynchronous background process.
You must also be aware of both storage and compute cost implications of enabling
SOS. As mentioned, Snowflake provides a suite of services, and you may not need to use
every available feature. For example, if a non-SOS enabled query takes two seconds to
fulfil, will the same SOS-enabled query fulfilled in one second make enough difference
to justify the cost?
Before investigating how to implement SOS, let’s examine where SOS can add
benefit.
Optimal Use Scenarios
According to SOS’s design intent, SOS is targeted at tables with high numbers of micro-
partitions for queries with selective predicates.
Chapter 7
SearCh OptimizatiOn ServiCe