Plan Optimization

a foundational element of any high performance database is its plan optimization engine recall that a query plan is in many ways similar to a generic computer program a key distinction, however, is that its machine code is in the language of relational algebra, set operations, i/o, network, and other database primitives when a database converts sql queries into a query plan, it must always generate a plan that produces the correct query results an over used joke at {{ocient}} is that if you could return incorrect results, you could make a blazingly fast database by simply returning random numbers for every query by way of analogy, this correct result requirement is the same as saying that a c++ compiler must always produce x86 machine code that correctly executes the originally provided instructions but just as a given c++ program can have many different possible representations in x86 machine code (different compilers, optimizations, etc) it is also the case that a given sql query can have many (infinitely, actually) different query plans in a database, the optimizer’s job is to take an already correct plan that is naively generated from a sql statement and produce an alternative plan that will emit the same results this is normally viewed as a minimization problem where the alternative plan is "less than" the original plan in some metric (usually estimated run time) and database engines have evolved extremely varied approaches to solving it some challenges to consider the search space for the minimization problem is infinite this means that any algorithm must eventually converge to some answer but do so in a reasonable amount of time it should not spend 100 seconds to shave 1 second off of a 2 second query plans are compared to each other based on some metric like execution time, but this metric is not trivial to build having a metric function m(p) that returns a useful number and is cheap enough to compute is a challenge in and of itself adding to this challenge is the additional considerations beyond measuring the computational cost of plan execution, namely disk and network i/o costs as well for these challenges, most database engines incorporate some sort of statistical approach statistics on the tables being queried (calculated either at data load time, or periodically updated) are used to bound execution and i/o estimates the optimizer of the ocient system does this as well while basic statistics like row counts are certainly considered, more advanced mathematical approaches are used as well multi dimensional probability density functions if a table has j columns, then it can make use of a j dimensional "table space" function f(v0, v1, v2, , vj) that maps a particular set of column values to the probability of those values the ocient system can then compute approximate row counts in the table by taking integrals of f in some hyper region of the table space hyperloglog in addition to row counts, the ability to estimate the number of distinct values is important as well the basic idea behind hyperloglog is that if a set has a uniformly randomized set of values, and n is the maximum number of leading zeros in the binary representations of all values in the set, then a good estimate of the set’s cardinality is 2^n here, each column is considered a set, and this statistical approach can estimate the number of distinct values for each column with such mathematical machinery available, you can see how the beginnings of a metric function for a plan can be built, but a few important details remain firstly, while the statistical approaches can make for good estimates, the ocient system must be able to transform from the domain of sql types into numbers for each supported sql type this is sometimes straightforward in cases like integral and floating point types, but becomes arbitrary and less obvious with things like variable length strings and arrays secondly, and more importantly, ocient must also be able to calculate the metric on sequences of operators, each of which can be considered as a separate transformation of input to output put another way, if given a sequence of plan operators where each operator is successively operating on rows t > p0 > p1 > p2 , then the raw column cardinality estimates of the columns in t might be of limited use in calculating a metric on p2 because p0 and p1 can drastically alter the statistics of the original table in order to account for this, the optimizer must have mathematical models of each operator that can be applied to input statistics to produce output statistics then, successive application of operators can be used to build up plan metrics comprised of per operator metrics clearly, successive approximation incurs increasing error but an interesting property of using a metric in this manner is that the exact error is not important instead, all that is necessary is that the accumulated errors in any estimation must not invalidate the relative comparison between two plans the topic of plan optimization is large and complicated, and the ocient optimizer is a key and integral component of its approach it incorporates not only detailed implementations but also an integrated view of the distributed nature of the query execution against nvme drives each iterative version of the optimizer also includes an ever increasing set of optimizations, giving it a sophisticated tool chest of transforms to apply to plans as well related links docid 4ycq1d8tkfmlsacorynf6 docid\ xpo4reeauxnlnh t3rss