PUPPY: Finding Performance Degradation Bugs in DBMSs via Limited-Optimization Plan Construction

Database management systems (DBMSs) consistently strive for enhanced performance. For a given query, the optimizer of a DBMS aims to construct an optimal execution plan that incorporates multiple optimization operations. However, the resulting plan may sometimes perform worse than even if no optimizations were applied. This occurs because the interactions between optimizations are complex and some situations might be overlooked in the implementation. We refer to these issues as Performance Degradation Bugs (PDBs). PDBs can result in significant consequences from decreased system efficiency and prolonged query processing times to potential disruptions in critical business operations. In this paper, we present PUPPY, an automated approach for detecting PDBs in DBMSs using limited-optimization plan construction. The key idea is to compare the performance with the plan generated with all optimization operations enabled, against the plan generated with only a subset of optimization operations in the same DBMS. If the response time of the plan with the limited optimization set is shorter than that of the fully optimized plan, it indicates a potential PDB. Specifically, PUPPY first generates queries that incorporate multiple optimization sequences, guided by optimization operation sequence coverage. Secondly, PUPPY analyzes the query plan and selectively disables specific optimizations to construct the limited optimization plan. We evaluate PUPPY on five widely-used DBMSs, namely MySQL, Percona, TiDB, PolarDB, and PostgreSQL against the state-of-the-art DBMS performance testing tools APOLLO and AMOEBA. More importantly, PUPPY reports 62 PDBs, with 54 anomalies confirmed as previously unknown bugs.