Practical MCTS-based Query Optimization: A Reproducibility Study and new MCTS algorithm for complex queries

TL;DR

This paper critically evaluates recent MCTS-based query optimization methods, identifies their limitations due to learned cost models, and introduces a robust MCTS algorithm leveraging internal cost models and a new selection policy, demonstrating superior performance on complex queries.

Contribution

The paper provides a comprehensive reproducibility study of MCTS-based query optimization frameworks and proposes a novel, more stable MCTS algorithm that outperforms existing learned methods and industry baselines.

Findings

Learned cost models in MCTS frameworks often fail under diverse workloads.

The proposed MCTS method with internal cost models and Extreme UCT outperforms prior learned approaches.

Our approach achieves superior results on complex join benchmarks compared to state-of-the-art optimizers.

Abstract

Monte Carlo Tree Search (MCTS) has been proposed as a transformative approach to join-order optimization in database query processing, with recent frameworks such as AlphaJoin and HyperQO claiming to outperform traditional methods. However, the fact that these frameworks rely on learned cost models raises concerns related to generalizability and deployment readiness. This paper presents a comprehensive reproducibility study of these methods, revealing that they often fail to support the claimed performance gains when subjected to diverse workloads. Through an ablation study, we diagnose the root cause of this instability: while the MCTS search strategy is effective, the accompanying learned cost models suffer from severe out-of-distribution generalization errors. Addressing this, we propose a novel MCTS framework. Unlike prior methods that rely on unstable learned components, our approach utilizes the database standard internal cost model, augmented by a new Extreme UCT (Upper Confidence Bound applied to Trees) selection policy to navigate the search space more robustly. We benchmark our method against the original AlphaJoin and HyperQO, as well as industry-standard baselines including Dynamic Programming (DP) and Genetic Query Optimization (GEQO), using the well-known Join Order Benchmark (JOB) and the new JOB-Complex benchmark. The results demonstrate that our approach outperforms learned MCTS methods and achieves superiority over a SOTA query optimizer in complex join scenarios on real-world data. We release the full implementation and experimental artifacts to support further research.