Finding Missed Optimizations in DBMSs through Unbalanced Short-circuit Query Construction

DBMSs underpin modern data-intensive applications, where performance directly impacts system responsiveness and user experience. To improve efficiency, DBMSs employ many sophisticated optimization techniques for SQL queries. However, the complexity of SQL queries and DBMS architectures often causes implementations to miss potential optimization opportunities, resulting in performance degradation, inefficient resource utilization, and diminished user experience. Identifying these missed optimizations is challenging due to the intricate interactions among query semantics, optimizer decisions, and execution behaviors. Existing approaches have detected many performance issues when implemented optimizations perform poorly. However, they rarely reveal optimizations that were entirely missed, leaving many performance gaps unaddressed. In this paper, we present SCor, a black-box approach for identifying missed optimizations in DBMSs through unbalanced short-circuit query construction. Our key insight is that the results of many queries can be determined without full execution, yet DBMSs still execute the entire query, revealing missed optimizations. SCor realizes it by constructing short-circuit queries, where the result can be obtained from low-cost operations alone. If the DBMS still executes the full query with high-cost operations, it indicates missed optimizations. Since short-circuit patterns are prevalent in SQL queries and can be flexibly embedded into diverse query structures, SCor can be systematically applied to expose missed optimizations across a wide range of queries. Our evaluation of SCor across 11 widely-used DBMSs, revealed 153 previously undetected performance bugs resulting from missed optimizations, including 2 bugs in Oracle and 3 bugs in PostgreSQL. Among all reported bugs, 125 have been confirmed by developers, and 33 have already been fixed.