Index-Based Join Size Estimation Using Adaptive Sampling

Cost-based query optimizers rely on cardinality estimates of intermediate results to avoid suboptimal query execution plans. However, when confronted with ad-hoc queries on big data, said optimizers can produce large estimation errors, resulting in drastic decreases in overall performance. Such errors occur because many estimation algorithms for joins make use of strong independence and uniformity assumptions. Moreover, equi-joins on skewed data with filter predicates tend to cause the aforementioned assumptions to fail [2]. Since the cardinality estimate of a result with many joins depends on estimates of the underlying joins, it has been shown that improving the accuracy of join size estimates in a "bottom-up" order can significantly improve performance [2]. Thus, our research aims at improving the estimation of two-table join sizes.

Certain join size estimation approaches that use offline samples fare poorly with filtering and can suffer from insufficient sample size [2]. Alternatively, query optimizers may make use of persisted histograms. However, the associated storage space is a large deterrent, as is the case with persisting offline samples [4]. In turn, algorithms have been presented wherein adaptive, block-level sampling is conducted during query optimization [5]. To the best of our knowledge, there is no algorithm for two-table join size estimation that 1) samples filtered base tables, 2) makes use exclusively of persisted counts in B+-tree indexes and 3) attempts to provide statistical confidence on estimates.

In this paper, we present and evaluate a novel join size estimation algorithm prototyped on SAP IQ. Our algorithm can be easily incorporated into query optimizers that utilize bottom-up enumeration and evaluate filter predicates prior to optimization. In contrast with existing machine learning based approaches [1], the algorithm is simpler to implement or derive from existing support for sampling. Join size estimates are produced using a combination of variance-based calculations from Oracle 12c [5] along with the usage of persisted counts in indexes from index-based sampling [2]. In doing so, the amount of sampling conducted is minimized until either a parameterized budget is surpassed or there is sufficient statistical confidence in an estimate. On a subset of industry-standard benchmark queries involving joins on skewed data, our method improved the overall execution time by 16%. Amongst queries with execution plans altered by our estimates, the mean percentage improvement in individual execution time was 34%.