Weighted Cost Model for Optimized Query Processing

Abstract

Query processing is one of the most commonly used database procedures as well as a significant criterion for evaluating database performance. A key study direction in the database field is how to optimize queries and enhance the efficiency of database queries. The cost-based optimizer (CBO) is the current industry standard. Traditional cost models just sum all the cost factors together to get the overall cost, which makes it impossible to thoroughly assess queries and match customers’ expectations. This paper proposes a Weighted Cost Model (WCM) that emphasizes the balance of different cost factors. Constants and operators that fit WCM are also rewritten. We discuss the correlation between transmission cost and other factors by introducing it as a new factor. Then, we simulate the transmission cost using the correlation we discovered. Finally, we integrate WCM into Apache Calcite’s Cascade-style query optimizer framework. In a variety of systems, we test WCM using both real data sets and a virtual TPC-H environment. Our experimental results show that WCM is more stable and performs better than (or equal to) Calcite in 90% of systems, with an 18-fold optimization on a single query and a 2.3-time improvement in the virtual TPC-H environment.