Why Query Plans Are Different: An Automatic Detection and Inference System

Preventing plan regression has always been a demanding task. SQL tuning advisor, e.g., index advisor, and optimizer testing tool are two common solutions. The former is proposed for database users to avoid a sub-optimal plan on a production system while the second tries to evaluate the optimizer before releasing a new version. However, both of them are uninformed for optimizer developers to quickly find the reason why a sub-optimal plan is generated for a query. Manual analysis is both labor-intensive and inefficient for large query plan and number of queries. To this end, we propose AutoDI, an automatic detection and inference system to analyze why a sub-optimal plan is obtained. AutoDI  consists of two main modules, Difference Finder and Inference. In Difference Finder, we introduce two types of signatures and design an efficient algorithm to detect the possible sub-optimal parts of the plan in a top-down manner. Inference has an extensible rule-action framework and takes the detected differences as the input to explore the possible reasons behind them. We test AutoDI  with analytic workloads on a production database. Compared to developers, AutoDI  can find the right reason in most cases with much less time. AutoDI has been deployed in TiDB to help optimizer developers analyze the plan regression cases.