How I Learned to Stop Worrying and Love Re-optimization

Abstract

Cost-based query optimizers remain one of the most important components of database management systems for analytic workloads. Though modern optimizers select plans close to optimal performance in the common case, a small number of queries are an order of magnitude slower than they could be. In this paper we investigate why this is still the case, despite decades of improvements to cost models, plan enumeration, and cardinality estimation. We demonstrate why we believe that a re-optimization mechanism is likely the most cost-effective way to improve end-to-end query performance. We find that even a simple re-optimization scheme can improve the latency of many poorly performing queries. We demonstrate that re-optimization improves the end-to-end latency of the top 20 longest running queries in the Join Order Benchmark by 27%, realizing most of the benefit of perfect cardinality estimation.

Break Up the Pipeline Structure to Reach a Nearly Optimal End-to-End Latency

“…In other words, reoptimization admits that the query optimizer can make mistakes in decisions and thus tries to alleviate them. In a recent work, Perron et al [31] evaluated the performance of reoptimization on the Join Order Benchmark [23], and their result suggests that reoptimization can significantly reduce query execution time.…”

“…Another baseline that we originally considered is reoptimization [18], which has been previously evaluated on JOB via simulation by Perron et al [31]. We implemented the reoptimization technique in PostgreSQL (we describe the details in Appendix E).…”

“…Another approach is to make a better query plan with the help of queries that have been executed before [9,13,20,24,30,33,34]. However, none of these approaches are completely satisfying [31].…”

“…As it is difficult to solve the cardinality estimation problem, reoptimization [3,18,31] is proposed to avoid the need for accurate cardinality estimation. Reoptimization first generates an initial execution plan at the beginning, and then detects during runtime whether the actual behavior of a query plan becomes significantly different from what was expected.…”

Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation

“…First, the estimation accuracy does not directly equal to the query plan quality. As different sub-plan queries matters differently to the query plan [3,44,55], a more accurate method may produce a much worse query plan if they mistake a few very important estimations [40]. Second, the actual query time is affected by multiple factors, including both query plan quality and CardEst inference cost.…”