Have query optimizers hit the wall?

Snodgrass, Richard T.; Currim, Sabah; Suh, Young-Kyoon

doi:10.1007/s00778-021-00689-y

Cited by 3 publications

(5 citation statements)

References 18 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…The Oracle optimizer is also based on built-in rules for heuristic query optimization, which will remain throughout the query optimization phase, and the process requires considerable tuning and maintenance works [17]. In System R, the query optimization uses dynamic planning [4] by parsing the plan structure into a left-deep join tree form. However, the above approach is usually limited by the cost model, which can lead to missing plans that produce poor query quality under a nonlinear cost profile [18].…”

Section: Join Order Optimizationmentioning

confidence: 99%

“…The fast and accurate formulation of efficient query plans is the key to improving database system performance and query efficiency. Cost-based multi-table join query order selection in traditional databases is dominated by dynamic planning methods [4,5] and heuristics [6][7][8] based on the core idea of dynamic planning methods to reduce the size of the search space by merging redundant states. However, the search space is still close to the exhaustive global space, so it is suitable for queries that contain few relations.…”

Section: Introductionmentioning

confidence: 99%

See 1 more Smart Citation

Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network

Zhao

Dang

et al. 2023

Electronics

View full text Add to dashboard Cite

A join order directly affects database query performance and computational overhead. Deep reinforcement learning (DRL) can explore efficient query plans while not exhausting the search space. However, the deep Q network (DQN) suffers from the overestimation of action values in query optimization, which can lead to limited query performance. In addition, ε-greedy exploration is not efficient enough and does not enable deep exploration. Accordingly, in this paper, we propose a dynamic double DQN (DDQN) order selection method(DDOS) for join order optimization. First, the method models the join query as a Markov decision process (MDP), then solves the DRL model by integrating the network model DQN and DDQN weighting into the DRL model’s estimation error problem in query joining, and finally improves the quality of developing query plans. And actions are selected using a dynamic progressive search strategy to improve the randomness and depth of exploration and accumulate a high information gain of exploration. The performance of the proposed method is compared with those of dynamic programming, heuristic algorithms, and DRL optimization methods based on the query set Join Order Benchmark (JOB). The experimental results show that the proposed method can effectively improve the query performance with a favorable generalization ability and robustness, and outperforms other baselines in multi-join query applications.

show abstract

Section: Join Order Optimizationmentioning

confidence: 99%

Section: Introductionmentioning

confidence: 99%

Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network

Zhao

Dang

et al. 2023

Electronics

View full text Add to dashboard Cite

show abstract

“…This approach is frequently found in PQO algorithms since they seek to cache the optimizer's plans [34]. This strategy is also employed in [30] to estimate the empirical suboptimality of existing query optimizers. The quality of the resulting candidate plan set is predicated upon the optimizer's ability to either generate optimal plans for each query instance or a sufficient variety of good plans across the workload to benefit from plan sharing.…”

Section: Related Workmentioning

confidence: 99%

“…However, for sufficiently complex queries, evaluating these exponentiallymany sub-plans is prohibitively slow even with optimizations [32]. The selected plans are also not always the fastest, as observed by [30].…”

Section: Related Workmentioning

confidence: 99%

“…To construct subsequent generations, RCE first uniformly samples parent plans from the previous generation. For each of these base plans, RCE perturbs the join cardinalities of only the sub-plans that appear in the parent plan by multiplicative factors sampled from an exponentially-spaced range (lines [27][28][29][30][31][32][33][34][35][36][37][38][39]. By repeating this process multiple times and feeding in the resulting perturbations into the query optimizer, RCE generates a set of children plans (lines [15][16][17][18][19][20][21].…”

Section: Row Count Evolutionmentioning

confidence: 99%

See 1 more Smart Citation

Kepler: Robust Learning for Parametric Query Optimization

Doshi,

Zhuang,

Jain

et al. 2023

Proc. ACM Manag. Data

View full text Add to dashboard Cite

Most existing parametric query optimization (PQO) techniques rely on traditional query optimizer cost models, which are often inaccurate and result in suboptimal query performance. We propose Kepler, an end-to-end learning-based approach to PQO that demonstrates significant speedups in query latency over a traditional query optimizer. Central to our method is Row Count Evolution (RCE), a novel plan generation algorithm based on perturbations in the sub-plan cardinality space. While previous approaches require accurate cost models, we bypass this requirement by evaluating candidate plans via actual execution data and training anML model to predict the fastest plan given parameter binding values. Our models leverage recent advances in neural network uncertainty in order to robustly predict faster plans while avoiding regressions in query performance. Experimentally, we show that Kepler achieves significant improvements in query runtime on multiple datasets on PostgreSQL.

show abstract